Pranay Rana: October 2011

Monday, October 24, 2011

DataLoadOptions and How to use in Compiled Linq query

DataLoadOption in LINQ allows immediate loading and filtering of related data. The DataLoadOption allow to load related object so this remove need of the firing subquery every time you ask for the related object(s).

Consider below case

If you do code like this
var distlist = (from d in edb.Distributors select d).ToList();
            foreach(Distributor d in distlist)
            {
              var clientlist = d.Customers;
              foreach( Customer c in clientlist)
              {
                   //do the code 
              }
            }
each time inner for loop fire query on database to get the customer related to distributor which in turn decrease the performance. But if you know in advance that you are need to use the related list when you are loading main list i.e you need to load data of related entity eagerly make use of DataLoadOptions.

Modified code is something like
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Distributorgt;(d => d.Customers);
dataContext.LoadOptions = dlo;
Note
  • Be careful when you use DataLoadOption because it may decrease the performance if you are not going to use related objects. Use only in situation when you want to load related object early and going to consume it all.
  • You an only attach DataLoadOption once with the instance of datacontext.

The above DataLoadOption runs perfectly when you use regular Linq Queries. But it does not work with compiled queries. When you run this code and the query hits the second time, it produces an exception:

DataLoadOptions in Complied queries
First to get more info about Complied look this post : Increase Linq query performance by Compling it
Now when you attache DataLoadOption to complied query as we did above it give you an exception at run-time
Compiled queries across DataContexts with different LoadOptions not supported

To avoid the exception you need to create the static DataLoadOption variable because as the compiled linq queries are the static one it not consume the DataLoadOption which is not static.

So for that I have created below code where GetDataLoadOpt() static function returns DataLoadOptions object and I store it into static variable dlo and than attach this dlo1 with the compiled version of query.

public static DataLoadOptions dlo1 = GetDataLoadOpt();

    public static Func<DataLoadTestDataContext, string, IQueryable<Product>>
        ProductByCategory =
        CompiledQuery.Compile((DataLoadTestDataContext db, string category) =>
        from p in db.Products where p.Category == category select p);

    public static DataLoadOptions GetDataLoadOpt()
    {
        DataLoadOptions dlo = new DataLoadOptions();
        dlo.LoadWith<Product>(p => p.ProductWithCategory);
        return dlo;
    }

    public static void testfunction()
    {
        DataLoadTestDataContext context = new DataLoadTestDataContext();
        context.LoadOptions = dlo1;
        var productlist = ProductByCategory(context, "mobile");

        foreach (Product p in productlist)
        {
            Console.WriteLine(p.ProductWithCategory);
        }
    }
If you want to get the above exception try code removing static from the function testfunction and variable dlo1 than assign it to compiled version of query you will get the run-time exception.

Monday, October 17, 2011

Increase Linq query performance by Compling it

Each time when we fire any Linq to SQL or to any other data-source using Entity Framework query run-time convert query to expression tree and than into t-SQL statement. So if the query get fired number of time in application it get converted in the expression tree to t-SQL statement by run-time this conversion increase execution time which in turn affect performance of the application. To avoid this cost Microsoft .net framework introduce concept of the complied queries which allows compilation and caching of queries for reuse.

Now, there is shopping website which list of the product by the category basically it allows filtering of product by the category. So if I have 100 no of user who logged in to system and do the filter the product by category they basically fire
from p in db.Products where p.Category == category select p
query to get the result they want. So this will increase the execution cost as this query get fire no of time and get converted in the expression tree and in turn get the result.

With the help of CompiledQuery class of .net framework I can rewrite my code and it's like as below
public static Func<DataLoadTestDataContext, string, IQueryable<Product>>
  ProductByCategory =
    CompiledQuery.Compile((DataLoadTestDataContext db, string category) =>
     from p in db.Products where p.Category == category select p);
Static Variable
Static variable is used to store the so its not thread safe and global to all. Because of static variable compilation will only occur once per AppDomain and will remain cached through the rest of the application's lifetime. If you don't use the static variable query get complied each time which increase the cost and decrease the performance of the application.

Constrain and Use
Cannot use to store the queries which returns Anonymous type, because the anonymous type doesn't have any type to assign generic argument of function.

Useful when query is used heavily and you want to reuse the query, by using this way increase the performance of the application.

Where to include the code ?
Better place to include above code is partial class, its extended partial class to the partial class generated by ORM tool.More:Extenend ORM generated class

