LINQ and Lambda Expressions

LINQ is a powerful tool, which combined with lambda expressions becomes in many cases the best way to querying our data sets

In this example we have a database with data of a business and we have defined the next schema of LINQ to SQL Classes

Now we have fully typed our classes

These are the variables and methods exposed in this example, the approach of this example is fill the data sets with Linq queries, and since that moment make different operations on the obtained lists with lambda expressions, instead of keep querying the data sets through the context

     
    static List<B_CUSTOMER> AllCustomers;
    static List<B_SALE> Sales;
    static List<B_PRODUCT> Products;
    static List<B_PROVIDER> Providers;
    static List<B_PROVIDERS_PRODUCT> ProvidersProducts;
    static List<B_SALE> HighestSales;

    static void Main(string[] args)
    {
        // will query the context
        GetBusinessData();
        // will query the collections
        GetPurchases(); 
        GetCustomersWithoutSales(); 
        GetHighestSales();
        GetProvidersForHighestSales();
        Console.ReadKey();
    }
    

The first method we implement is GetBusinessData, which fills our typed lists

     
    using (BusinessClassesDataContext context = new BusinessClassesDataContext())
    {
        AllCustomers = (from cust in context.B_CUSTOMERs
                        orderby cust.FirstName ascending, cust.LastName ascending
                        select cust).ToList<B_CUSTOMER>();

        Sales = (from sale in context.B_SALEs
                    orderby sale.Cuantity descending
                    select sale).ToList<B_SALE>();

        Products = (from prod in context.B_PRODUCTs
                    orderby prod.Description ascending
                    select prod).ToList<B_PRODUCT>();

        Providers = (from prov in context.B_PROVIDERs
                        orderby prov.Name
                        select prov).ToList<B_PROVIDER>();

        ProvidersProducts = (from provprod in context.B_PROVIDERS_PRODUCTs
                                select provprod).ToList<B_PROVIDERS_PRODUCT>();

    }

This is the implementation of the GetPurchases method:
We select from the set AllCustomers, those who are in the Sales set, and after expose them in hierarchy with their purchases

     
    Console.WriteLine("\nPurchases:");

    foreach (B_CUSTOMER customer in Sales.Select(sd => sd.B_CUSTOMER)
        .Distinct())
    {
        Console.WriteLine(customer.FullName + " : ");
        foreach (string purchase in Sales
            .Where(s => s.B_CUSTOMER == customer)
            .Select(sd => sd.B_PRODUCT.Description + " : " +
                sd.Cuantity.ToString() + " unit(s) " +
                sd.Price.ToString() + " $"))
            Console.WriteLine(" - " + purchase);
    }
    

This is the method GetCustomersWithoutSales, which performs a fast set operation to discard from AllCustomers those who don't have made purchases

     
    List<B_CUSTOMER> customersWithoutSales = AllCustomers
        .Except(Sales.Select(sd => sd.B_CUSTOMER).Distinct())
        .ToList<B_CUSTOMER>();

    Console.WriteLine("\nCustomers without sales:");
    foreach (B_CUSTOMER customer in customersWithoutSales)
        Console.WriteLine(" - " + customer.FullName);

The implementation of the method GetHighestSales is as follows, notice that the condition might be dynamic

     
    HighestSales = Sales
        .Where(s => s.Price >= 400)
        .OrderBy(s => s.Price).ToList();

    Console.WriteLine("\nHighest Sales:");
    foreach (B_SALE saleData in HighestSales)
        Console.WriteLine(" - " + saleData.B_CUSTOMER.FullName + " : " +
            saleData.B_PRODUCT.Description + " : " + 
            saleData.Cuantity + " unit(s) " +
            saleData.Price.ToString() + " $");

Finally we have defined the method GetProvidersForHighestSales, which gets those providers which provide of the products with highest sales

     
    Console.WriteLine("\nProviders of products with highest sales:");
    foreach (B_PRODUCT product in HighestSales.Select(ht => ht.B_PRODUCT))
    {
        Console.WriteLine("- Providers of " + product.Description);
        foreach (B_PROVIDER provider in ProvidersProducts
            .Where(pr => product.IdProduct == pr.IdProduct)
            .OrderBy(pr => pr.B_PRODUCT.Description)
            .Select(pr => pr.B_PROVIDER).Distinct())
            Console.WriteLine("   - " + provider.Name);
    }
    

This is the output of our application, by combining LINQ and lambdas, we did not have to make a new Linq instruction for each query, taking adventage of the potential of Generics

<METHOD SOFTWARE © 2012>