How to do a left outer join in Entity Framework without using the query syntax?

Question

I'm trying to convert this query using linq's query syntax to a method based syntax.

Here's the query:

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

the query above produces this sql query:

{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}

So far what I've tried is something like this:

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

and this one produces a different sql query(Which of course has a different meaning with regards to how the data is being used in the program ):

{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}

Your answers would be of great help and be very much appreciated!

Answer 1

You generally could get the exact expression used from any query expression by accessing the Expression property of the query. Then just analyze that expression and reproduce it.

var expr = products.Expression;

On the other hand, every expression using the query syntax has a straight forward translation. Joins with the into part of the clause corresponds to a GroupJoin() whereas extra from clauses corresponds to a SelectMany(). This should produce an equivalent query:

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

But you could remove some redundancies cutting variables that are no longer used and take advantage of some useful operators. Just be aware that it may affect the actual query produced so it doesn't match exactly, but it should be close enough.

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