1

I am trying to come up with a c# linq to left join two sql tables with a default value set when no match is found

In Months table I have following data

MonthId Name
1       Jan
2       Feb
3       Mar
4       Apr
5       May

mData has below Data

MonthId Count 
1       10    
2       20
3       5

and I am expecting an outcome like below

   Name     Count 
    Jan       10    
    Feb       20
    Mar       5   
    Apr       0
    May       0

I have below query which returns only when a match is found

from p in Months
    join g in mData on p.MonthId equals g.MonthId
    select new {
    p.Name,
    g.Count
    }
DoIt
  • 3,270
  • 9
  • 51
  • 103
  • Possible duplicate of [LEFT OUTER JOIN in LINQ](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) – Hadi Jan 29 '18 at 23:01

1 Answers1

1

You need to throw the result of the join into a group and call DefaultIfEmpty:

from p in Months
    join g in mData on p.MonthId equals g.MonthId into monthData
    from md in monthData.DefaultIfEmpty(new MData{Count = 0})
    select new {
        p.Name,
        md.Count
    }

Here is the MSDN for DefaultIfEmpty()

Jacob Lambert
  • 7,449
  • 8
  • 27
  • 47