[Solved]-LINQ Outer Join Has Duplicates-LINQ,C#

are you saying that the collection you are joining on has duplicates? because if so, you can just group the productseries collection ahead of time.

var query = from etarecord in etarecord_0140

join productseriesrecord in productseries.groupby(series => series.productseriesid).select(seriesgroup => seriesgroup.first())
on etarecord.productseriesid equals productseriesrecord.productseriesid
into productseriesgroup
from productseries in productseriesgroup.defaultifempty()

where etarecord.state == "a"
select new { etarecord = etarecord, productseriesrecord = etarecord };

query.dump();

now this is assuming that you are working with a static list, not a db. if it is a db connection then you should probably do the distinct on the results. it could be done in a similar manner after the fact.

i would do this in a subquery:

var query = from etarecord in etarecord_0140
            where etarecord.state == "a"
            select new 
            { 
                etarecord = etarecord,
                productseriesrecord = 
                  (from productseriesrecord in productseries
                   where productseriesrecord.productseriesid == etarecord.productseriesid
                   select productseriesrecord).firstordefault()
            };

in linq to objects this may be an inefficient operation because the subquery is executed for each etarecord, but since the whole statement is translated into sql the query optimizer will take care of an optimized execution plan.

that's the story for linq-to-entities.

linq-to-sql always seems to produce n + 1 queries for group joins (which is join - into) combined with firstordefault(). i've tried several scenarios but i can't get it to generate one query only. the only solution i could find generating one query is:

var query = from etarecord in etarecord_0140
            where etarecord.state == "a"
            from productseriesrecord in
                     productseries
                        .where(ps => ps.productseriesid == etarecord.productseriesid)
                        .take(1)
                        .defaultifempt()
            select new { etarecord = etarecord, productseriesrecord = productseries };

so the join syntax is abandoned and in a rather contrived way the first record of productseries belonging to a etarecord is queried.

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.

you should be able to add an extra filtering step to group by etarecord and just select the first record per group

ie

query  = (from r in query 
         group r by r.etarecord.etaid into results 
         select results.firstordefault());

it looks like you need grouping:

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"
group productseries by new { etarecord.productseriesid, etarecord } into g
select new 
       { 
         etarecord = g.key.etarecord, 
         productseriesrecord = g.select(x => x).firstordefault() 
        };

updated fiddle