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.
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.