How to configure navigation property for custom SQL in EF Core 5如何在 EF Core 5 中为自定义 SQL 配置导航属性

标签：Customer Core set get EF db CustomerId SQL public

我有一个自定义 SQL 语句来获取客户的最大订单。我没有名为 MaxOrders 的表 - 它只是一个自定义查询。

我正在使用 Include 获取客户记录和相关对象

dbcontext.Customers.Include(x => x.MaxOrder)

我想知道如何为这种场景配置导航属性。

客户类别

public class Customer 
{
    public int Id { get; set;}
    public string Name { get; set;}

    public MaxOrder MaxOrder { get; set;}
}

MaxOrder 类

public class MaxOrder 
{
    public int CustomerId { get; set;}
    public decimal TotalAmount { get; set;}

    public Customer Customer { get; set;}
}

数据库上下文

public DbSet<Customer> Customers { get; set; }
public DbSet<MaxOrder> MaxOrders{ get; set; }

模型构建器

modelBuilder.Entity<MaxOrder>()
            .HasNoKey()
            .ToView(null)
            .ToSqlQuery(@"SELECT CustomerId, SUM(Amount) AS TotalAmount 
                          FROM Orders O 
                          WHERE Id = (SELECT MAX(Id) 
                                      FROM Orders 
                                      WHERE CustomerId = O.CustomerId)
                          GROUP BY CustomerId")

【问题讨论】：

使用Include 这是不可能的。只有通过Select 的自定义投影，可能还有第三方扩展，可以扩展/注入表达式树（很多）。
出于技术原因，是否需要将MaxOrders 实现为自定义查询？如果没有，这应该可以通过将其更改为返回与自定义查询等效的表达式的只读属性（.GroupBy().Select() 等...）来实现。
嗨@BradleyUffner，虽然实际查询比示例复杂一点，但如果可行的话，它可以是lambda 或linq 查询。
您的查询类型看起来不错。但是您可能应该明确定义导航属性。 EF Core 应在您使用时将查询嵌入为子查询。但是您可能应该以dbcontext.MaxOrders.Include(m => m.Customer) 开始所有查询

标签： c# entity-framework entity-framework-core asp.net-core-webapi

【解决方案1】：

我无法使用ToSqlQuery 使其工作，因为在设置MaxOrder 和Customer 之间的关系时收到NotImplementedException: SqlQuery 异常。使用视图，它可以正常工作。如果您能够创建视图，我建议您这样做。

MaxOrder 需要一个密钥，它是 Customer 的 FK，并且为 MaxOrder:Customer 定义了 1:1 的关系。将 .ToView("vwMaxOrder") 调用替换为 .ToSqlQuery(<body of view>) 以重现上述异常。

public class TestDbContext : DbContext
{
    public TestDbContext(DbContextOptions<TestDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .ToTable("Customer");

        modelBuilder.Entity<Order>()
            .ToTable("Order")
            .HasOne(o => o.Customer)
            .WithMany(c => c.Orders)
            .IsRequired();

        modelBuilder.Entity<OrderItem>()
            .ToTable("OrderItem")
            .HasOne(oi => oi.Order)
            .WithMany(o => o.Items)
            .IsRequired();

        modelBuilder.Entity<OrderItem>()
            .HasOne(oi => oi.Item)
            .WithMany()
            .IsRequired();

        modelBuilder.Entity<Item>()
            .ToTable("Item");

        modelBuilder.Entity<MaxOrder>()
            .ToView("vwMaxOrder")
            .HasKey(mo => mo.CustomerId);

        modelBuilder.Entity<MaxOrder>()
            .HasOne(mo => mo.Customer)
            .WithOne(c => c.MaxOrder)
            .HasForeignKey<MaxOrder>(mo => mo.CustomerId);
    }

    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<Item> Items { get; set; }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Order> Orders { get; set; }
    public MaxOrder MaxOrder { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public Customer Customer { get; set; }
    public ICollection<OrderItem> Items { get; set; }
    public DateTime Created { get; set; }
}

public class OrderItem
{
    public int Id { get; set; }
    public Order Order { get; set; }
    public Item Item { get; set; }
    public int Quantity { get; set; }
    public decimal Price { get; set; }
}

public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class MaxOrder
{
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }
    public decimal Value { get; set; }
}

查看：

CREATE VIEW [dbo].[vwMaxOrder]
    AS 
select
    c.Id CustomerId
    , Value = MAX(OrderTotal.Value)
from
    Customer c
    inner join [Order] o
        on c.Id = o.CustomerId
    inner join
        (
            select
                oi.OrderId
                , Value = SUM(oi.Price * oi.Quantity)
            from
                OrderItem oi
            group by
                oi.OrderId
        ) OrderTotal
            on o.Id = OrderTotal.OrderId
group by
    c.Id

演示程序：

class Program
{
    static void Main(string[] args)
    {
        using var db = CreateDbContext();

        //AddCustomers(db);
        //AddItems(db);
        //AddOrders(db);
        //AddOrderItems(db);

        var customers = db.Customers
            .Include(c => c.Orders)
                .ThenInclude(o => o.Items)
            .Include(c => c.MaxOrder)
            .ToArray();

        foreach(var customer in customers)
        {
            Console.WriteLine("----------------------");
            Console.WriteLine($"Customer ID {customer.Id} max order amount: {customer.MaxOrder.Value}");
            
            foreach (var order in customer.Orders)
            {
                var total = order.Items.Sum(oi => oi.Price * oi.Quantity);

                Console.WriteLine($"Order ID {order.Id} total: {total}");
            }
        }
    }

    static TestDbContext CreateDbContext()
    {
        var opts = new DbContextOptionsBuilder<TestDbContext>()
            .UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;Database=DemoDB;Trusted_Connection=True;")
            .Options;

        return new TestDbContext(opts);
    }

    static void AddCustomers(TestDbContext db)
    {
        db.Customers.Add(new Customer()
        {
            Name = "Customer A"
        });

        db.Customers.Add(new Customer()
        {
            Name = "Customer B"
        });

        db.SaveChanges();
    }

    static void AddItems(TestDbContext db)
    {
        db.Items.Add(new Item()
        {
            Name = "Item A",
        });

        db.Items.Add(new Item()
        {
            Name = "Item B",
        });

        db.SaveChanges();
    }

    static void AddOrders(TestDbContext db)
    {
        db.Orders.Add(new Order()
        {
            Created = DateTime.Now,
            Customer = db.Customers.First(),
        });

        db.Orders.Add(new Order()
        {
            Created = DateTime.Now.AddDays(-1),
            Customer = db.Customers.First(),
        });

        db.Orders.Add(new Order()
        {
            Created = DateTime.Now.AddDays(-2),
            Customer = db.Customers.Skip(1).First(),
        });

        db.Orders.Add(new Order()
        {
            Created = DateTime.Now.AddDays(-3),
            Customer = db.Customers.Skip(1).First(),
        });

        db.SaveChanges();
    }

    static void AddOrderItems(TestDbContext db)
    {
        var orders = db.Orders.Include(o => o.Items).ToArray();
        var items = db.Items.ToArray();

        for(var i = 0; i < orders.Length; ++i)
        {
            var order = orders[i];

            for(var j = 0; j < items.Length; ++j)
            {
                order.Items.Add(new OrderItem()
                {
                    Item = items[j],
                    Quantity = i + j + 1,
                    Price = 20 - i * 2 - j * 3,
                });
            }
        }

        db.SaveChanges();
    }
}

结果：

----------------------
Customer ID 1 max order amount: 81.00
Order ID 1 total: 54.00
Order ID 2 total: 81.00
----------------------
Customer ID 2 max order amount: 111.00
Order ID 3 total: 111 119)">100.00
Order ID 4 total: 111.00

