SQL to LINQ Left Outer Join with Multiple Conditions

Question

I am struggling with this left outer join with multiple conditions in LINQ. My initial SQL statement is as follows.

SELECT DISTINCT [Product].[prod_id],
    [Product].[brd_id],
    [Product].[prod_brdId],
    [Size].[size_id],
    [Size].[size_sort],
    [Bin].[bin_rack],
    [Bin].[bin_alpha]
  
  FROM [Proddetails]
  JOIN [Prodcolor]
  ON [Proddetails].[pcl_id] = [Prodcolor].[pcl_id]
  
  JOIN [Product]
  ON [Prodcolor].[prod_id] = [Product].[prod_id]
  
  JOIN [Size]
  ON [Proddetails].[pdt_size] = [Size].[size_id]
  
  LEFT OUTER JOIN [Bin]
  ON [Product].[prod_id] = [Bin].[prod_id]
  
  ORDER BY [Product].[brd_id], [Product].[prod_brdId], [Size].[size_sort]

And my corresponding LINQ statement in .NET

BinProds = (
from pd in applicationDbContext.Proddetails
  join pc in applicationDbContext.Prodcolors on pd.PclId equals pc.PclId
  join pr in applicationDbContext.Products on pc.ProdId equals pr.ProdId
  join sz in applicationDbContext.Sizes on pd.PdtSize equals sz.SizeId
  join bn in applicationDbContext.Bins on pr.ProdId equals bn.ProdId into ps                
  from bn in ps.DefaultIfEmpty()
  select new CoreBin
  {
    ... fields here
  }
).Distinct().OrderBy(i=>i.BrdId).ThenBy(j=>j.ProdBrdId).ThenBy(k=>k.SizeSort).ToList();

So, both of these execute successfully in SQL Server Studio and .NET respectively. However it's not giving me the exact result I'm looking for because it's missing one additional condition on the LEFT OUTER JOIN. Below is the one change I made to my SQL statement which gives me exactly what I want in SQL Server Studio. I just can't figure out how to adjust the LINQ to get the same result in my .NET project.

LEFT OUTER JOIN [Bin]
ON [Product].[prod_id] = [Bin].[prod_id]
AND [Bin].[size_id] = [Size].[size_id]

I'm hoping there is a reasonable adjustment. If you need my table outputs or db design, plz let me know.

Does this answer your question? [How to do joins in LINQ on multiple fields in single join](https://stackoverflow.com/questions/373541/how-to-do-joins-in-linq-on-multiple-fields-in-single-join) See also https://learn.microsoft.com/en-us/dotnet/csharp/linq/join-by-using-composite-keys — Charlieface, Mar 14 '21 at 01:43
By the way, why do you have a `distinct`, usually an indication that the joins haven't been though through, `Proddetails` and `Prodcolor` seem extraneous — Charlieface, Mar 14 '21 at 01:44
@Charlieface yes, it does seem extraneous. This is a quite unique situation in how we store our products (which have 1 or more colors which have 1 or more sizes per color). These are stored in bins but only by product and size. So I need the Prodcolor join to bridge Product and Proddetails but I don't need the extra records so I'm using DISTINCT to resolve that. Currently testing out these recommended solutions. — Todd Wunsch, Mar 14 '21 at 17:21
Still don't get it, because no information is added to the query from those tables except for the fact that a `product` must exist in them. Perhaps you are missing some filters on those tables. And basically, the better way to do this kind of check is with an `exists`, and then you don't need `distinct`, which can cause inefficiency — Charlieface, Mar 14 '21 at 21:20

score 0 · Accepted Answer · answered Mar 14 '21 at 13:30

0

This one should give you desired SQL:

.....
  join bn in applicationDbContext.Bins 
     on new { pr.ProdId, sz.size_id } equals new { bn.ProdId, bn.size_id } into ps                
  from bn in ps.DefaultIfEmpty()
  select new CoreBin
  {
    ... fields here
  }
.....

answered Mar 14 '21 at 13:30

Svyatoslav Danyliv

21,911
3
16
32

This is exactly what I needed. Worked brilliantly!! Thank you!! – Todd Wunsch Mar 14 '21 at 18:49

SQL to LINQ Left Outer Join with Multiple Conditions

1 Answers1