How to use Group By with Task<IEnumerable> in LINQ

I am new to the entity framework and LINQ. Trying to learn it by example. I have an Entity called “Participant” as below:

    public class Participant
    {
        public int Id { get; set; }
        public int Count { get; set; }
        public string Zip { get; set; }
        public string ProjectStatus { get; set; }
        public string IncomingSource { get; set; }
    }

I am trying to use Group by and return the result as Task<IEnumerable<Participant>>. The Sql Query that I found the is : SELECT Count(Id) as #, Zip FROM [database].[dbo].[Participants] GROUP BY Zip Order By Zip

The Code that I am trying to accomplish the same result is like below:

    public Task<IEnumerable<Participant>> GetResults()
    {

        var results = context.Participants
        .GroupBy(i => i)
        .Select(i => new { 
            Count = i.Key, 
            Zip = i.Count() 
            }
        ).ToList();

        return results;
    }

However, this gives me a conversion issue. The complete error stack is: Cannot implicitly convert type 'System.Collections.Generic.List<<anonymous type: project.API.Models.Participant Count, int Zip>>' to 'System.Threading.Tasks.Task<System.Collections.Generic.IEnumerable<project.API.Models.Participant>>'

I am not sure how to solve convert these. Any help would be appreciated.

Answer

When you use GroupBy, you want to make groups of elements that have something in common. The property that you want to be in common is specified in parameter keySelector.

With this parameter you say: please make groups of Paraticipants, all with the same value of the property specified in the keySelector.

In your case: you want to make groups of Participants that have the same value of ZIP. In other words: if you fetch two Participants that are in the same group, you want to be certain that they have the same value for ZIP.

So first, change your keySelector:

var result = dbContext.Participants.GroupBy(participant => participant.Zip)

The result is a sequence of Groups. Every Group has a Key, and every Group IS (not has!) a sequence of Participants. All Participants have a value for property Zip that equals the value of Key.

After that, you want to take every group, and from every group you want to make a new Participant object, that has only two properties filled

• Count is the number of Participants in the Group

• Zip is the Zip of any of the elements in the Group, which is, as we saw earlier the Key of the Group.

  .Select(groupOfParticipantsWithSameKey => new Participant { Count = groupOfParticipantsWithSameKey.Count(), Zip = groupOfParticipantsWithSameKey.Key, });

Did you notice that I changed the identifier i with a proper identifier. Choosing the proper identifier will help you identifying problems in LINQ. It might be a little more tying, but it helps you to understand what each element of the sequence your are processing represents.

By the way, there is an overload of Enumerable.GroupBy, the one with a parameter resultSelector. This makes your Select unnecessary.

var result = context.Participants
    .GroupBy(participanti => participant.Zip,

    // parameter resultSelector, take each common Zip, and all Participants that have this Zip
    // to make one new object
    (zip, participantsWithThisZip) => new Participant
    {
        Zip = zip,
        Count = participantsWithThisZip.Count(),
    });

This one is even easier to understand, because you have eliminated the identifier Key.

A small design improvement

You have created a method, that takes all Participants and returns one Participant per used Zip, where the Count is the number of Participants that have this Zip.

If you will be using this more often, then it would be a good idea to create a separate method for this, an extension method of IQueryable<Participant>. This way you can reuse the method with every sequence of Participants, not only all Participants within the database. See Extension Methods demystified

public static class ParticpantExtensions
{
    public static IQueryable<Participant> ToParticipantsWithSameZip(
        this IEnumerable<Participant> participants)
    {
        return participants.GroupBy(
        participanti => participant.Zip,
        (zip, participantsWithThisZip) => new Participant
        {
            Zip = zip,
            Count = participantsWithThisZip.Count(),
        });
    }
}

Usage:

Your original method:

Task<IList<Participant>> FetchParticipantsWithSameZipAsync() 
{
    using (var dbContext in new MyDbContext(...))
    {
        return await dbContext.ToParticipantsWithSameZip().ToListAsync();
    }
}

You can reuse it in the non-async version:

IList<Participant>> FetchParticipantsWithSameZipAsync() 
{
    using (var dbContext in new MyDbContext(...))
    {
        return dbContext.ToParticipantsWithSameZip().ToList();
    }
}

But now you can also intertwine it with other LINQ methods:

var newYorkParticipantsWithSameZip = dbContext.Participants
    .Where(participant => participant.State == "New York")
    .ToParticipantsWithSameZip()
    .OrderBy(participant => participant.Count())
    .ToList();

Several advantages:

• Reusable
• Code looks cleaner,
• Easier to understand what it does
• You can unit test it without a database: any IQueryable<Participant> will do.
• If you need to change ToParticipantsWithSameZip, there is only one place that you have to change and to rewrite the test.

So if you will be using it on several places: consider the extension method