Telerik blogs

LINQ Tip of the week: A quick peek behind the curtains

July 30, 2009 Productivity 0 Comments


In our previous post in this series we had a look at how to optimize query execution with the help of parameterized queries. Today we will briefly look at what actually happens ‘behind the curtains’ when a LINQ query is executed and you get back  the results.

 

As you already know LINQ can be used to query various data sources like in-memory objects, XML or databases,which is of particular interest to us. Consider the following example where we query for all employees who are born in the same month as today i.e.  in the month of July.

 

var employees = from e in scope.Extent<Employee>()
                          where e.BirthDate.Month == DateTime.Now.Month
                          select e;
 
As you can see LINQ makes it very easy to express the desired query filter, but what is interesting is how this query is translated by the Telerik OpenAccess ORM to an appropriate backend specific SQL statement.  The above query when used against MS SqlServer gets translated to the following select statement:
 
SELECT [EmployeeID] , [Address] ,[BirthDate] , [City] , [Country] , [ReportsTo] , [Extension] , [FirstName] ,[HireDate] , [HomePhone] , [LastName] , [Notes] , [PhotoPath] , [PostalCode] , [Region] ,[Title] , [TitleOfCourtesy] FROM [Employees] WHERE MONTH([BirthDate]) = ?
 
Two things are worth noticing over here -
  • An automatically parameterized query is generated which gets cached and is reused with the current date, whenever the LINQ query is executed.
  • The BirthDate.Month property gets translated to the backend specific function call – MONTH(date), which returns the month part of the specified date, in our case the BirthDate.

 

Telerik OpenAccess ORM performs several such translations in order to improve the performance of the LINQ queries. Consider the following example where we query for all employees whose country name ends with ‘ia’.

 

var employees = from e in scope.Extent<Employee>()
                                where e.Country.EndsWith("ia")
                                select e;

 

It gets translated to the following select statement

SELECT [EmployeeID] , [Address] , [BirthDate] , [City] , [Country] , [ReportsTo] , [Extension] ,[FirstName] , [HireDate] , [HomePhone] , [LastName] , [Notes] , [PhotoPath] , [PostalCode] ,[Region] , [Title] , [TitleOfCourtesy] FROM [Employees] WHERE [Country] LIKE '%ia'

 

Notice how the String.EndsWith call is translated to a SQL ‘LIKE’ pattern. Various String methods like Contains, EndsWith, Equals are translated to SQL specific LIKE patterns.

It is also worth mentioning that all these SQL translations are backend specific. So a call to DateTime.Month would translate to MONTH (date) on MS SqlServer and for Firebird it would generate a call to EXTRACT (MONTH FROM datetime).

 

Keep an eye out for more such interesting LINQ tips and tricks, wishing you happy programming with Telerik OpenAccess ORM.

Telerik Data Access

Comments

Comments are disabled in preview mode.