Linq left join returns inner join

Question

I am trying to perform an outer join in C# using Linq, the person mentoring me keeps saying I shouldn't try to do an outer join which isn't really an answer.

What I got from the other threads is that I need the .DefaultIfEmpty() where ever I may not have a record.

I tried it first on just the lines where there may be a missing information then added it to every line just to see if that was the problem.

Every time I run this I get only the inner join records. It works great other than it is not including the two records from my DB that only have information in the first two tables.

var sqlQuery =
from s in ctx.Suppliers
from sp in ctx.SupplierParts
    .Where(sp => sp.SupplierID == s.SupplierID)
    .DefaultIfEmpty()
from sm in ctx.SupplierManufacturerRelations
    .Where(sm => sm.SupplierPNID == sp.SupplierPNID)
    .DefaultIfEmpty()
from mp in ctx.ManufacturerParts
    .Where(mp => mp.MfgPNID.Equals(sm.MfgPNID))
    .DefaultIfEmpty()
from m in ctx.Manufacturers
    .Where(m => m.ManufacturerID.Equals(mp.ManufacturerID))
    .DefaultIfEmpty()
from im in ctx.ItemMasters
    .Where(im => im.PreID == mp.PreID)
    .Where(im => im.PartNumber == mp.PartNumber)
    .DefaultIfEmpty()
from c in ctx.ComponentClasses
    .Where(c => c.CCID == im.CCID)
    .DefaultIfEmpty()
from um in ctx.UnitsOfMeasures
    .Where(um => um.UOMID == sp.UOMID)
    .DefaultIfEmpty()

select new
{ my variables}

var querylist = sqlQuery.Where(n => n.SupplierID == thisSupplier).ToList();

I also tried

from s in ctx.Suppliers
    join sp in ctx.SupplierParts on s.SupplierID equals sp.SupplierID
    join sm in ctx.SupplierManufacturerRelations on sp.SupplierPNID equals sm.SupplierPNID into spartgroup
from sm in spartgroup.DefaultIfEmpty()
    join mp in ctx.ManufacturerParts on sm.MfgPNID equals mp.MfgPNID into mpartgroup
from mp in mpartgroup.DefaultIfEmpty()
     join m in ctx.Manufacturers on mp.ManufacturerID equals m.ManufacturerID into mgroup
from m in mgroup.DefaultIfEmpty()
     join im in ctx.ItemMasters 
     on new { key1 = (int)mp.PreID, key2 = (int)mp.PartNumber }
     equals new { key1 = im.PreID, key2 = im.PartNumber }
     into tpartgroup
from im in tpartgroup.DefaultIfEmpty()
     join c in ctx.ComponentClasses on im.CCID equals c.CCID into fullgroup
from c in fullgroup.DefaultIfEmpty()
     join um in ctx.UnitsOfMeasures on sp.UOMID equals um.UOMID

This SQL query works and doesn't omit the rows

    SELECT Supplier.SupplierID
         , SupplierPart.SupplierPNID
         , SupplierPart.SupplierPN
         , SupplierPart.Description
         , SupplierManufacturerRelation.MfgPNID
         , ManufacturerPart.PreID
         , ManufacturerPart.PartNumber
         , ItemMaster.CCID
         , ItemMaster.Description AS Expr1
      FROM Supplier  
Inner JOIN SupplierPart 
        ON Supplier.SupplierID = SupplierPart.SupplierID 
 Left JOIN SupplierManufacturerRelation 
        ON SupplierPart.SupplierPNID = SupplierManufacturerRelation.SupplierPNID 
 Left JOIN ManufacturerPart 
        ON SupplierManufacturerRelation.MfgPNID = ManufacturerPart.MfgPNID 
 Left JOIN ItemMaster 
        ON ManufacturerPart.PreID = ItemMaster.PreID 
       AND ManufacturerPart.PartNumber = ItemMaster.PartNumber
     WHERE Supplier.SupplierID = 9

