macsux macsux - 2 months ago 6
SQL Question

How do you left join using "date between" operator in linq?

I have two tables. The parent table has a single date column, and child table has 2 date columns (From / To). I need to make a left join from parent to child where parent's date column is between one in child. In sql this would look something like this:

select p.cob, count(*) from parent p
left join child c on p.cob between c.effective and c.expiry
group by p.cob

How does one write this in linq - I'm a bit stuck here....


This should be what you are looking for

var query = from p in context.Parent
            from c in context.Child.Where(x => p.cob >= x.effective)
                                   .Where(x => p.cob <= x.expiry)
            group p by p.cob into pg
            select new
              cob = pg.Key,
              count = pg.Count()