How do I use Count and Group in a select query in Linq?

Question

I had the following Linq code:

    var allRequests = model.GetAllRequests();

    var unsatisifedRequests = (from request in allRequests
                               where request.Satisfied == false
                               select request)
                               .OrderBy(r => r.RequestedOn)
                               .GroupBy(r => r.RequestedCountryId);

After which I then did a foreach over unsatifiedRequests building a new TOARequestListSummary object for each. This meant if I "returned" 4 items from the query, it would make 4 calls to the DB, once per loop of the foreach to grab the individual rows.

This seems to be the wrong way to use Linq, so I tries to convert this query to one which used projections to return the TOARequestListSummary objects directly and I came up with:

var summaries = (from request in allRequests
                             where request.Satisfied == false
                             group request by request.RequestedCountryId into requestGroups
                             select new TOARequestListSummary
                             {
                                 CountryName = requestGroups.First().RequestedCountry.Description,
                                 RequestCount = requestGroups.Count(),
                                 FirstRequested = requestGroups.First().RequestedOn
                             });

But when I run this, I get the following exception:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I have got as far as knowing that the Linq equivalent to EXISTS is Contains, but I have no idea how to indroduce this into the query.

Answer 1

This should work for you:

var summaries = (from request in allRequests
                 where request.Satisfied == false
                 group request by request.RequestedCountry into g
                 select new TOARequestListSummary
                 {
                     CountryName = g.Key.Description,
                     RequestCount = g.Count(),
                     FirstRequested = g.Min(i => i.RequestedOn)
                 });

In your original version of this query (the second one you posted), your group's key was the RequestedCountryId. Though this will technically be grouping on that, you actually want to use the associated object. This way you'll have easy access to the needed properties and won't need to worry about grabbing the first item.

Answer 2

Sorry, this is an answer, rather than an additional comment to Ryan's answer, but it is too long to fit...

This gets very strange. In LinqPad the following works a treat:

from request in TOARequests
where request.Satisfied == false
&& request.Active == true
orderby request.RequestedOn
group request by request.RequestedCountry into g
select new 
{
    CountryName = g.Key.Description,
    RequestCount = g.Count(),
    FirstRequested = g.First().RequestedOn
}

But the following throws the same translation exception in C#

var summaries = (from request in context.Repository<TOARequest>()
                             where request.Satisfied == false
                             && request.Active == true
                             orderby request.RequestedOn
                             group request by request.RequestedCountry into g
                             select new
                             {
                                 CountryName = g.Key.Description,
                                 RequestCount = g.Count(),
                                 FirstRequested = g.First().RequestedOn
                             }).ToList();

The only difference I can see if the ToList(), but even without that when I try to enumerate the list, it throws the exception.