Left Outer Join on Multiple Columns in LINQ not receiving and rows

Question

I'm trying to do a left outer join with linq on on 2 Lists of the same type. I've been following the syntax I see in examples online, but my left join is ending up with a count of 0 values. The 2 lists being joined are not empty so I should definitely be getting some results however I can't seem to determine what is wrong with the syntax. Any help would be greatly appreciated.

var leftOuterJoin = from r in received
                    join rs in reserved.DefaultIfEmpty()
                    on new {a = r.ProductId, b = r.WarehouseSectionId } equals new { a = rs.ProductId, b =  rs.WarehouseSectionId } into joinedL
                    from rs in joinedL.DefaultIfEmpty()
                    select new
                    {
                        SKU = r.SKU,
                        ProductName = r.ProductName,
                        QTY = r.QTY,
                        PhysicalStock = (rs.QTY != null && rs.QTY > 0) ? r.QTY + rs.QTY : r.QTY,
                        WarehouseSection = r.WarehouseSection,
                        WarehouseName = r.WarehouseName,
                        ProductId = r.ProductId,
                        WarehouseSectionId = r.WarehouseSectionId
                    };

Edit: I am able to make the query return values by commenting out Physical Stock in the select but I can still not figure out a reason for this. It looks like this error is caused by using the rs.qty variable, if I change any of the fields to rs.qty, it will trigger the same error. All of the rs.qty fields have values however there are more r items than rs items

//PhysicalStock = (rs.QTY != null && rs.QTY > 0) ? r.QTY + rs.QTY : r.QTY,

I think here "new {a = r.ProductId, b = r.WarehouseSectionId } equals new { a = rs.ProductId, b = rs.WarehouseSectionId }" you have a problem.... That equal will compare 2 references from 2 instances and not the values from those new anonymous objects created. You should check how the real SQL statement looks like after this query. — D A, Oct 28 '21 at 08:42
@DA That's not true, the generated SQL will usually be along the lines of `left join reserved rs on r.ProductId = rs.ProductId and r.WarehouseSectionId = rs.WarehouseSectionId` — Charlieface, Oct 28 '21 at 09:30
`from rs in joinedL.DefaultIfEmpty()` seems superfluous. Does `received` definitely have items in it? — Charlieface, Oct 28 '21 at 09:31
No, `reserved.DefaultIfEmpty()` is not necessary. The part `from rs in joinedL.DefaultIfEmpty()` is what turns the group join (`into joinedL`) into a flattened left outer join. See also https://stackoverflow.com/questions/15595289/linq-to-entities-join-vs-groupjoin/15599143#15599143. — Gert Arnold, Oct 28 '21 at 11:35
@Charlieface, yes received definitely has values in it, I confirmed with the debugger beforehand, atleast in this case, but not necessarily in every use of the function — nightwolf555, Oct 28 '21 at 23:20
Cannot reproduce https://dotnetfiddle.net/wBh01k. Are you sure this is your exact query? — Charlieface, Oct 28 '21 at 23:28
Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. — philipxy, Oct 30 '21 at 00:51
Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL include DDL & tabular initialization code. When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. [ask] [Help] — philipxy, Oct 30 '21 at 00:52

score 1 · Answer 1 · answered Oct 30 '21 at 02:05

I managed to fix this problem. The issue was with this line:

PhysicalStock = (rs.QTY != null && rs.QTY > 0) ? r.QTY + rs.QTY : r.QTY,

Instead of doing the null check on qty I should have been doing the null check on rs to make sure the object was not null

Fix:

   PhysicalStock =  rs != null ? rs.QTY + r.QTY : r.QTY,

Left Outer Join on Multiple Columns in LINQ not receiving and rows

1 Answers1