CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
NeoCowboy
NeoCowboy

Reputation: 3

Linq left join returns inner join

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

Upvotes: 0

Views: 2770

Answers (1)

NetMage
NetMage

Reputation: 26917

For translating SQL to LINQ query comprehension:

  1. Translate FROM subselects as separately declared variables.
  2. Translate each clause in LINQ clause order, translating monadic operators (DISTINCT, TOP, etc) into functions applied to the whole LINQ query.
  3. Use table aliases as range variables. Use column aliases as anonymous type field names.
  4. Use anonymous types (new { ... }) for multiple columns.
  5. Left Join is simulated by using a into join_variable and doing another from from the join variable followed by .DefaultIfEmpty().
  6. Replace COALESCE with the conditional operator and a null test.
  7. Translate IN to .Contains() and NOT IN to !...Contains()
  8. SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
  9. SELECT fields must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
  10. 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
          };

Upvotes: 1

Related Questions