LINQ Outer Join Has Duplicates

I'm running this query in LINQPad. It works except that ProductSeries has duplicate records.

var query = from etaRecord in EtaRecord_0140

  join productSeriesRecord in ProductSeries
  on etaRecord.ProductSeriesID equals productSeriesRecord.ProductSeriesID
  into productSeriesGroup
  from productSeries in productSeriesGroup.DefaultIfEmpty()

  where etaRecord.State == "A"
  select new { EtaRecord = etaRecord, ProductSeriesRecord = productSeries };

query.Dump();

I tried using FirstOrDefault() instead of DefaultIfEmpty(), but I get this error:

An expression of type 'LINQPad.User.ProductSeries' is not allowed in a subsequent from clause in a query expression with source type 'System.Linq.IQueryable'. Type inference failed in the call to 'SelectMany'.

How can I get the FirstOrDefault() for ProductSeries so that there is only one row for each EtaRecord?

.NET fiddle is here: https://dotnetfiddle.net/kRrold

Jon Skeet
people
quotationmark

The problem is your extra from clause:

from productSeries in productSeriesGroup.DefaultIfEmpty()

You should ditch that, and just use:

let productSeries = productSeriesGroup.FirstOrDefault()

... or just use productSeriesGroup.FirstOrDefault() within the select clause, like this:

var query = from etaRecord in etaRecords            
            join productSeriesRecord in productSeriesRecords
            on etaRecord.ProductSeriesId equals productSeriesRecord.ProductSeriesId
            into productSeriesGroup
            select new { EtaRecord = etaRecord,
                         ProductSeriesRecord = productSeriesGroup.FirstOrDefault() };

With either change, the result is now:

Snuh 1 - null
Snuh 2 - null
Snuh 3 - null
Snuh 4 - Description A
Snuh 5 - null
Snuh 6 - Description B

I assume that's what you wanted.

people

See more on this question at Stackoverflow