Linq to SQL：左连接分组集进行交叉应用/外部应用

发布于 2024-10-21 16:10:25 字数 889 浏览 7 评论 0原文

基本上，我正在进行报告类型查询，其中聚合来自多个表的数据并将其连接到单个表。

它看起来有点像这样：

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
              }

我希望这创建的sql将DB.People连接到docs的结果，但相反我得到了一些疯狂的CROSS APPLY (( SELECT NULL AS [EMPTY]) as [t1] OUTER APPLY ... stuff。

我已经尝试了我能想到的左连接语法的每种变体，我什至包装了 docs 在另一个查询中，我得到了同样的东西

。

原文

Basically I'm making a report type query where I am aggregating data from multiple tables and left joining it to a single table.

it looks a bit 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 that this created sql that left joined DB.People to the results of docs but instead I get some crazy CROSS APPLY(( SELECT NULL AS [EMPTY]) as [t1] OUTER APPLY ... stuff.

I've tried every variant of the left join syntax I can think, I've even wrapped docs in another query, and I get the same thing.

What am I missing?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

白日梦 2024-10-28 16:10:25

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

上面将清楚地生成笛卡尔结果，稍后对其进行过滤。

也许您想加入：

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

这样怎么样：

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)
}

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

The above will clearly generate cartesian results which are later filtered.

Perhaps you meant 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)
}

回复收藏 0 原文

~没有更多了~