sql - Linq to Entities 相当于 T-SQL 中的过滤内联视图
问题描述
我有一个来自我需要做的数据库的查询。我了解如何在 T-SQL 中编写查询。真正的查询要复杂得多,但模式的简单说明是这样的:
SELECT * FROM [dbo].[A] AS a
LEFT JOIN dbo.[B] AS b ON a.ID = b.ParentID
LEFT JOIN dbo.[C] AS c ON y.ID = c.ParentID
LEFT JOIN
(
SELECT * FROM dbo.[D]
WHERE OtherID = @otherID
) AS d ON c.ID = d.ParentID
LEFT JOIN
(
SELECT * FROM dbo.[E]
WHERE OtherID = @otherID
) AS e ON e.ID = e.ParentID
WHERE A.ID = @Id
我需要在 C# linq to sql 中编写该 SQL(用于实体框架核心),以便它生成与上面过滤的内联视图等效的内容。目标显然是返回一个始终包含树 A->B->C 并包含 D 或 E 的结果集,当且仅当这些节点也匹配二级过滤。请注意,在内联视图内进行过滤非常容易,但在内联视图外进行过滤非常困难,因为内联视图外的过滤往往会导致 C 节点在没有匹配的 D 子节点时消失。那不是本意。
谢谢
PS:为澄清起见,您可能会首先尝试将上述内容编写为:
query = from a in context.A
join bt in context.B on a.ID equals bt.ParentID into btent
from b in btent.DefaultIfEmpty()
join ct in context.C on b.ID equals ct.ParentID into ctent
from c in ctent.DefaultIfEmpty()
join dt in context.D on c.ID equals dt.ParentID into dtent
from d in dtent.DefaultIfEmpty()
.Include(a => a.B).ThenInclude(b => b.C).ThenInclude(c => c.D)
.Where(a => a.ID = myPrimaryID && d.OtherId = myOtherID)
问题是“d”实体上的 where 子句仅返回存在 D 实体的那些行,因此如果不存在,则整个堆栈将为空。如果您尝试变得可爱并说出“d”实体为空或与过滤器匹配的过滤器,如果您在这种情况下检查 EF 生成的 sql,则它是不正确的。与上面的 T-SQL 一样,正确的过滤必须发生在“连接”内。
PPS:是的,如果您除了父对象之外不进行过滤,您可以完全省去这一点,只需编写包含和 where 子句,但我认为经过反思,您会意识到通过适用于曾孙但不过滤孙子是复杂的。如果您可以用任何一种“形式”写出查询,我将不胜感激。
解决方案
除了缺乏自然left outer join
语法,select
是最后一个,并且select *
需要匿名/具体类型投影(但它可以包含整个实体)之外,LINQ 支持与标准 SQL 相同的构造,包括内联子查询。
因此,可以按照示例 SQL 查询的确切方式编写 LINQ 查询:
from a in db.A
join b in db.B on a.ID equals b.ParentID
into a_b from b in a_b.DefaultIfEmpty()
join c in (from c in db.C where c.OtherID == myOtherID select c) on b.ID equals c.ParentID
into b_c from c in b_c.DefaultIfEmpty()
join d in (from d in db.D where d.OtherID == myOtherID2 select d) on c.ID equals d.ParentID
into c_d from d in c_d.DefaultIfEmpty()
select new { a, b, c, d }
EF Core 将其翻译为:
SELECT [s].[ID], [s0].[ID], [s0].[ParentID], [t].[ID], [t].[OtherID], [t].[ParentID], [t0].[ID], [t0].[OtherID], [t0].[ParentID]
FROM [SO6_A] AS [s]
LEFT JOIN [SO6_B] AS [s0] ON [s].[ID] = [s0].[ParentID]
LEFT JOIN (
SELECT [s1].[ID], [s1].[OtherID], [s1].[ParentID]
FROM [SO6_C] AS [s1]
WHERE [s1].[OtherID] = @__myOtherID_0
) AS [t] ON [s0].[ID] = [t].[ParentID]
LEFT JOIN (
SELECT [s2].[ID], [s2].[OtherID], [s2].[ParentID]
FROM [SO6_D] AS [s2]
WHERE [s2].[OtherID] = @__myOtherID2_1
) AS [t0] ON [t].[ID] = [t0].[ParentID]
另一种标准的 LINQ 方法是使用复合连接键将谓词推入连接条件(因此不会过滤掉外部连接结果):
from a in db.A
join b in db.B on a.ID equals b.ParentID
into a_b from b in a_b.DefaultIfEmpty()
join c in db.C on new { K1 = b.ID, K2 = myOtherID } equals new { K1 = c.ParentID, K2 = c.OtherID }
into b_c from c in b_c.DefaultIfEmpty()
join d in db.D on new { K1 = c.ID, K2 = myOtherID2 } equals new { K1 = d.ParentID, K2 = d.OtherID }
into c_d from d in c_d.DefaultIfEmpty()
select new { a, b, c, d }
翻译为:
SELECT [s].[ID], [s0].[ID], [s0].[ParentID], [s1].[ID], [s1].[OtherID], [s1].[ParentID], [s2].[ID], [s2].[OtherID], [s2].[ParentID]
FROM [SO6_A] AS [s]
LEFT JOIN [SO6_B] AS [s0] ON [s].[ID] = [s0].[ParentID]
LEFT JOIN [SO6_C] AS [s1] ON ([s0].[ID] = [s1].[ParentID]) AND (@__myOtherID_0 = [s1].[OtherID])
LEFT JOIN [SO6_D] AS [s2] ON ([s1].[ID] = [s2].[ParentID]) AND (@__myOtherID2_1 = [s2].[OtherID])
更紧凑的 LINQ 方式是使用相关子查询而不是连接:
from a in db.A
from b in db.B.Where(b => a.ID == b.ParentID).DefaultIfEmpty()
from c in db.C.Where(c => b.ID == c.ParentID && c.OtherID == myOtherID).DefaultIfEmpty()
from d in db.D.Where(d => c.ID == d.ParentID && d.OtherID == myOtherID2).DefaultIfEmpty()
select new { a, b, c, d }
EF Core 很高兴地将其翻译为:
SELECT [s].[ID], [s0].[ID], [s0].[ParentID], [t].[ID], [t].[OtherID], [t].[ParentID], [t0].[ID], [t0].[OtherID], [t0].[ParentID]
FROM [SO6_A] AS [s]
LEFT JOIN [SO6_B] AS [s0] ON [s].[ID] = [s0].[ParentID]
LEFT JOIN (
SELECT [s1].[ID], [s1].[OtherID], [s1].[ParentID]
FROM [SO6_C] AS [s1]
WHERE [s1].[OtherID] = @__myOtherID_0
) AS [t] ON [s0].[ID] = [t].[ParentID]
LEFT JOIN (
SELECT [s2].[ID], [s2].[OtherID], [s2].[ParentID]
FROM [SO6_D] AS [s2]
WHERE [s2].[OtherID] = @__myOtherID2_1
) AS [t0] ON [t].[ID] = [t0].[ParentID]
最后,EF Core 中最紧凑和首选的方法是在 LINQ to Entities 查询中使用导航属性而不是手动连接:
from a in db.A
from b in a.Bs.DefaultIfEmpty()
from c in b.Cs.Where(c => c.OtherID == myOtherID).DefaultIfEmpty()
from d in c.Ds.Where(d => d.OtherID == myOtherID2).DefaultIfEmpty()
select new { a, b, c, d }
EF Core 也将其翻译为:
SELECT [s].[ID], [s0].[ID], [s0].[ParentID], [t].[ID], [t].[OtherID], [t].[ParentID], [t0].[ID], [t0].[OtherID], [t0].[ParentID]
FROM [SO6_A] AS [s]
LEFT JOIN [SO6_B] AS [s0] ON [s].[ID] = [s0].[ParentID]
LEFT JOIN (
SELECT [s1].[ID], [s1].[OtherID], [s1].[ParentID]
FROM [SO6_C] AS [s1]
WHERE [s1].[OtherID] = @__myOtherID_0
) AS [t] ON [s0].[ID] = [t].[ParentID]
LEFT JOIN (
SELECT [s2].[ID], [s2].[OtherID], [s2].[ParentID]
FROM [SO6_D] AS [s2]
WHERE [s2].[OtherID] = @__myOtherID2_1
) AS [t0] ON [t].[ID] = [t0].[ParentID]
推荐阅读
- r - 删除第二个“_”后的部分列名
- javascript - 可从 rxjs 观察到
- list - Haskell - 使用列表计算复数
- python - 如何使用字典重命名列?
- javascript - 无法删除我附加的事件侦听器
- java - KNIME:比较一列是否包含另一列的子集
- r - 在管道中的同一对象上调用两个不同的函数 (%>%)
- java - 为什么我的登录和注册不起作用甚至重定向?
- c# - SAP.Middleware.Connector.RfcSerializationException:未找到 RFCID.TableContLZ 的结束标记
- javascript - 如何修复 TypeError:无法在我的 MEAN 堆栈应用程序中读取未定义的属性“地图”?