First of all, *if* you want to "translate" SQL joins you have to use the LINQ `join` statement, not `where`. But you should avoid using LINQ `join` altogether. Use navigation properties, i.e. the associations in the LINQ class model. — Gert Arnold, Feb 19 '18 at 17:29
Are you using LINQ-to-SQL or EntityFramework or some other ORM? — Rand Random, Feb 19 '18 at 17:32
I'm fairly certain I'm using LINQ-to-SQL I'm using EntityFramwork for normal database interaction but this was to avoid having to pull all the tables and combine them in in the program which is what I did originally. — NeoCowboy, Feb 19 '18 at 17:42
so you are using EntityFramework and not LINQ-to-SQL, LINQ-to-SQL is a ORM on its own and I highly doubt you are using 2 ORMs at the same time - so since you are using an ORM, do as @GertArnold told so, instead of trying to join the tables yourself - use the provided `Navigation Properties` from EntityFramework so your first "join" should be simply written as `var supplierParts = ctx.Suppliers.SelectMany(x => x.SupplierParts)` will will give you a list of SupplierParts of all Suppliers - or if you had only one it would be `ctx.Suppliers.First().SupplierParts` — Rand Random, Feb 19 '18 at 17:49
Have you considered saving your SQL query in the database as a user stored procedure, and then from your .net code calling the user stored procedure? This would also let you tweak your sql code without the need to recompile your code at a later date. — Random_User, Feb 19 '18 at 17:55
See msdn : https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — jdweng, Feb 19 '18 at 17:56
@Random_User I asked about using stored procedures but was told that it is prefered for this project to do everything in the C# program. — NeoCowboy, Feb 19 '18 at 19:21
I based the query on information from https://stackoverflow.com/a/23558389/9230695 I had also tried what was refered to as the "old-fashioned" way in this artical but that wasn't working either and as is stated in this answer I found it much harder to read through. — NeoCowboy, Feb 19 '18 at 19:28
Your SQL query does an `INNER JOIN` on `SupplierPart`, why are you doing an outer join in your first code sample? — NetMage, Feb 19 '18 at 20:57
I tried using an outer join there to see if I could get it to work at all. One article I read suggested that any inner joins in the statement would make it want to do inner joins for everything. — NeoCowboy, Feb 19 '18 at 21:23

NetMage · Accepted Answer · 2018-02-20T16:53:19.860

For translating SQL to LINQ query comprehension:

Translate FROM subselects as separately declared variables.
Translate each clause in LINQ clause order, translating monadic operators (DISTINCT, TOP, etc) into functions applied to the whole LINQ query.
Use table aliases as range variables. Use column aliases as anonymous type field names.
Use anonymous types (new { ... }) for multiple columns.
Left Join is simulated by using a into join_variable and doing another from from the join variable followed by .DefaultIfEmpty().
Replace COALESCE with the conditional operator and a null test.
Translate IN to .Contains() and NOT IN to !...Contains()
SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
SELECT fields must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
Proper FULL OUTER JOIN must be handled with an extension method.

So from your SQL, your query should look like:

var ans = from s in ctx.Suppliers
          join sp in ctx.SupplierParts on s.SupplierID equals sp.SupplierID
          join sm in ctx.SupplierManufacturerRelations on sp.SupplierPNID equals sm.SupplierPNID into smj
          from sm in smj.DefaultIfEmpty()
          join mp in ctx.ManufacturerParts on sm?.MfgPNID equals mp.MfgPNID into mpj
          from mp in mpj.DefaultIfEmpty()
          join im in ctx.ItemMasters on new { key1 = (int)mp.PreID, key2 = (int)mp.PartNumber } equals new { key1 = im.PreID, key2 = im.PartNumber } into imj
          from im in imj.DefaultIfEmpty()
          select new {
              s.SupplierID, sp.SupplierPNID, sp.SupplierPN, sp.Description, sm.MfgPNID, mp.PreID, mp.PartNumber, im.CCID, Expr1 = im.Description
          };

Unless I'm missing something this is the same thing as my second attempt above with the addition of the lambda which then causes an error: Error CS8072 An expression tree lambda may not contain a null propagating operation — NeoCowboy, Feb 20 '18 at 14:38
Your second attempt is incomplete and doesn't match the SQL you provided. I'm sorry about the null operator, I forgot to delete all the `?` from the code - I test it using LINQ to Objects in LINQPad which doesn't handle null the way LINQ to SQL does. — NetMage, Feb 20 '18 at 16:54
I believe I found my ultimate problem, and actually both styles work. What I found was that in my select statement I was I was choosing im.PreID and im.PartNumber which are non-nullable int fields. While @NetMage's example worked because he properly translated the select from mp.PreID and mp.PartNumber which are nullable. — NeoCowboy, Feb 21 '18 at 14:37

Linq left join returns inner join

1 Answers1