【讨论】：

在我自己的项目中，我使用 context.table.FromSql...() 和无键 (.HasNoKey()) 类型，并加入具有导航属性的其他表。一切正常。

【解决方案2】：

免责声明：您要问的是不 EF Core 5.0 自然支持，因此提供的解决方法很可能会在未来的 EF Core 版本中中断。使用它需要您自担风险，或者使用支持的（映射到包含所需 SQL 的真实数据库视图，正如其他人所提到的）。

现在，问题。首先，您要映射到 SQL 并在关系中使用的实体类型不能是无键的。只是因为目前keyless entity types

仅支持导航映射功能的一个子集，具体来说：

他们可能永远不会充当关系的主要目的。

他们可能没有导航到拥有的实体

它们只能包含指向常规实体的参考导航属性。

实体不能包含无键实体类型的导航属性。

在您的情况下，Customer 违反了最后一条规则，将导航属性定义为无键实体。但是没有它，您将无法使用Include，这是所有这一切的最终目标。

没有解决该限制的方法。即使您使用一些技巧来映射关系并获得正确的 SQL 翻译，仍然不会加载导航属性，因为所有与 EF Core 相关的数据加载方法都依赖于更改跟踪，并且它需要带有键的实体。

因此，实体必须是“正常的”（带有键）。这没有问题，因为查询具有定义一对一关系的唯一列。但是，这遇到了另一个当前 EF Core 限制 - 在模型完成期间，您会得到 NotImplemented 异常，因为普通实体映射到 SqlQuery。不幸的是，这是关系模型终结中许多地方使用的 static 函数，这也是一个 static 方法，因此实际上不可能从外部拦截和修复它。

一旦您了解了问题（哪些支持，哪些不支持），下面是解决方法。支持的映射是要查看的正常实体。因此，我们将使用它（ToView 而不是失败的ToSqlQuery），而不是名称将提供包含在() 中的 SQL，以便能够从关联的 EF Core 元数据中识别和提取它。请注意，EF Core 不会验证/关心您在 ToTable 和 ToView 方法中提供的名称 - 只是它们是否为 null。

