Selecting aggregate (grouped) statistics with records in a LINQ-to-Entities Query

I have a query I'm attempting to port from SQL (T-SQL) to LINQ-to-Entities 4.0 (C#). The result set contains a combination of standard "detail rows" as well as aggregate "statistic" information.

The original SQL used a standard select le开发者_运维问答ft-joined to the aggregate information, similar to this:

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

A left join is used rather than subqueries in the SELECT statement for performance reasons - more than one aggregate statistic is returned (total posts and the date of the last post)

I've had some partial success converting it to a LINQ-to-Entities query in C# 4.0, but I'm not entirely sure how the join should tie in with the group statement. I imagine I'm thinking about this in terms of SQL and not using LINQ correctly.

I had some success breaking out the statistics into a separate query:

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

Unfortunately, the join used in the LINQ query filters out all users who have not submitted any articles.

Switching to the equivalent of an outer join by including DefaultIfEmpty causes other problems - I can only return "null" for TotalPosts instead of 0. Even with "TotalPosts = (s.TotalPosts == null) ? 0 : s.TotalPosts" in the select, an exception is thrown unless the TotalPosts property is nullable.

What are the best practices for combining detail rows and aggregate information in this way?

Thanks!

Try this:

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

One option you have is to make sure that the appropriate properties in the stats query are nullable. LINQ-to-entities will make the necessary adjustments to make this work if at all possible. Then perform the left outer join as usual.

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

To get an outer join you need to use DefaultIfEmpty. To solve the null issue you might try

TotalPosts = s.TotalPosts.GetValueOrDefault(),

or if s.TotalPosts somehow does not turn up as int? you might try hacks like

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