LINQ Group Operations

In this post we will make a review of the main grouping operations applied with LINQ

The first step will be adding our diagram of LINQ to SQL Classes

For the sake of simplicity, we will program the function LoadContext, which we will call once and will preload data in typed lists

  
    static void LoadContext()
    {
        using (BusinessDataContext businessCtx = new BusinessDataContext())
        {
            customers = (from cst in businessCtx.B_CUSTOMERs
                            select cst).ToList<B_CUSTOMER>();

            products = (from prd in businessCtx.B_PRODUCTs
                        select prd).ToList<B_PRODUCT>();

            providers = (from prv in businessCtx.B_PROVIDERs
                            select prv).ToList<B_PROVIDER>();

            providers_products = (from prv_prd in businessCtx.B_PROVIDERS_PRODUCTs
                                    select prv_prd).ToList<B_PROVIDERS_PRODUCT>();

            sales = (from sls in businessCtx.B_SALEs
                        select sls).ToList<B_SALE>();
        }
    }
        

The next method will make an average of sales grouped by customers, and grouped by products in the second case

  
    static void getAvgSales()
    {
        var avgSalesByCustomer = 
            (from s in sales
                join c in customers
                on s.IdCustomer equals c.IdCustomer
                group s by new { c.FirstName, c.LastName } into cust
                orderby cust.Key.FirstName
                select new
                {
                    Customer = cust.Key.FirstName + " " + cust.Key.LastName,
                    CountSales = cust.Count(),
                    AvgSales = cust.Average(sl => sl.Price)
                });


        var avgSalesByProduct = 
            (from s in sales
                join p in products
                on s.IdProduct equals p.IdProduct
                group s by new { p.Description } into product
                orderby product.Key.Description
                select new
                {
                    Product = product.Key.Description,
                    CountSales = product.Count(),         
                    CountUnits = product.Sum(sl => sl.Cuantity),
                    AvgSales = product.Average(sl => sl.Price)
                });
            
        Console.WriteLine("\n\nAVERAGE BY CUSTOMER\n\n");
        foreach (var avgSale in avgSalesByCustomer)
            Console.WriteLine("{0} : {1} purchases : {2}$", avgSale.Customer, 
                avgSale.CountSales, string.Format("{0:N2}", avgSale.AvgSales));

        Console.WriteLine("\n\nAVERAGE BY PRODUCT\n\n");
        foreach (var avgSale in avgSalesByProduct)
            Console.WriteLine("{0} : {1} sales : {2} units : {3}$", avgSale.Product, 
                avgSale.CountSales, avgSale.CountUnits, string.Format("{0:N2}", 
                avgSale.AvgSales));
    }
        

The output is as follows

The next method will extract sales data grouped by product and the different periods of sale date

  
    static void getSalesByDate()
    {
        var salesByPeriod = 
            (from s in sales
                group s by new 
                { s.B_PRODUCT.Description, s.SaleDate} into grp
                orderby grp.Key.SaleDate, grp.Key.Description
                select new
                {
                    Date = grp.Key.SaleDate.Value.ToString("MM/yyyy"),
                    Product = grp.Key.Description,
                    CountSales = grp.Count(),
                    CountUnits = grp.Sum(sl => sl.Cuantity),
                    AvgSales = grp.Average(sl => sl.Price)
                });

        Console.WriteLine("\n\nAVERAGE BY DATE PERIOD\n\n");
        foreach (string date in salesByPeriod.Select(s => s.Date).Distinct())
        {
            Console.WriteLine(date);
            foreach (var sale in salesByPeriod.Where(s => s.Date == date))
                Console.WriteLine("-- {0} : {1} sales : {2} units : {3}$",
                    sale.Product, sale.CountSales, sale.CountUnits, 
                    string.Format("{0:N2}", sale.AvgSales));
        }
    }
        

Notice that in this occasion, we don't have performed the join to the product set, instead, we have used the access property to the set of products from the set of sales
The result is the next

We can also use lambda syntax in order to release more group operations, such as max or min

  
    static void getSalesByDate()
    {
        var salesByPeriod = 
            (from s in sales
                group s by new 
                { s.B_PRODUCT.Description, s.SaleDate} into grp
                orderby grp.Key.SaleDate, grp.Key.Description
                select new
                {
                    Date = grp.Key.SaleDate.Value.ToString("MM/yyyy"),
                    Product = grp.Key.Description,
                    CountSales = grp.Count(),
                    CountUnits = grp.Sum(sl => sl.Cuantity),
                    AvgSales = grp.Average(sl => sl.Price)
                });


        var maxSalesDate = (from sl in salesByPeriod select sl)
            .Where(sl => sl.AvgSales == salesByPeriod.Max(sld => sld.AvgSales));

        Console.WriteLine(maxSalesDate.First());
    }
        

The result is this

<METHOD SOFTWARE © 2014>