代码之家 › 专栏 › 技术社区 › ShadowChaser

在LINQ to Entities查询中选择包含记录的聚合(分组)统计信息

outer-join group-by linq-to-entities c#-4.0 linq

ShadowChaser · 技术社区 · 15 年前

我有一个查询试图从SQL(T-SQL)到LINQ,再到Entities 4.0(C#)。结果集包含标准“细节行”和聚合“统计”信息的组合。

SELECT 
    UserId, 
    Name, 
    Email, 
    ISNULL(Stats.TotalPosts, 0) as TotalPosts,
    Stats.LastPost
FROM Users
LEFT OUTER JOIN
(
    SELECT UserId, COUNT(*) as TotalPosts, MAX(DatePosted) as LastPost
    FROM Articles
    GROUP BY UserId
) as Stats ON Stats.UserId = Users.UserID

出于性能原因,在SELECT语句中使用左联接而不是子查询-将返回多个聚合统计信息(总文章数和最后一篇文章的日期)

我在C#4.0中将它转换为LINQ to Entities查询部分成功,但我不完全确定联接应该如何与group语句关联。我想我是从SQL的角度来考虑这个问题,而不是正确地使用LINQ。

我成功地将统计数据分解为一个单独的查询:

var stats =
(
    from a in entities.Articles
    group a by a.UserId into g
    select new
    {
        UserId = g.Key,
        TotalPosts = g.Count(),
        LastUpdated = g.Max(i => i.DatePosted)
    }
);

var query =
(
    from u in entities.Users
    join s in stats on u.UserId equals s.UserId
    orderby u.Name 
    select new UserListing()
    {
        UserId = u.UserId,
        Name = u.Name,
        Email = u.Email,
        TotalPosts = s.TotalPosts,
        LastUpdated = s.LastUpdated
    }
);

通过包含DefaultIfEmpty切换到与外部联接等效的联接会导致其他问题-对于TotalPosts,我只能返回“null”而不是0。即使是“TotalPosts=(s.TotalPosts==null)?0:s.TotalPosts“在select中,除非TotalPosts属性为空,否则将引发异常。

以这种方式组合细节行和聚合信息的最佳实践是什么?

谢谢!

3 回复 | 直到 15 年前

Hogan 15 年前

试试这个:

var query =
(
    from u in entities.Users
    join s in stats on u.UserId equals s.UserId into g
    from a in g.DefaultIfEmpty()
    orderby u.Name 
    select new UserListing()
    {
        UserId = u.UserId,
        Name = u.Name,
        Email = u.Email,
        TotalPosts = a.TotalPosts,
        LastUpdated = a.LastUpdated
    }
);

Jeff Mercado 15 年前

stats 查询可以为空。如果可能的话,LINQ-to-entities将进行必要的调整以使其工作。然后像往常一样执行左外连接。

var stats =
(
    from a in entities.Articles
    group a by a.UserId into g
    select new
    {
        UserId = g.Key,
        TotalPosts = (int?)g.Count(),
        LastUpdated = g.Max(i => i.DatePosted)
    }
);

var query =
(
    from u in entities.Users
    join s in stats on u.UserId equals s.UserId into joinedStats
    from s in joinedStats.DefaultIfEmpty() // do left outer join
    orderby u.Name 
    select new UserListing()
    {
        UserId = u.UserId,
        Name = u.Name,
        Email = u.Email,
        TotalPosts = s.TotalPosts,  // null if doesn't contain stats
        LastUpdated = s.LastUpdated // default DateTime if doesn't contain stats
    }
);

Albin Sunnanbo 15 年前

要获得外部连接,您需要使用 DefaultIfEmpty .

TotalPosts = s.TotalPosts.GetValueOrDefault(),

或者如果s.TotalPosts没有出现 int?

TotalPosts = ((int?)s.TotalPosts).GetValueOrDefault(0),