Linq to SQL: a left join in a grouped set makes Cross Apply / Outer Apply

Question

Basically, I make a report type query where I aggregate data from several tables and leave them joining one table.

it looks something like this:

var docs = from x in DB.Docs group x by x.PersonId into g select new { g.Key, totalSent = g.Sum(x => x.SentDate.HasValue ? 1 : 0), lastSent = g.Max(x => x.SentDate) ... }; var summary = from x in DB.People from y in docs.Where(y => y.Key == x.Id).DefaultIfEmpty() select new { x.Id, x.Name, y.totalSent, y.lastSent } 

I would expect this created sql that left to join DB.People to the docs results, but instead I get crazy stuff CROSS APPLY(( SELECT NULL AS [EMPTY]) as [t1] OUTER APPLY ...

I tried every version of the left join syntax that I can think of, I even wrapped the docs in a different request, and I get the same.

What am I missing?

Answer 1

 from x in DB.People from y in docs.Where(y => y.Key == x.Id).DefaultIfEmpty() 

The above will explicitly generate Cartesian results, which are then filtered.

Perhaps you wanted to join:

 from x in DB.People join y2 in docs on x.Id equals y2.Key into g from y in g.DefaultIfEmpty() 

---

How about this:

 from x in DB.People let g = x.Docs select new { x.Id, x.Name, totalSent = g.Sum(y => y.SentDate.HasValue ? 1 : 0), lastSent = g.Max(y => y.SentDate) }