DefaultIfEmpty() in LINQ to SQL join causing duplicates

Question

Code:

var cons = from c in dc.Consignments
join p in dc.PODs ON c.ID equals p.Consignment into pg
from p in pg.DefaultIfEmpty()
...(other joins)...
select new {
...
PODs = pg
...
}

Basically, I want one row to be selected for each Consignment, and I want to select the object 'PODs' which should be a collection of PODs. This works, however I get an row for each POD there is - so if I have 3 POD's on a consignment, 3 rows will be returned for that consignment. Am I selecting the PODs incorrectly? If I take away the DefaultIfEmpty(), it strangely works fine and doesn't cause duplicates.

Answer 1

You're using a second from clause, which is effectively flattening things - but then you're still using pg in your select. The point of DefaultIfEmpty() is if you want a left outer join, effectively - where you would expect one result per valid combination.

I suspect you just want:

var cons = from c in dc.Consignments
join p in dc.PODs ON c.ID equals p.Consignment into pg
select new {
  ...
  PODs = pg
  ...
}

or maybe

var cons = from c in dc.Consignments
join p in dc.PODs ON c.ID equals p.Consignment into pg
select new {
  ...
  PODs = pg.DefaultIfEmpty()
  ...
}

... but the latter will give you a result with a single null entry in PODs when there weren't any PODs, which probably isn't what you were after.