Tuesday, October 11, 2011

Extenend ORM generated class

In this post I am going to show how you can extend the class generated by the ORM tools. To demonstrate I am using Linq To Sql ORM.

When you make use of ORM tool like Linq to Sql or Entity-Framework it generate the classes from the database structure given as input. For example consider below example
Now I want to display the list of product in my grid but I have to display product name with categoryname for example productname(categoryname) or consider situation where I have ordertable and I have to display one more extra column in grid with display total = quantity * price.

To achieve this make look to the class generated by ORM tools, when you see the class definition you see its decorated with the partial keyword. C#2.0 partial class allow us to create class which expands in two different file and at the time of compile both file get compile in one class. So by making use of same rule I have added one more class file and its partial as below.
public partial class Product
{
   public string ProductWithCategory
   {
      get
      {
         return this.ProductName + "(" + this.Category +")";
      }
   }
}
Now to we can consume property in the presentation or businesslayer like as below.
var productlist = (from p in context.Products select p).ToList();
foreach (Product p in productlist)
{
   Console.WriteLine(p.ProductWithCategory);
}
So by above way adding property to partial class we can easily achieve the task.

Summary
By using partial class we can add the custom logic to the class created by the ORM tool(s).

Monday, October 3, 2011

Log your LINQ query


Most of the beginner developer who are using LINQ to SQL as there back-end to talk with the database (i.e to perform the database CRUD operation), don't have idea what the query get fire to database out of LINQ query.

Lastly I asked to log the query that fire to database out of my LINQ query. So as solution I found one make use of SQL Server Profiler to check fire query. But with the profiler I can not able to log the queries.

I fond one solution is to make use of Log property of DataContext object. Log property allow me to log the queries in the file. Consider the below code

//created temp file 
using 
(System.IO.StreamWriter sw = new System.IO.StreamWriter(@"e:\tempdatacontext.log"))
{
    EmployeeDataContext edb = new EmployeeDataContext();
    //assigned streamwriter to the log property of datacontext
    edb.Log = sw;
    var cust = from c in edb.Customers
              join d in edb.Distributors on
                new { CityID = c.CityId, StateID = c.StateId, 
                      CountryID = c.CountryId, Id = c.DistributorId }
                equals
                new { CityID = d.CityId, StateID = d.StateId, 
                      CountryID = d.CountryId, Id = d.DistributorId }
              select c;

    List<customer> custList = cust.ToList();
}

So once the code get executed it's time to check the temp file. As I opened up the file I found following query get fired on my database.


It's fun to find the query get fire to database and you get to know if there is any problem in the LINQ query you wrote.

Saturday, October 1, 2011

Linq Join on Mutiple columns using Anonymous type

I was working on the project using LINQ. I got the requirement to join the two entity on multiple column.

For example consider the following image. There are two entity Distributor and Customer related to each other.

Now I want to find out all customer who lives in same city where the distributor living.
So to find out that I have to make join between Customer and Distributor. And to achieve this I need to join by using multiple columns City,State,Country,ID. (Note: I am using id in join because later on I want to get which distributor near to customer).

Now with the LINQ you can join two entity on multiple columns by creating one anonymous type.
EmployeeDataContext edb= new EmployeeDataContext();
var cust = from c in edb.Customers
           join d in edb.Distributors on
             new { CityID = c.CityId, StateID = c.StateId, CountryID = c.CountryId, 
                   Id = c.DistributorId }    
           equals
             new { CityID = d.CityId, StateID = d.StateId, CountryID = d.CountryId, 
                   Id = d.DistributorId }    
           select c;
Note : As anonymous types used to join entity on multiple column, so for that make sure that both are equal and they must have the same properties in same order. Otherwise it don't get complied and you get error.

Once you are done run the code and you see the following query in your sql profiler or you can also use the visual studio feature to get the query.
SELECT [t0].[Id], [t0].[Name], [t0].[EmailId], [t0].[CityId], [t0].[StateId], 
[t0].[CountryId], [t0].[PinCode], [t0].[DistributorId]
FROM [dbo].[Customer] AS [t0]
INNER JOIN 
[dbo].[Distributor] AS [t1] ON 
([t0].[CityId] = [t1].[CityId]) 
    AND ([t0].[StateId] = [t1].[StateId]) 
    AND  ([t0].[CountryId] = [t1].[CountryId]) 
    AND ([t0].[DistributorId] =[t1].[DistributorId])