How to make left outer join in Entity Framework without using query syntax?

Question

I am trying to convert this query using linq query syntax to method based syntax.

Here's the request:

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 creates 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 I have tried 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 creates another sql query (which, of course, has a different meaning with respect to how the data is 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 will be of great help and will be very grateful!

Answer 1

You can usually get the exact expression used from any query expression by accessing the Expression property of the query. Then simply analyze this expression and reproduce it.

 var expr = products.Expression; 

On the other hand, every expression using query syntax has a direct translation. Connections to the into part of this clause correspond to GroupJoin() , while additional from clauses correspond to SelectMany() . This should result in an equivalent query:

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

But you can remove some shorthand variables that are no longer used and use some useful operators. Just keep in mind that this may affect the actual query created so that 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>();