KaushaL.NET

Part 2: LINQ Tips & Tricks [Things to get you started efficiently]

This blog entry is in continuation with my first post on Part 1: LINQ Tips & Tricks [Things to get you started efficiently]

Continuing to share some other important tips that you can take care of while developing with LINQ.

[9] Loading Options while working with tables in Relationships

You always need to struggle for performance as a LINQ Developer; perhaps because to understand better how internal mechanism works.

using (BEDataContext context = new BEDataContext())
{
    var customers = from cust in context.Customers
                    select cust;
    foreach (var customer in customer)
        Response.Write(customer.CustomerAddresses.Count().ToString());
}

In above example, you will see in Sql Server Profiler that for each customer one query will be executed additional each times to get the total addresses count for that customer when you enumerates in foreach loop. Now, consider below re-written snippet:

using (BEDataContext context = new BEDataContext())
{
    var customers = from cust in context.Customers
                    select cust;
    DataLoadOptions dataLoadOptions = new DataLoadOptions();
    dataLoadOptions.LoadWith<Customers>(A => A.CustomerAddresses);
    context.LoadOptions = dataLoadOptions;
    foreach (var customer in customer)
        Response.Write(customer.CustomerAddresses.Count().ToString());
}

Simply in above example, with DataLoadingOptions we are infect just telling LINQ Query engine that while it fetches the Customer data; also fetch associated CustomerAddresses table record count for that particular Customer. The benefit is; only ONE query will be executed to get the final result.

[10] Overriding Database Modification Statements

If you have been thinking that using LINQ to SQL in your environment is not possible, perhaps because of requirements to use stored procedures for all modifications to the database, then you would be interested in knowing that the actual code that gets called to make the updates, including inserts and deletes, can be overridden.

Overriding the code called to insert, update, and delete is as simple as defining the appropriately named partial method with the appropriate signature. When you override this way, the DataContext change processor will call your partial method implementation for the database update, insert, or delete.

Example:

Think of having Northwind database dbml context class, and then partial class can be created as:

public partial class BEDataContext : System.Data.Linq.DataContext
{
    partial void InsertPerson(Person instance)
    {
        Console.WriteLine("Insert override method called for Person {0}.", instance.FirstName);
    }
    partial void UpdatePerson(Person instance)
    {
        Console.WriteLine("Update override method was called for Person {0}.", instance.FirstName);
    }
    partial void DeletePerson(Person instance)
    {
        Console.WriteLine("Delete override method was called for Person {0}.", instance.FirstName);
    }
}

Notice that the signature of the partial methods are [ACTION][EntityName] where ACTION is INSERT | UPDATE | DELETE and EntityName is “Person”. Once you call SubmitChanges() method then these partial methods will be called. IF you want default behavior to occur then you can simply call this.ExecuteDynamicInsert(Person instance), ExecuteDynamicUpdate(Person instance) and ExecuteDynamicDelete(Person instance) respectively for insert, update and delete. Please note that here it is developer’s responsibility to handle concurrency conflicts.

[11] Proper usage of Translate<T> method

There are some limitations I faced with LINQ to SQL. Probably it’s possible that I am unaware of the proper method but you will find over the net that it’s a common problem LINQ developer faced mostly with Dynamic Queries which are executed with “exec” statement in Stored Procedures. So, the problem is regarding- Stored Procedure having Dynamic “Select” Queries. When you try to drop a Stored Procedure (which contains Dynamic Sql Query or Queries or having Temporary tables) from Solution Explorer to dbml file it’s quite possible that instead of generating correct return type for the method it generates “int” as the return type of that method.

What should be done in this case then? There are alternatives. One of which is using Translate<T> method. Translate<T> is similar to ExetureQuery method. The method actually translates the query results in to sequence of objects. The difference is- we pass Sql statement string as parameter in ExecuteQuery method while in Translate<T> we pass System.Data.Common.DbDataReader like SqlDataReader.

So, the Question is how this method would help to generate correct sequence of object while the Stored Procedure contains Dynamic Select Query? The idea is you can simply create and execute SqlCommand for that Stored Procedure and initialize SqlDataReader with ExecuteReader() method which you can further pass to Translate<T> method to have correct result.

Example:

