Constructing Expression Trees

Constructing Expression Trees in runtime encapsulates the logic of the tree into the Expression

In this post we have an example of Construct and Compile Lambda Expression, with a simple logic, the function receives a name and a number of request days, and for each day adds it to the result

            var test = ConstructRuntimeFunction();

            var i = test("test", 4);

            //
            //"test, 0 added, 1 added, 2 added, 3 added, 4 added"

The expression before compile is the next tree:

The implementation of ConstructRuntimeFunction is the next:

        static Func<string, int, string> ConstructRuntimeFunction()
        {
            var prmA = Expression.Parameter(typeof(string), "name");

            var prmB = Expression.Parameter(typeof(int), "request_days");

            var loopDays = Expression.Variable(typeof(int), "loop_days");

            var dayDate = Expression.Variable(typeof(DateTime), "day_date");

            var result = Expression.Variable(typeof(string), "result");

            var loopEnd = Expression.Label();

            MethodInfo method_add_day = Assembly.GetExecutingAssembly().GetType("Test").GetMethod("increaseDay");

            MethodInfo method_write = Assembly.GetExecutingAssembly().GetType("Test").GetMethod("addVisitDay");

            var func = Expression.Lambda<Func<string, int, string>>(

                Expression.Block(new[] { result }, Expression.Assign(result, prmA),

                Expression.Block(new[] { loopDays }, Expression.Assign(loopDays, Expression.Constant(0)),

                Expression.Block(new[] { dayDate }, Expression.Assign(dayDate, Expression.Constant(DateTime.Now))),

                Expression.Loop(
                    Expression.Block(
                        Expression.IfThen(
                            Expression.Not(
                                Expression.LessThanOrEqual(loopDays, prmB)),
                                Expression.Break(loopEnd)),

                    Expression.Block(new[] { dayDate }, Expression.Assign(dayDate, Expression.Call(method_add_day, loopDays))),

                    Expression.Assign(result, Expression.Call(method_write, result, loopDays)),

                    Expression.PostIncrementAssign(loopDays)),

                loopEnd)),

            result), prmA, prmB);
            
            return func.Compile();
        }

LINQ Expression Trees

An expression tree is an efficient data representation of a query operator lambda expression. This data representation is evaluated all simultaneously, so that an individual query can be built and launched on a data source in once

Consider the next query with two operators expecting delegates as arguments:

        private static decimal[] celsiusDegrees = new decimal[] 
            {0.0M, 6.0M, 14.0M, 25.0M, 28.0M, 34.0M, 36.0M};

        private static IEnumerable<decimal> enumerableTemps = celsiusDegrees
            .Where(i => i > 30.0M)
            .OrderBy(i => i).ToArray();

        private static IQueryable<decimal> queryableTemps = celsiusDegrees.Where(c => c > 30.0M)
            .OrderBy(c => c).AsQueryable();

When the first code is compiled .NET IL emits code with two anonymous methods, one for each of the query lambda expressions

This query is evaluated and launched linearly, first Where operator, then OrderBy operator. This linear evaluation has performance for this example, but consider a query on a large dataset. These scena is when Expression Trees become necessary. Expression trees will be generated if the operator is declared to accept an expression of a delegate

These are the two different implementations of the Where operator:

Standard query operator in LINQ to Objects API, in System.Linq.Enumerable class

        public static IEnumerable<T> Where<T>(
                this IEnumerable<T> source,
                Func<T, bool> predicate);

Implementation in the LINQ to SQL API, in the System.Linq.Queryable class

        public static IQueryable<T> Where<T>(
                this IQueryable<T> source,
                System.Linq.Expressions.Expression<Func<int, bool>> predicate);

The result of this code is the next

        Console.WriteLine(enumerableTemps);

        foreach (var temp in enumerableTemps)
            Console.WriteLine(temp);

        Console.WriteLine(Environment.NewLine);

        Console.WriteLine(queryableTemps);
        foreach (var temp in queryableTemps)
            Console.WriteLine(temp);

