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>