EFCore中的CTE(公共表达式)
EFCore中的CTE(公共表达式)
我在我的数据库中有一个存储位置层次结构的表格。它有三列(Id, Name, ParentId)
。根据一个条件,我需要加载一些行以及它们一直到根节点的所有父节点。如果是在ADO中,我会使用以下语句。
with Temp as ( select * from Locations where Name like '%filter%' union all select Locations.* from Temp join Locations on Temp.ParentId = Locations.Id ) select * from Temp
我正在使用EFCore,并且通过几次搜索,我找到了How does Entity Framework work with recursive hierarchies? Include() seems not to work with it,How to do recursive load with Entity framework?以及其他一些较旧的解决方案。
我找到的所有解决方案要么硬编码了层次结构的深度(使用Include
),要么在C#中进行递归。在这种情况下,我的问题是什么是最佳解决方案呢?
我可以使用FromSqlRaw
(类似以下代码),但是我不喜欢在C#中手动编写查询语句。
var locations = DataContext.Locations .FromSqlRaw("MyQuery").ToList();
我正在使用EFCore 3.1.7
。
CTE(Common Table Expression)在EF Core中不被原生支持,这导致了问题的出现。在EF Core的GitHub页面上可以找到与此相关的问题,编号为#26486。然而,有一个名为linq2db的库支持CTE,并且可以与EF Core结合使用。
linq2db是一个强大的SQL查询库,支持在查询中使用CTE。它通过提供自己的实现来解决EF Core不支持CTE的问题。linq2db.EntityFrameworkCore是linq2db的一个扩展,它可以与EF Core连接起来,并允许在EF Core中使用CTE。
通过在代码中引用linq2db.EntityFrameworkCore库,我们可以轻松地开始使用CTE。下面是一个示例代码,展示了如何在EF Core中使用CTE:
using linq2db.EntityFrameworkCore; var optionsBuilder = new DbContextOptionsBuilder() .UseSqlServer(connectionString) .UseLinqToDB(); using (var dbContext = new MyDbContext(optionsBuilder.Options)) { var query = dbContext.MyTable .WithCTE("MyCTE", q => q .From () .Where(t => t.SomeColumn == 1) .Select(t => new { t.Id, t.Name })) .FromSqlRaw("SELECT * FROM MyCTE") .ToList(); }
在上面的代码中,我们首先使用linq2db.EntityFrameworkCore库的UseLinqToDB方法来配置EF Core上下文。然后,我们定义了一个CTE,命名为MyCTE,并且使用它来过滤另一个表中的数据。最后,我们使用FromSqlRaw方法执行基于CTE的查询,并将结果转换为列表。
通过使用linq2db和linq2db.EntityFrameworkCore,我们可以在EF Core中使用CTE,解决了EF Core不支持CTE的问题。这为我们提供了更多灵活性和功能,使我们能够更好地处理复杂的查询需求。