The ease way to create expression trees is by using advanced LINQ features, however, a tree made up only of constans would be converted automatically at compile time, the aim is build parametric expressions, such as the formula to convert celsius to kelvin degrees : K = C + 273.15

        Func<decimal, decimal> CelsiusToKelvin = (c) => c + 273.15M;

        Expression<Func<decimal, decimal>> CelsiusToKelvinExp = (c) => c + 273.15M;

The first delegate instance is no different from a regular function, you can get an expression tree representation instead of the delegate simply declaring CelsiusToKelvin as an Expression<TDelegate>

The result of this code is the next

            Console.WriteLine(CelsiusToKelvin.ToString());
            Console.WriteLine(CelsiusToKelvin(34.2M));

            Console.WriteLine(CelsiusToKelvinExp.ToString());
            Console.WriteLine(CelsiusToKelvinExp.Compile()(34.2M));

Generics : Ordered Collections

Ordered Collections are correspondants collections of IEnumerable and IQueryable types

As we apply order by clauses the collections become IOrdered_

In this post we have a simple demo, intended to create a third ordered collection using the implementation of the method CreateOrderedEnumerable

Now we are creating a IOrderedQueryable collection from the first one, in its implementation of enumerable is the function of the queryable expression

LINQ Compiled Queries

The process of converting LINQ queries to SQL statements, involves syntax check, and the construction of the SQL query, this task is performed every time we launch the LINQ query

In compiled queries, the syntax check and construction plan are cached in a static class, so LINQ uses this cached plan from the static class object instead of re-building it in sequent executions

In this example we have the next diagram of LINQ to SQL Classes

We will compile these two queries. A compiled query is stored in a Func delegate, where the first argument must be an instance of DataContext (or derived), and the last argument must be the type returned from the query, you can define up to three arguments in the middle as parameters of the compiled query. You will need to specify these arguments for each compiled query invocation

 
        private static Func<BusinessClassesDataContext, DateTime, IQueryable<B_SALES>>
            SalesByDate = CompiledQuery.Compile(
                (BusinessClassesDataContext ctx, DateTime saleDate) =>
                    (from sl in ctx.GetTable<B_SALES>()
                     where sl.SALEDATE >= saleDate
                     select sl));

        private static Func<BusinessClassesDataContext, int, Decimal>
            SalesAverageByCustomer = CompiledQuery.Compile(
                (BusinessClassesDataContext ctx, int idCustomer) =>
                    (from sl in ctx.B_SALES
                     where sl.B_CUSTOMERS.IdCustomer == idCustomer
                     group sl by sl.Price
                     into sales
                     select sales.Average(sl => sl.Price)).First());
                         

Now the code calling these queries is the next

 
        using (BusinessClassesDataContext context = new BusinessClassesDataContext())
        {
            foreach (B_SALES sale in SalesByDate(context, saleDate))
            {
                Console.WriteLine("{0} : {1}", sale.IdSale, sale.IdProduct);
            }

            Console.WriteLine("{0:N2}$", SalesAverageByCustomer(context, 1));
        }            
    

<METHOD SOFTWARE © 2014>

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>

Profiling LINQ Queries

When we are implementing LINQ queries to retrieve data from our database server, it's a good practice profile the queries through SQL Profiler tool, it might be decisive for improving the query and reducing the time responses

In this example we have our DataContext pointing to a database with a table named B_PRODUCTS, filled with 30000 records for this example

In order to measure the time response of this query into the server side, we will begin a new instance of SQL Profiler tool, and begin a new trace connected to our server

We will make use of the template T-SQL duration, for targeting the duration of the query in milliseconds when is processed by the server. In the events tab of the trace, we can filter the trace log only to those events we want to register

At this point, we run the trace, and launch the query

This is the initial linq query

     
	using (BusinessClassesDataContext context = new BusinessClassesDataContext())
    {
        Products = (from prods in context.B_PRODUCTs
                    orderby prods.Description ascending
                    select prods).ToList<B_PRODUCT>();
    }
    

