c# – 如何在不使用查询语法的情况下在Entity Framework中执行左

发布时间：2020-12-15 08:05:11 所属栏目：百科来源：网络整理

导读：我正在尝试使用 linq的查询语法将此查询转换为基于方法的语法. 这是查询： var products = from p in context.Products join t in context.TopSellings on p.Id equals t.Id into g from tps in g.DefaultIfEmpty() orderby tps.Rating descending select ne

我正在尝试使用 linq的查询语法将此查询转换为基于方法的语法.

这是查询：

var products = from p in context.Products
                 join t in context.TopSellings
                 on p.Id equals t.Id into g
                 from tps in g.DefaultIfEmpty()
                 orderby tps.Rating descending
                 select new
                 {
                     Name = p.Name,Rating = tps.Rating == null ? 0 : tps.Rating
                 };

上面的查询产生这个SQL查询：

{SELECT 
    [Project1].[Id] AS [Id],[Project1].[Name] AS [Name],[Project1].[C1] AS [C1]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id],[Extent1].[Name] AS [Name],CASE WHEN ([Extent2].[Rating] IS NULL) THEN 0 ELSE [Extent2].[Rating] END AS [C1],[Extent2].[Rating] AS [Rating]
        FROM  [dbo].[Products] AS [Extent1]
        LEFT OUTER JOIN [dbo].[TopSellings] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
    )  AS [Project1]
    ORDER BY [Project1].[Rating] DESC}

到目前为止,我尝试过的是这样的：

var products = context.Products
    .Join(inner: context.TopSellings.DefaultIfEmpty(),outerKeySelector: c => c.Id,innerKeySelector: y => y.Id,resultSelector: (j,k) => new { Name = j.Name,Rating = k.Rating == null ? 0 : k.Rating })
    .OrderByDescending(p => p.Rating);

这个产生一个不同的sql查询(当然,这与数据在程序中的使用方式有不同的含义)：

{SELECT 
    [Project1].[Id] AS [Id],CASE WHEN ([Join1].[Rating] IS NULL) THEN 0 ELSE [Join1].[Rating] END AS [C1]
        FROM  [dbo].[Products] AS [Extent1]
        INNER JOIN  (SELECT [Extent2].[Id] AS [Id],[Extent2].[Rating] AS [Rating]
            FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
            LEFT OUTER JOIN [dbo].[TopSellings] AS [Extent2] ON 1 = 1 ) AS [Join1] ON [Extent1].[Id] = [Join1].[Id]
    )  AS [Project1]
    ORDER BY [Project1].[C1] DESC}

您的答案将会非常有用,非常感谢！

解决方法

通常,您可以通过访问查询的Expression属性来获取从任何查询表达式中使用的确切表达式.然后只需分析该表达式并重现它.

var expr = products.Expression;

另一方面,使用查询语法的每个表达式都具有直接的转换.与子句的部分连接对应于GroupJoin(),而来自子句的额外对应于SelectMany().这应该产生一个等价的查询：

var products = context.Products.GroupJoin(context.TopSellings,p => p.Id,t => t.Id,(p,g) => new { p,g })
    .SelectMany(x => x.g.DefaultIfEmpty(),(x,tps) => new { x.p,x.g,tps })
    .OrderByDescending(x => x.tps.Rating)
    .Select(x => new { x.p.Name,Rating = x.tps.Rating == null ? 0 : x.tps.Rating });

但是你可以删除一些冗余,削减不再使用的变量并利用一些有用的运算符.请注意,它可能会影响生成的实际查询,因此它不完全匹配,但它应该足够接近.

var products = context.Products.GroupJoin(context.TopSellings,g) => g.DefaultIfEmpty()
        .OrderByDescending(tps => tps.Rating)
        .Select(tps => new { p.Name,Rating = tps.Rating ?? 0 })
);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!