In this article, we will consider issues related to tracing, optimization and compilation of the generated SQL code of two ORM frameworks: LINQ to SQL and Entity Framework.
Foreword
Object-Relational Mapping frameworks are currently very popular tools for rapidly developing data-layer applications. But they have “pitfalls” - not always efficient SQL code. Therefore, in this article we will try to solve this problem.
The above tools will be used because:
- both are bundled with .NET Framework 4
- EF is very similar to LINQ to SQL, which makes it easy to review and compare both frameworks.
We will review the following topics:
- Tracing the generated SQL-code
- Efficient SQL Query Creation
- Compiling LINQ expressions
Before we start, please notice LINQ to SQL requires Visual Studio 2008 and higher, and EF only VS 2010.
Tracing the generated SQL-code
Before we start looking at query optimization, we first need to look at the SQL generated by these tools. Let’s say we have two tables Profiles and Users. Let’s create two models based on LINQ to SQL and EF - DatabaseContext and DatabaseEntities, respectively.
DatabaseEntities Model for Entity Framework
DatabaseContext Model for LINQ to SQL
In EF, this issue is solved using the ObjectTrace class.
using (DatabaseEntities context = new DatabaseEntities())
{
var query = from p in context.Profiles
where p.ProfileID == 100
select p;
Console.WriteLine(((ObjectQuery<Profile>)query).ToTraceString());
}
In LINQ to SQL, we can get the query code through the Log property.
using (DatabaseContext context = new DatabaseContext())
{
context.Log = Console.Out;
var query = from p in context.Profiles
where p.ProfileID == 100
select p;
}
Efficient SQL Query Creation
Sometimes ORMs produce inefficient queries - data is selected from all columns of the corresponding tables, which is not the best way. However, this can be fixed fairly quickly.
To avoid this, we can use the following:
- use anonymous classes
- use proxy classes
- use eSQL in Entity Framework
This is how the request to both models will look like:
//LINQ to SQL
using (DatabaseContext db_context = new DatabaseContext())
{
var query = from profile in db_context.Profiles
where profile.ProfileID == 100
select profile;
}
Generated SQL code::
SELECT [t0].[ProfileID], [t0].[Body]
FROM [dbo].[Profiles] AS [t0]
WHERE [t0].[ProfileID] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [100]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
//EF
using (DatabaseEntities db_entity = new DatabaseEntities())
{
var query = from profile in db_entity.Profiles
where profile.ProfileID == 100
select profile;
}
Generated SQL code::
SELECT
[Extent1].[ProfileID] AS [ProfileID],
[Extent1].[Body] AS [Body]
FROM [dbo].[Profiles] AS [Extent1]
WHERE 100 = [Extent1].[ProfileID]
All columns will be selected from the Profiles table, which is not very necessary for us, given that the Body property can be large. To do this, we will try to use anonymous classes.
//LINQ to SQL
using (DatabaseContext db_entity = new DatabaseContext())
{
var query = from profile in db_entity.Profiles
where profile.ProfileID == 100
select new { ID = profile.ProfileID };
}
//EF
using (DatabaseEntities db_entity = new DatabaseEntities())
{
var query = from profile in db_entity.Profiles
where profile.ProfileID == 100
select new { ID = profile.ProfileID };
}
A proxy class can be used too:
class ProfileProxy
{
public int ProfileID { get; set; }
}
Note that both properties and regular fields can be used to store data. Now we use it:
//LINQ to SQL
using (DatabaseContext db_context = new DatabaseContext())
{
var query = from profile in db_context.Profiles
where profile.ProfileID == 100
select new ProfileProxy() { ProfileID = profile.ProfileID };
}
Generated SQL code::
SELECT [t0].[ProfileID]
FROM [dbo].[Profiles] AS [t0]
WHERE [t0].[ProfileID] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [100]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
//EF
using (DatabaseEntities db_entity = new DatabaseEntities())
{
var query = from profile in db_entity.Profiles
where profile.ProfileID == 100
select new ProfileProxy { ProfileID = profile.ProfileID };
}
Generated SQL code::
SELECT
[Extent1].[ProfileID] AS [ProfileID]
FROM [dbo].[Profiles] AS [Extent1]
WHERE 100 = [Extent1].[ProfileID]
Now let’s move on to Entity SQL or just eSQL. This built-in language in EF is very similar to regular SQL, with the main difference being that classes are used instead of tables in the query. For a more detailed study visit MSDN: http://msdn.microsoft.com/en-us/library/bb387118.aspx Using eSQL
//eSQL query
using (DatabaseEntities context = new DatabaseEntities())
{
var query = @"select r.UserID, r.UserName from DatabaseEntities.Users as r";
var result1 = context.CreateQuery<User>(query);
}
Generated SQL code::
SELECT
[Extent1].[UserID] AS [UserID],
[Extent1].[UserName] AS [UserName]
FROM [dbo].[Users] AS [Extent1]
Compiling LINQ queries
In most cases the majority of the execution time is spent in SQL-code generation phase. Moreover, it is repeated during each invocation. To avoid this, one can use the CompiledQuery classes of the same name, located in:
- System.Data.Linq – for LINQ to SQL
- System.Data.Objects – for EF
Before proceeding with their consideration, it is necessary to understand lambda expressions. To compile the query, you need to call the Compile() function for both classes. The difference between them is only in the type of the first argument: for EF it is System.Data.Objects.ObjectContext, and for LINQ to SQL it is System.Data.Linq.DataContext.
//EF CompiledQuery
var q = System.Data.Objects.CompiledQuery.Compile<DatabaseEntities, IQueryable<Profile>>
(ctx =>
from p in ctx.Profiles
where p.ProfileID == 1
select p);
var context = new DatabaseEntities();
var result = q(context).ToList();
//LINQ to SQL CompiledQuery
var q = System.Data.Linq.CompiledQuery.Compile<DatabaseContext, IQueryable<Profile>>
(ctx =>
from p in ctx.Profiles
where p.ProfileID == 1
select p);
var context = new DatabaseContext();
var result = q(context).ToList();
I would like to note the following points:
- When compiling LINQ queries, anonymous classes can’t be used
- EF lacks the ability to compile eSQL. But eSQL is always cached, and even that can be disabled.
Заключение
Thus, using the above techniques when working with ORM frameworks, we can achieve quite performant code. For example, the query compilation alone speeds up things by more than 5x, and specifying only the required columns when selecting allows us to achieve higher performance too.