c# - 如何在不使用查询语法的情况下在Entity Framework中进行左外部联接？

我正在尝试使用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],
    [Project1].[Name] AS [Name],
    [Project1].[C1] AS [C1]
    FROM ( SELECT
        [Extent1].[Id] AS [Id],
        [Extent1].[Name] AS [Name],
        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;

另一方面，每个使用查询语法的表达式都具有直接的翻译。与该子句的into部分相连的连接对应于GroupJoin()，而额外的from子句对应于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,
    p => p.Id, t => t.Id,
    (p, g) => g.DefaultIfEmpty()
        .OrderByDescending(tps => tps.Rating)
        .Select(tps => new { p.Name, Rating = tps.Rating ?? 0 })
);