Linq to Entities等同于T-SQL中筛选的内联视图

Question

我需要从数据库中进行查询。我了解如何在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#中的SQL编写为SQL(用于entityframework核心(，以便它生成与上面过滤的内联视图等效的视图。目标显然是返回始终包含树a->B->并且包含D或E，当且仅当这些节点也匹配二次过滤。请注意，在内联视图内进行过滤非常容易，但在内联视图外进行过滤非常困难，因为在没有匹配的D子级时，在内联图外进行过滤往往会导致C节点消失。这不是我们的本意。

感谢

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'实体为null或与筛选器匹配的地方说filter，如果你在这种情况下检查EF生成的sql，那就不正确了。正确的筛选必须在"join"中进行，就像上面的T-SQL一样。

PPS：是的，如果你除了父对象之外不进行过滤，你可以完全省去这一点，只写includes和where子句，但我想仔细想想，你会意识到，用一个适用于伟大孙子但不过滤孙子的术语进行过滤是很复杂的。如果你能以任何一种"形式"写出查询，我将不胜感激

Answer 1

除了缺乏自然的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]

Answer 2

足够公平。99.9%的EF关于翻译LEFT JOIN的问题是使用导航属性的简单失败。

EF Core将在下一版本中添加已筛选的Include，请参阅EF Core中Include的筛选。

或者你可以投影A，以及选择的子集合，比如这样：

var q = from a in db.A
select new 
{
a, 
Bs = a.Bs,
Ds = a.Bs.SelectMany( b => b.Ds ).Where(d => d.OtherID = dOtherId)
};