linq到实体等同于t-sql中的筛选内联视图

2w3kk1z5 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(333)

我需要从数据库中查询。我了解如何用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（对于entityframework core）来编写sql，以便它生成与上面过滤的内联视图等效的视图。很明显，我们的目标是返回一个结果集，该结果集总是包含树a->b->c，并且当且仅当这些节点也匹配二次过滤时才包含d或e。注意，在内联视图中进行过滤非常容易，但是在内联视图之外进行过滤非常困难，因为在内联视图之外进行过滤往往会导致c节点在没有匹配的d子级时消失。这不是目的。
谢谢
附言：为了澄清，你可以第一次尝试将上述内容写成：

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实体存在的那些行，因此如果不存在，整个堆栈将为空。如果你想说filter，其中d实体为null或者与filter匹配，如果你在这种情况下检查ef生成的sql，那么它是不正确的。正确的过滤必须发生在“join”中，就像上面的t-sql一样。
pps：是的，如果你除了父对象之外没有过滤，你可以完全省去这个，只写includes和where子句，但是我想经过思考，你会意识到，用一个术语来过滤一个伟大的孙子，但不过滤孙子是复杂的。如果你能用这两种形式写出来，我将不胜感激。

sql sql-server linq entity-framework-core inline-view

来源：https://stackoverflow.com/questions/63059747/linq-to-entities-eqiuvalent-to-a-filtered-inline-view-in-t-sql

2条答案

按热度按时间

m3eecexj1#

除了缺乏自然的 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方法是使用复合连接键将 predicate 推入连接条件（从而不过滤掉外部连接结果）：

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中的手动连接来查询实体：

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]

赞(0）回复(0）举报 2021-07-24

wsxa1bj12#

很公平。99.9%的关于翻译左连接的ef问题都是因为没有使用导航属性。
ef core正在下一个版本中添加筛选的包含请参阅筛选包含在ef core中。
或者您可以投影一个，以及选定的子集合，如下所示：

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)
        };

赞(0）回复(0）举报 2021-07-24

我来回答

linq到实体等同于t-sql中的筛选内联视图

2条答案

相关问题

热门标签

最新问答