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

Answer 2

using EF

 AAWSADBEntitiesContext = new AAWSA_DBEntitiesContext();
        //    .Where(pp1 => ((zemechaObj.DriverId == pp1.DriverId) || (zemechaObj.DriverId == pp1.DriverId)))
        var myresult =( from zemechaObj in AAWSADBEntitiesContext.WaterSupplyForwardedZemechResourses
                        where zemechaObj.CompanyId == companyId && zemechaObj.Status == WaterSupplyServiceRequest.Shared.ToString()

                        from driverObj in AAWSADBEntitiesContext.tbl_Driver
                               .Where(driver => ((zemechaObj.DriverId == driver.DriverId) ))
                                 .DefaultIfEmpty()
                            //fromBranch
                        from fromBranch in AAWSADBEntitiesContext.tbl_CompanyRegistrationInformation
                              .Where(fromB => ((zemechaObj.FromBranchId == fromB.CompanyId)))

                            //toBranch
                        from toBranch in AAWSADBEntitiesContext.tbl_CompanyRegistrationInformation
                              .Where(toB => ((zemechaObj.ToBranchId == toB.CompanyId)))
                                //vehicle
                             from vehicleObj in AAWSADBEntitiesContext.tbl_Vehicle
                                .Where(veh => ((zemechaObj.VehicleId == veh.VehicleId)))
                                  .DefaultIfEmpty()
                                 //assistant one
                        from DriverAssistantOneObj in AAWSADBEntitiesContext.tbl_DriverAssistant
                               .Where(driverAssistOne => ((zemechaObj.DriverAssitantFirstID == driverAssistOne.DriverAssistantId)))
                                 .DefaultIfEmpty()
                            //assistant one
                        from DriverAssistantTwoObj in AAWSADBEntitiesContext.tbl_DriverAssistant
                               .Where(driverAssistTwo=> ((zemechaObj.DriverAssitantSecondID == driverAssistTwo.DriverAssistantId)))
                                 .DefaultIfEmpty()
                            select new BranchResourceForZemechaEntities()
                            {
                                ForwaredResourseID = zemechaObj.ForwaredResourseID,
                                ServiceStartDate = zemechaObj.ServiceStartDate.ToString(),
                                ServiceEndDate = zemechaObj.ServiceEndDate.ToString(),
                                ForwaredDate = zemechaObj.ForwaredDate.ToString(),
                                Status = zemechaObj.Status,
                                Comment = zemechaObj.Comment,
                                //from Branch
                                FromBranchName = fromBranch.CompanyName,
                                //To Branch
                                ToBranchName = toBranch.CompanyName,
                                VehicleId = zemechaObj.VehicleId,
                                //Vehicle info
                                PlateNumber = vehicleObj.PlateNumber+" ",
                                DriverId = zemechaObj.DriverId,
                                //Driver Full Name
                                DriverFullName = driverObj.FirstName + " " + driverObj.MiddleName + " " + driverObj.LastName,
                                // Driver Assitant one Full Name
                                DriverAssitantFirstID = zemechaObj.DriverAssitantFirstID,
                                DriverAssistantOneFullName = DriverAssistantOneObj.FirstName + " " + DriverAssistantOneObj.MiddleName + " " + DriverAssistantOneObj.LastName,
                                // Driver Assitant Two Full Name
                                DriverAssitantSecondID = zemechaObj.DriverAssitantSecondID,
                                DriverAssistantTwoFullName = DriverAssistantTwoObj.FirstName + " " + DriverAssistantTwoObj.MiddleName + " " + DriverAssistantTwoObj.LastName,

                                CompanyId = zemechaObj.CompanyId,
                                FromBranchId = zemechaObj.FromBranchId,
                                ToBranchId = zemechaObj.ToBranchId,
                                BrLoggedUserId = zemechaObj.BrLoggedUserId.ToString()

                            }).ToList();
        return myresult.ToList<BranchResourceForZemechaEntities>();