然后我们需要插入 EF Core 查询处理管道，并将“视图名称”替换为实际的 SQL。

以下是上述想法的实现（将其放在您的EF Core项目中的某个代码文件中）：

namespace Microsoft.EntityFrameworkCore
{
    using Metadata.Builders;
    using Query;

    public static class InlineSqlViewSupport
    {
        public static DbContextOptionsBuilder AddInlineSqlViewSupport(this DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.ReplaceService<ISqlExpressionFactory, CustomSqlExpressionFactory>();

        public static EntityTypeBuilder<TEntity> ToInlineView<TEntity>(this EntityTypeBuilder<TEntity> entityTypeBuilder, string sql)
            where TEntity : class => entityTypeBuilder.ToView($"({sql})");
    }
}

namespace Microsoft.EntityFrameworkCore.Query
{
    using System.Linq.Expressions;
    using Metadata;
    using SqlExpressions;

    public class CustomSqlExpressionFactory : SqlExpressionFactory
    {
        public override SelectExpression Select(IEntityType entityType)
        {
            var viewName = entityType.GetViewName();
            if (viewName != null && viewName.StartsWith("(") && viewName.EndsWith(")"))
            {
                var sql = viewName.Substring(1, viewName.Length - 2);
                return Select(entityType, new FromSqlExpression("q", sql, NoArgs));
            }
            return base.Select(entityType);
        }

        private static readonly Expression NoArgs = Expression.Constant(new object[0]);

        public CustomSqlExpressionFactory(SqlExpressionFactoryDependencies dependencies) : base(dependencies) { }
    }
}

前两种方法只是为了方便 - 一种用于添加必要的管道，另一种用于在名称中编码 sql。实际工作在第三个类中，它替换了标准 EF Core 服务之一，拦截了负责表/视图/TVF 表达式映射的 Select 方法，并将特殊视图名称转换为 SQL 查询。

有了这些助手，您就可以按原样使用您的示例模型和DbSets。您只需将以下内容添加到派生的 DbContext 类中：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    // ...
    optionsBuilder.AddInlineSqlViewSupport(); // <--
}

并使用以下流畅的配置：


modelBuilder.Entity<MaxOrder>(builder =>
{
    builder.HasKey(e => e.CustomerId);
    builder.ToInlineView(
        @"SELECT CustomerId, SUM(Amount) AS TotalAmount 
          FROM Orders O 
          WHERE Id = (SELECT MAX(Id) 
                      FROM Orders 
                      WHERE CustomerId = O.CustomerId)
        GROUP BY CustomerId");
});

现在

var test = dbContext.Customers
    .Include(x => x.MaxOrder)
    .ToList();

将运行无错误并生成类似 SQL

SELECT [c].[Id], [c].[Name], [q].[CustomerId], [q].[TotalAmount]
FROM [Customers] AS [c]
LEFT JOIN (
    SELECT CustomerId, SUM(Amount) AS TotalAmount 
                          FROM Orders O 
                          WHERE Id = (SELECT MAX(Id) 
                                      FROM Orders 
                                      WHERE CustomerId = O.CustomerId)
                        GROUP BY CustomerId
) AS [q] ON [c].[Id] = [q].[CustomerId]

更重要的是，将正确填充Customer.MaxOrder 属性。任务完成:)

【讨论】：

这是我用例的优雅解决方案。谢谢！

【解决方案3】：

我会提出更通用且易于维护的解决方案：

public static class Associations
{
    [Expandable(nameof(MaxOrderImpl)]
    public static MaxOrder MaxOrder(this Customer customer)
        => throw new NotImplementedException();

    private static Expression<Func<Customer, MaxOrder>> MaxOrderImpl()
    {
        return c => c.Orders.OrderByDescending(o => o.Id)
            .Selec(o => new MaxOrder{ CustomerId = o.CustomerId, TotalAmount = o.Amount })
            .FirstOrDefault();
    }
}

然后你可以在查询中使用这个扩展：

dbcontext.Customers.Select(x => new CustomerDto 
{
    Id = x.Id,
    Name = x.Name,
    MaxOrder = x.MaxOrder()
});

查询是用 LINQ 编写的，可以轻松添加扩展并在其他查询中重用。

此类解决方案需要 LINQKit 并配置您的上下文：

builder
    .UseSqlServer(connectionString)
    .WithExpressionExpanding(); // enabling LINQKit extension

【讨论】：

转 https://www.likecs.com/ask-572591.html#sc=100

标签：Customer,Core,set,get,EF,db,CustomerId,SQL,public
From： https://www.cnblogs.com/wl-blog/p/16830984.html

How to configure navigation property for custom SQL in EF Core 5如何在 EF Core 5 中为自定义 SQL 配置导航属性

相关文章

赞助商

阅读排行