Try .NET /elinq/
Powered by Try .NET

SQL Server GROUP BY

In practice, the GROUP BY clause is often used with aggregate functions. Our basic entities do not have properties for them. A dedicated type is needed.

The simplest way is to declare a class that references the related entities and adds missing properties:

public class CustomerYearOrders
{
    public Customers Customer { get; set; }
    public int Year { get; set; }
    public int OrdersPlaced { get; set; }
}

In EF such types are called Keyless Entity Types and they must be mapped as keyless:

modelBuilder.Entity<CustomerYearOrders>(entity => entity.HasNoKey());

Then we can introduce a dedicated DbSet<> property in the context or use ad-hoc context.Set<>() method.

Basic GROUP BY:

var customerIds = new int?[] { 1, 2 };

var query = DbContext.Set<CustomerYear>()
    .Query((Orders order, CustomerYear alias) =>
    {
        var year = YEAR(order.OrderDate);
        var customerId = order.CustomerId;

        var result = SELECT<CustomerYear>(customerId.@as(alias.Customer.CustomerId), year.@as(alias.Year));
        FROM(order);
        WHERE(customerIds.Contains(customerId));
        GROUP(BY(customerId), BY(year));

        return result;
    })
    .OrderBy(c => c.Customer)
    .Include(c => c.Customer);

foreach (var customer in query.Take(3))
    Console.WriteLine((customer.Customer.CustomerId, customer.Year));

With aggregate:

var customerIds = new[] { 1, 2 };

var query = DbContext.Set<CustomerYearOrders>()
    .Query((Orders order, CustomerYearOrders alias) =>
    {
        var year = YEAR(order.OrderDate);
        var customerId = order.CustomerId;
        var count = COUNT(order.OrderId);

        var result = SELECT<CustomerYearOrders>(customerId.@as(alias.Customer.CustomerId),
            year.@as(alias.Year),
            count.@as(alias.OrdersPlaced));
        FROM(order);
        WHERE(IN(customerId, customerIds));
        GROUP(BY(customerId), BY(year));

        return result;
    })
    .OrderBy(c => c.Customer)
    .Include(c => c.Customer);

foreach (var customer in query.Take(3))
    Console.WriteLine((customer.Customer.CustomerId, customer.Year));

The next few examples in the tutorial do permutations with already covered features. Except the last which has a cool SUM expression:

var query = DbContext.Set<OrderNetSale>()
                .Query((OrderItems orderItems, OrderNetSale alias) =>
                {
                    var sum = SUM(orderItems.Quantity * orderItems.ListPrice * (1 - orderItems.Discount));

                    var result = SELECT<OrderNetSale>(orderItems.OrderId.@as(alias.Order.OrderId), sum.@as(alias.NetSale));
                    FROM(orderItems);
                    GROUP(BY(orderItems.OrderId));

                    return result;
                })
                .Include(c => c.Order);

foreach (var orderNetValue in query.Take(3))
    Console.WriteLine((orderNetValue.Order.OrderId, orderNetValue.NetSale));

< BACK | HOME