Guidelines and Best Practices in Optimizing LINQ Performance - 03 Dec 2009

Language Integrated Query (LINQ) is a query execution pipeline for use in the managed environment of .NET Framework. In essence, LINQ is Microsoft's object relational mapper between your business objects and the underlying data sources and provides a simplified framework for accessing relational data in an object-oriented fashion.

Although LINQ is great in the sense that you can query data in your object model seamlessly, there are certain factors that you need to consider to ensure that your application performs to the extent you need it to. This article takes a look at some of the best practices that you can follow for enhancing the performance of LINQ in your applications.

The Best Practices

Here are some of the best practices that you can follow to boost your LINQ query performance.

Object tracking. Turn ObjectTrackingEnabled Property off if not required. If you need only to read data through the data context and don't need to edit the data, you should turn off ObjectTrackingEnabled property; doing so will turn off the unnecessary identity management of objects and help boost the application's performance.

Here is an example:

    using (TestDataContext dataContext = new TestDataContext())     {     dataContext.ObjectTrackingEnabled = false;     }

Data Contexts.If there are multiple disconnected databases that you're using in your application, try using multiple data contexts to reduce the identity management and object tracking overhead costs. You should attach only those objects to your data context that have been changed.

Compiled queries. You can use compiled queries to boost your application's performance.But remember that a compiled query could be costly when used for the first time. So, do ensure you use compiled queries only in situations where you need them that is, when you need a query to be used repeatedly.

Optimistic concurrency. Concurrency handling is a mechanism that enables you to detect and resolve conflicts that arise out of concurrent requests to the same resource at any point in time. Concurrency in ADO.NET is of two types: optimistic and pessimistic. LINQ follows an optimistic concurrency model by default. You should avoid using optimistic concurrency if not needed. To turn off the check for optimistic concurrency, you can use UpdateCheck.Never in the attribute level mapping for your entity classes, as shown below:

    [Column(Storage="_FirstName", DbType="NText",     UpdateCheck=UpdateCheck.Never)]     public string FirstName     {     get     {     return this._FirstName;     }     set     {     if ((this._FirstName !=value))     {     this.OnFirstNameChanging(value);     this.SendPropertyChanging();     this._FirstName =value;     this.SendPropertyChanged("FirstName");     this.OnFirstNameChanged();     }     }     } 

Retrieve selective data. You should use Take and Skip methods appropriately when you need to bind paged data to data controls. Here is an example:

    private List GetStudentRecordss(int index, intsize)     {     using (TestDataContextdataContext = new TestDataContext())     {     returndataContext.Students     .Take( size)     .Skip( index * size)     .ToList();     }     }

You should also filter down your required data appropriately using DataLoadOptions.AssociateWith so that only the data that is required is returned. Here is an example that shows how you can use DataLoadOptions.AssociateWith to retrieve selective data in LINQ:

    using (TestDataContext dataContext = new TestDataContext())     {     DataLoadOptionsdataLoadOptions = new DataLoadOptions();     dataLoadOptions.AssociateWith    (emp=>emp.Department.Where(dept => dept.DeptCode == 1));     dataContext.LoadOptions =dataLoadOptions;     } 

Analyze queries. You can also analyze how your LINQ queries have generated the corresponding SQL statements and monitor them in the Visual Studio IDE. To do this, you need to use the Log property of the data context as shown in the code snippet below:

    using (TestDataContext dataContext = new TestDataContext())     {     #if DEBUG     dataContext.Log =Console.Out;     #endif     }