Entity Framework: Group By example C#

In this guide you will see how to perform a group by operation on your data in Entity Framework and how to use aggregate functions such as SUM, MAX or COUNT.

The GROUP BY statement is usually used (mostly with aggregate functions) to group the result of your query by one or more columns.

Starting Point

We will use the simple database created in theĀ Entity Framework Database First Approach.

database-first-edmx

Yeah, yeah, I added Guns to the model, sue me.

Syntax

The first thing you have to know is the syntax:

SELECT [column-name]
FROM [table-name]
WHERE [where-condition]
GROUP BY [column/columns]
ORDER BY [column/columns]

Aggregate Functions

The group by statement is usually seen with an aggregate function, used to extrapolate useful information from the data.

Let’s consider the three more common functions used.

COUNT

The COUNT function gives us informations about the number of results for each value of the column we chose to group by.

Following the model, we can get..

  • The number of robot factories for each distinct location:

    using (RobotIndustryEntities context = new RobotIndustryEntities())
    {
        var robotFactories = context
            .RobotFactories.GroupBy(x => x.Location)
            .Select(x => new { Location = x.Key, Buildings = x.Count() });
    }

    Or

    var query = from r in context.RobotFactories
                group r by r.Location into g
                select new
                {
                  Location = g.Key,
                  Buildings = g.Count()
                };

    Result:
    group-by-count

  • The number of robot dogs armed for each location:
    SELECT f.Location, COUNT(d.RobotDogId)
    FROM RobotDog d
    JOIN RobotFactories f
    ON d.RobotFactoryId = f.RobotFactoryId
    WHERE d.Armed = 1
    GROUP BY f.Location
    var robotDogs = from d in context.RobotDogs
        join f in context.RobotFactories
        on d.RobotFactoryId equals f.RobotFactoryId
        where (d.Armed==true)
        select new { f.Location, d.RobotDogId } into x
        group x by new { x.Location } into g
        select new
        {
            Location = g.Key.Location,
            Count = g.Select(x => x.RobotDogId).Count()
        };

SUM

We could try to find the number of guns of robots with the same name:

SELECT Name, SUM(Guns)
FROM RobotDog
GROUP BY Name

Or

var robotDogs = context.RobotDogs
    .GroupBy(x => x.Name)
    .Select(g => new { Name = g.Key, Guns = g.Sum(x => x.Guns) });

Or maybe the number of guns in each location:

SELECT f.Location, SUM(d.Guns) AS Guns
FROM RobotFactories f
JOIN RobotDog d
ON f.RobotFactoryId = d.RobotFactoryId
GROUP BY (b.Location)

Or

var robotLocations = context.RobotFactories
    .Join(
        context.RobotDogs,
        f => f.RobotFactoryId,
        d => d.RobotFactoryId,
        (f, d) => new { f.Location, d.Guns })
    .GroupBy(f => f.Location)
    .Select(x => new { Location = x.Key, Guns = x.Select(g => g.Guns).Sum() });

MAX

As a last example, we could find the max number of guns owned by a robot in each location:

SELECT f.Location, MAX(d.Guns) AS Guns
FROM RobotFactories f
JOIN RobotDog d
ON f.RobotFactoryId= d.RobotFactoryId
GROUP BY (b.Location)
var robotDogs = from d in context.RobotDogs
    join f in context.RobotFactories
    on d.RobotFactoryId equals f.RobotFactoryId
    select new { f.Location, d.RobotDogId } into x
    group x by new { x.Location } into g
    select new
    {
        Location = g.Key.Location,
        Guns = g.Select(x => x.RobotDogId).Max()
    };