Now we will include a where clause with 2 conditions and check the trace log

     
		Products = (from prods in context.B_PRODUCTs
                where prods.Description.EndsWith("kit")
                    && prods.IdProduct > 100
                orderby prods.Description ascending
                select prods).ToList<B_PRODUCT>();
                

Now we will change the implementation of the first condition making use of the SqlMethods namespace, the which one includes SQL expressions for querying data

     
		Products = (from prods in context.B_PRODUCTs
                where SqlMethods.Like(prods.Description, "%kit")
                    && prods.IdProduct > 100
                orderby prods.Description ascending
                select prods).ToList<B_PRODUCT>();

With this second approach, we can appreciate a reduction of the time response of 40 milliseconds, with this information we can leverage the most efficient way to query our data


<METHOD SOFTWARE © 2013>

LINQ Join Operations

In this post we make a review of linq join operators, to perform different kind of join on two sets of related data

All data in this example is from Northwind database, distributed as example database by Microsoft, you can download it here: Northwind database for SQL Server

First, we add a set of LINQ to SQL Classes, with the next result

And we add a method to retrieve our sets of data

     
        using (NorthwindDataContext nwContext = new NorthwindDataContext())
        {
            customerList = (from cust in nwContext.Customers
                            select cust).ToList<Customer>();

            orderList = (from ord in nwContext.Orders
                            select ord).ToList<Order>();

            productList = (from prod in nwContext.Products
                            select prod).ToList<Product>();

            supplierList = (from supp in nwContext.Suppliers
                            select supp).ToList<Supplier>();

            categoryList = (from cat in nwContext.Categories
                            select cat).ToList<Category>();
        }

The first join operation we perform is a simple inner join of equivalence that produces a result ser of Customers that has a matching element in Orders

     
        var customer_orders = from cust in customerList
                              join ord in orderList 
                              on cust.CustomerID equals ord.CustomerID
                              orderby cust.ContactName ascending
                              select new { cust.ContactName, ord.OrderID };

        foreach (var cust_ord in customer_orders)
            Console.WriteLine("Customer = {0}, Order Id = {1}", 
                cust_ord.ContactName, cust_ord.OrderID);
                

The result of this query is the next

The second query is an inner join that produces a sequence of objects, each of which has a key and an inner sequence of all matching elements, we get this result set through the keyword into

     
        var customer_orders = from cust in customerList
                              join ord in orderList on cust.CustomerID equals ord.CustomerID
                              into cs
                              orderby cust.ContactName ascending
                              select new { Key = cust.CustomerID, Items = cs };

        foreach (var cust in customer_orders)
        {
            Console.WriteLine("Customer ID = {0} :", cust.Key);
            foreach (var order in cust.Items)
                Console.WriteLine(" - Order: {0}, {1}", order.OrderDate, order.OrderID);
        }
        

You can also select elements from the set produced by the into clause

     
        var prodByCategory = from cat in categoryList
                             join prod in productList 
                             on cat.CategoryID equals prod.CategoryID 
                             into ps
                             from p in ps
                             select new { CategoryName = cat.CategoryName, p.ProductName };

        foreach (var item in prodByCategory)
            Console.WriteLine("Category = {0}. Product Name = {1}", 
                item.CategoryName, item.ProductName);
                

With the next query, we perform a left outer join to produce a result set that includes all the left hand side elements at least once, even if they don't match any right side element

DefaultIfEmpty preserves left-hand elements that have no matches on the right side

     
        var productSupps = from prod in productList
                           join sup in supplierList on prod.SupplierID equals sup.SupplierID 
                           into ps
                           from p in ps.DefaultIfEmpty()
                           orderby prod.ProductName
                           select new
                           {
                               PName = prod.ProductName,
                               SName = p == null ? "--- No suppliers ---" : p.CompanyName
                           };

        foreach (var product in productSupps)
            Console.WriteLine("Product mame = {0} . Supplier name = {1}", 
                product.PName, product.SName);
                

<METHOD SOFTWARE © 2012>

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>