如何在EFCore5中為自定義SQL配置導航屬性

查看：

CREATE VIEW [dbo].[vwMaxOrder] 。
    視圖 
select
    c.Id CustomerId
    , Value = MAX(OrderTotal.Value)
來自的
    客戶c
    內部 join [Order] o
        on c.Id = o.CustomerId
    內部 join
        ()
            select ()
                oi.OrderId
                , Value = SUM(ooi.Price * oi.Quantity)
            來自的
                Oi.OrderItem
            group by
                oi.OrderId
        ) 訂單總數
            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($"客戶ID {customer.Id} 最大訂單金額。{customer.MaxOrder.Value}"）。)
            
            foreach (var order  in customer.Orders)
            {
                var total = order.Items.Sum(ooi => 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;")
            .選項。

        return new TestDbContext（opts）。
    }

    static void AddCustomers（TestDbContext db）
    {
        db.Customers.Add(new Customer()
        {
            Name = "Customer A"/span>
        });

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

        db.Items.Add(new Item()
        {
            名稱 = "專案B"。
        });

        db.SaveChanges()。
    }

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

        db.Orders.Add(new Order()
        {
            Created = DateTime.Now.AddDays（-1）。
            客戶 = 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()
                {
                    專案 = items[j],
                    數量 = i   j   1,
                    價格 = 20 - i * 2 - j * 3,
                });
            }
        }

        db.SaveChanges()。
    }
}

結果：

----------------------
客戶ID 1 最大訂單金額。81.00
訂單ID 1總計。54.00[/span]
訂單ID 2總計：54.00。81.00[/span]
----------------------
客戶ID 2 最大訂單金額。111.00 111.00
訂單ID 3總計。100.00[/span]
訂單ID 4總計：100.00。111.00

uj5u.com熱心網友回復：

Disclaimer:你所問的是不由EF Core 5.0自然支持的，因此所提供的解決方法很可能會在未來的EF Core版本中被破壞。使用它的風險自負，或者使用is支持的方法（映射到real資料庫視圖，包含所需的SQL，正如其他人所提到的）。

現在，問題來了。首先，你想要映射到 SQL 并在關系中使用的物體型別不能是無鍵的。這只是因為目前無鍵物體型別

只支持導航映射功能的一個子集，具體為：

• 它們可能永遠不會作為關系的主端。
• 它們不能有對所有物體的導航
。
• 它們只能包含指向常規物體的參考導航屬性。
• 物體不能包含指向無鍵物體型別的導航屬性。

在你的案例中，Customer定義了指向無鍵物體的導航屬性，這違反了最后一條規則。但是如果沒有它，你將無法使用Include，這就是所有這些的最終目的。

對于這個限制，沒有任何解決方法。即使你通過一些黑客手段來映射關系并獲得正確的 SQL 翻譯，導航屬性仍然不會被加載，因為所有 EF Core 相關的資料加載方法都依賴于變化跟蹤，而它需要有鍵的物體。

因此，物體必須是 "正常 "的（有鍵）。這沒有什么問題，因為查詢有唯一的列，定義了一對一的關系。然而，這又遇到了當前EF Core的限制--在模型最終確定期間，對于映射到SqlQuery的正常物體，你會得到NotImplemented例外。不幸的是，這是在關系模型最終確定程序中許多地方使用的靜態函式，這也是一個靜態方法，所以實際上不可能從外部攔截并修復它。

一旦你知道了這些問題（哪些是支持的，哪些是不支持的），下面是解決方法。支持的映射是普通物體到視圖的映射。所以我們將使用它（ToView而不是失敗的ToSqlQuery），但是代替名字的是提供用()括起來的SQL，以便能夠識別并從相關的EF Core元資料中提取它。請注意，EF Core并不驗證/關心你在ToTable和ToView方法中提供的名稱是什么--只是它們是否為null。

然后我們需要插入EF Core查詢處理管道，并將 "視圖名稱 "替換為實際的SQL。

下面是上述想法的實作（把它放在你的EF Core專案中的某個代碼檔案中）：

namespace Microsoft.EntityFrameworkCore
{
    
    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。
{
    使用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"/span>, 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訂單 
                                      WHERE CustomerId = O.CustomerId)
                        GROUP BY CustomerId
) AS [q] ON [c].[Id] = [q>.[CustomerId]

而且更重要的是，將正確地填充Customer.MaxOrder屬性。任務完成:)

uj5u.com熱心網友回復：

我想提出更普遍和更容易維護的解決方案：

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()。 } }

然后你可以在查詢中使用這個擴展：

查詢是用LINQ寫的，擴展可以很容易地添加，并在其他查詢中重復使用。

這種解決方案需要LINQKit和配置你的背景關系：

builder
    .UseSqlServer(connectionString)
    .WithExpressionExpanding(); //啟用LINQKit擴展。

uj5u.com熱心網友回復：

你想要的是某種計算列，但在Sqlserver中，只有標量運算式被允許成為一個表的計算列。 因此，你的解決方案是在客戶表的基礎上創建一個視圖，并將MaxOrder添加為列。

然后使新的列成為只讀：

modelBuilder.Entity<ViewCustomer>（) .Property(e => e.MaxOrder).HasDatabaseGeneratedOption（DatabaseGeneratedOption.Computed）。

移除

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

從你的dbcontext中。

你可以像表一樣插入和更新你的視圖。

標籤：

上一篇：實體化[org.springframework.orm.hibernate5.LocalSessionFactoryBean]失敗。工廠方法'sessionFactory'發生了例

下一篇：在MVC中使用EntityFramework更新表的新資料時遇到困難