using(SqlConnection sqlCn = new SqlConnection(ConfigurationManager.ConnectionStrings["NWind"].ConnectionString))
{
    sqlCn.Open();
    SqlCommand sqlCmd = new SqlCommand("usp_DynamicSelectQuerySP", sqlCn);
    sqlCmd.CommandType = CommandType.StoredProcedure;
    SqlDataReader sqlReader = sqlCmd.ExecuteReader();
    using (NWindDataContext context = new NWindDataContext())
        IEnumerable<Customer> custs = context.Translate<Customer>(sqlReader);
    foreach (Customer c in custs)
        Console.WriteLine("ID = {0} : Name = {1} : Contact = {2}", c.CustomerID, c.CompanyName, c.ContactName);
    sqlCn.Close();
}

Another situational usage of Translate<T> method is for integrating LINQ to SQL code into existing ADO.NET code.

[12] Stored Procedures return type with Dynamic Sql or Temporary tables

This is regarding the possible problem/situation I already talked about in previous point. Working with the Stored Procedure along with; LINQ to SQL dbml need a way for figuring out what will be the return type of the method created by that stored procedure. To do this without actually running the actual stored procedure; it uses the SET FMTONLY command set to ON so that SQL Server can just parse the stored procedure for correct return type. But, this parsing does not extend or generate proper return types if there are any Dynamic Sql or temporary tables being used. So you must change the return type from the scalar integer to known type you want it to be. What happening is, when the stored procedure is dragged and dropped on the DBML designer, Visual Studio is generating wrong signature for the stored procedure return typ. That is, instead of returning a result set as output, an integer value is set as return type.

So, finally here is the alternative when correct return type is not being generated in LINQ to SQL dbml designer file.

Hack/possible solution is:

• Delete the stored procedure method from dbml file as well as drop it from database. Create a dummy stored procedure with the similar signatures, parameters (if any), output fields (if any) and select fields that Dynamic Sql consists of. This can be a simple select statement without any panic or conditions; a simplified version of the same SP.
• Once you create it; drag & drop it on dbml and you notice that dbml designer now have correct return type for the method just because now the dummy SP doesn’t have dynamic Sql. That’s it.
• Now you can remove/alter the dummy SP with the actual one that contains Dynamic Sql.

[13] Setting DataContext.ObjectTrackingEnabled Property

ObjectTrackingEnabled Property tells the LINQ to SQL to track or not to track the original value and object identity for the DataContext. We can understand that tracking the original values would be only requiring when you are inseting, updating or deleting the records (DML operations) and certainly not at the time of retrieval or simple at the time of selecting the records. That means, we do not need to bear the heavy cost of object tracking service. So, if we set it off/false then it improves performance at retrieval time, because there are fewer items to track.

Example:

using (NWindDataContext context = new NWindDataContext())
{
    context.ObjectTrackingEnabled = false;
}

Here is one exception point to be noted is that: Deferred LINQ Queries require object tracking to be enabled true.

[14] Try to separate DataContext dbml class

This might not be possible in almost cases but you can think of separating database objects as a single unit of work and create separate DataContext classes. Since we consider DataContext dbml class as a whole database and not as a single unit of work/module we used to put all the tables, methods in one DataContext class only. Separating DataContext based on workspace would minimize in-memory cost of those database objects as well as object tracking cost would be minimized.

[15] UpdateCheck Property usage (Concurrency Check)

LINQ to SQL supports Concurrency Check with sql timestamp column. The property name is “UpdateCheck” for each field of the database object. You can turn this off, if it’s not required to improve the performance. To access/change it, open the dbml file select the table object’s fields for which you don’t want to have UpdateCheck and press F4 to open the property dialog box. You will find last property option “UpdateCheck” with possible options: “Never”, “Always” and “When Changed” with default “Always” option.

[16] Only fetch records that are required

Take a simple example of binding a gridview. You are paginating the records as usual to any application then consider using powerful LINQ to SQL standard operators/methods like Take & Skip.

Example:

public List<Employee> GetEmployee(int PageIndex, int PageSize)
{
  using (NwindDataContext context = new NwindDataContext ())
  {
    return context. Employee
           .Take< Employee >(PageSize)
           .Skip< Employee >(PageIndex * PageSize)
           .ToList< Employee >();
   }
}

hope the tips would help./.

Reference: Apress: Pro LINQ: Language Integrated Query in C# 2010

more to come on EF & LINQ.