小贝子编程

如何在EF Core 5中为自定义SQL配置导航属性



我有一个自定义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;}
}

DbContext

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

ModelBuilder

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")

免责声明:你问的是而不是EF Core 5.0自然支持，因此所提供的解决方案很可能在未来的EF Core版本中失效。使用它的风险由您自己承担，或者使用所支持的(映射到包含所需SQL的真实数据库视图，正如其他人所提到的)。

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

只支持导航映射功能的一个子集，特别是:

  • 它们可能永远不会作为关系的主要端。
  • 他们可能没有导航到自己的实体
  • 它们只能包含指向常规实体的引用导航属性。
  • 实体不能包含指向无键实体类型的导航属性。

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

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

因此，实体必须为"normal";(关键)。这没有问题，因为查询有唯一的列，它定义了一对一的关系。然而，这触及了当前EF Core的另一个限制——在模型最终化期间，映射到SqlQuery的普通实体会得到NotImplemented异常。不幸的是，这是在static中函数在关系模型终结的许多地方使用，它也是一个静态方法，因此实际上不可能从外部拦截和修复它。

一旦您知道了问题(支持什么和不支持什么)，这里是解决方案。支持的映射关系为普通实体。因此，我们将使用(ToView而不是失败的ToSqlQuery)，但不是名称将提供包含()的SQL，以便能够从相关的EF Core元数据中识别和提取它。请注意，EF Core并不验证/关心您在ToTable和ToView方法中提供的名称-只关心它们是否为null。

然后我们需要插入EF Core查询处理管道并替换"视图名称";

以下是上述想法的实现(把它放在你的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查询。

有了这些帮助器，您就可以按原样使用示例模型和DbSet了。您所需要的只是将以下内容添加到派生的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();

将运行w/o错误并生成类似

的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属性。任务完成了:)

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

MaxOrder需要一个key，该key是到Customer的FK，并且为MaxOrder:Customer定义了1:1的关系。用.ToSqlQuery(<body of view>)替换.ToView("vwMaxOrder")调用来重现上面描述的异常。

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: 100.00
Order ID 4 total: 111.00

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

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

相关内容

  • 没有找到相关文章

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium