StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSelecting aggregate (grouped) statistics with records in a LINQ-to-Entities Query
text
Body
copied!<p>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.</p> <p>The original SQL used a standard select left-joined to the aggregate information, similar to this:</p> <pre><code>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 </code></pre> <p>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)</p> <p>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.</p> <p>I had some success breaking out the statistics into a separate query:</p> <pre><code>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 } ); </code></pre> <p>Unfortunately, the join used in the LINQ query filters out all users who have not submitted any articles. </p> <p>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. </p> <p>What are the best practices for combining detail rows and aggregate information in this way?</p> <p>Thanks!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload