我有两个表。父表有一个日期列,子表中有2日期列(自/至)。我需要从父左连接到子女,父母的日期列是在孩子之间。在SQL这将是这个样子:
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
一个人如何写在LINQ - 我有点坚持在这里......
How does one write this in linq - I'm a bit stuck here....
这应该是你在找什么
var query = from p in context.Parent
from c in context.Child.Where(x => p.cob >= x.effective)
.Where(x => p.cob <= x.expiry)
.DefaultIfEmpty()
group p by p.cob into pg
select new
{
cob = pg.Key,
count = pg.Count()
};