Entity Framework 6 Performance Considerations

Using Entity Framework it is easy to forget about SQL query optimization and as a result develop poor performing applications. A scenario that best illustrates this is fetching a list of results from a table that has foreign keys to one or more child tables.

Consider the simple class structure:

public class Person
{
    public int Id { get; set; }
    public string Firstname { get; set; }
    public string Lastname { get; set; }
    public virtual Address Address { get; set; }
}

public class Address
{
    public int Id { get; set; }
    public string Line1 { get; set; }
    public string Line2 { get; set; }
    public string Country { get; set; }
    public string PostCode { get; set; }
}

Assuming a code first scenario the context can be queried quite easily, here the context is used in an MVC controller to return a list of Person entities to a view.

public ActionResult LazyLoad()
{
    List<Person> viewModel = this._context.People.ToList();
    return View(viewModel);
}

In the view the navigation properties can be used to access properties in the linked Address entity.

@model List<Person>
<div>
@foreach (var person in Model)
{
<div>
    <h3>@person.Firstname @person.Lastname</h3>
    <ul>
        <li>@person.Address.Line1</li>
        <li>@person.Address.Line2</li>
        <li>@person.Address.Country</li>
        <li>@person.Address.PostCode</li>
    </ul>
</div>
}
</div>

Running the code everything works as you would expect. The SQL that is generated by Entity Framework can be inspected using SQL Profiler. This was part of Sql Server Management studio but can now be found in Start -> Programs -> Microsoft SQL Server Tools 17. Assuming SQL Server 2017.

Start a New Trace in the profiler and run the sample again, the queries that Entity Framework generates will be output to the trace. The first query is the initial context call to get all People:

SELECT 
[Extent1].[Id] AS [Id],
[Extent1].[Firstname] AS [Firstname], 
[Extent1].[Lastname] AS [Lastname], 
[Extent1].[Address_Id] AS [Address_Id]
FROM [dbo].[People] AS [Extent1]

In the view we are referencing the linked table for Address, Entity Framework did not know we also needed this data so it now has to go back to the db to fetch it, you will see the following query also executing:

exec sp_executesql N'SELECT 
[Extent2].[Id] AS [Id], 
[Extent2].[Line1] AS [Line1], 
[Extent2].[Line2] AS [Line2], 
[Extent2].[Country] AS [Country], 
[Extent2].[PostCode] AS [PostCode]
FROM [dbo].[People] AS [Extent1]
INNER JOIN [dbo].[Addresses] AS [Extent2] ON [Extent1].[Address_Id] = [Extent2].[Id]
WHERE ([Extent1].[Address_Id] IS NOT NULL) AND ([Extent1].[Id] = @EntityKeyValue1)',N'@EntityKeyValue1 int',@EntityKeyValue1=1

This is the Lazy Loading feature and so for each row in the list it generates a separate query. This can quickly cause a performance issue as the number of rows increases.

Includes

One way around the problem is to tell Entity Framework up front what you want it to retrieve by using includes. This means the developer needs to think about what linked entities are going to be required in much the same way they would when hand writing SQL queries. The syntax is as follows:

public ActionResult Includes()
{
    List<Person> viewModel = this._context.People.Include("Address").ToList();
    return View(viewModel);
}

Personally I don't like magic strings in code so you need to add in a reference to System.Data.Entities and then you can use linq expressions instead:

List<Person> viewModel = this._context.People.Include(ctx => ctx.Address).ToList();

Running the same code again and inspecting the SQL queries there is now only one query.

SELECT 
[Extent1].[Id] AS [Id], [Extent1].[Firstname] AS [Firstname], [Extent1].[Lastname] AS [Lastname], [Extent2].[Id] AS [Id1], [Extent2].[Line1] AS [Line1], [Extent2].[Line2] AS [Line2], [Extent2].[Country] AS [Country], [Extent2].[PostCode] AS [PostCode] FROM [dbo].[People] AS [Extent1] LEFT OUTER JOIN [dbo].[Addresses] AS [Extent2] ON [Extent1].[Address_Id] = [Extent2].[Id]

Projection

There is a drawback to relying on Entity Framework to do everything for you; since we are not specifying what fields we want from a table Entity Framework will always return everything. In small simple structures this may not be an issue but for large result sets a lot of unnecessary reads could be happening.

Rather than using the entities directly in our views we can create a poco view model class, this is beneficial in some cases as it further separates the view code from any persistence or business logic and reduces the possibility of exposing too much data to the front end.

This is a simple example of a view model class:

public class PersonViewModel
{
    public string Firstname { get; set; }
    public string Lastname { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string Country { get; set; }
    public string PostCode { get; set; }
}

The query needs to be modified slightly to return a list of PersonViewModel rather than the raw entities, this is where projection comes in and we can specify what should be returned.

public ActionResult Projection()
{
    List<PersonViewModel> viewModel = this._context.People
        .Select(p => new PersonViewModel()
        {
            Firstname = p.Firstname,
            Lastname = p.Lastname,
            Address1 = p.Address.Line1,
            Address2 = p.Address.Line2,
            Country = p.Address.Country,
            PostCode = p.Address.PostCode
        }).ToList();

        return View(viewModel);
}

By doing this Linq to Entities can build a more efficient query and only return what it needs from the db. Running the code again and inspecting the profiler the result is the following query:

SELECT 
1 AS [C1],
[Extent1].[Firstname] AS [Firstname],
[Extent1].[Lastname] AS [Lastname],
[Extent2].[Line1] AS [Line1],
[Extent2].[Line2] AS [Line2],
[Extent2].[Country] AS [Country],
[Extent2].[PostCode] AS [PostCode]
FROM [dbo].[People] AS [Extent1]
LEFT OUTER JOIN [dbo].[Addresses] AS [Extent2] ON [Extent1].[Address_Id] = [Extent2].[Id]

Notice that this time it does not return the Id fields and only returns what was specified in the .Select.

This can be taken one step further to help promote code re-use. There may be more than one scenario where we want to use the same view model so rather than repeating the Select statement a mapper class could be used. The first attempt might look like this:

public PersonViewModel MapViewModel(Person p)
{
    return new PersonViewModel()
    {
        Firstname = p.Firstname,
        Lastname = p.Lastname,
        Address1 = p.Address.Line1,
        Address2 = p.Address.Line2,
        Country = p.Address.Country,
        PostCode = p.Address.PostCode
    };
}

// In the controller action PersonMapper mapper = new PersonMapper(); List<PersonViewModel> viewModel = this._context.People.Select(p => mapper.MapViewModel(p)).ToList();

But there is a problem, in order to use projection Linq to Entities must be able to translate the mapping; running the above code will result in the following error.

LINQ to Entities does not recognize the method 'PersonViewModel MapViewModel(Person)' method, and this method cannot be translated into a store expression.

One way around this is to create a static method on the ViewModel to return the projection expression instead.

public static Expression<Func<Person, PersonViewModel>> MapFromPerson() 
{
    return p => new PersonViewModel() 
    {   
        Firstname = p.Firstname,
        Lastname = p.Lastname,
        Address1 = p.Address.Line1,
        Address2 = p.Address.Line2,
        Country = p.Address.Country,
        PostCode = p.Address.PostCode
     };
}

This assumes the caller always returns an IQueryable, the added benefit here is you can further filter the query later without worrying about unexpected lazy loads or extra trips to the db.

public ActionResult Mapper()
{
    List<PersonViewModel> viewModel = this._context.People.Select(PersonViewModel.MapFromPerson()).ToList();
    return View(viewModel);
}

One important caveat is the IQueryable<T> type, if IEnumerable<T> is used instead then you can end up reading too much from the db take the following example. This can happen unintentionally in some cases:

public class PersonRepository
{
     private readonly MyContext _context;

     public PersonRepository(MyContext context)
     {
         this._context = context;
     }

     public IEnumerable<Person> GetAllPeople()
     {
// Although this is an IQueryable<Person> it will be implicitly converted to IEnumerable<Person> return this._context.People; } }

The above is an overly simplified example used to wrap Entity Framework but it may be used to wrap some common repository functions and could be used to further separate db logic from business logic.

public ActionResult Repository()
{
    PersonRepository repo = new PersonRepository(this._context);
    List<PersonViewModel> viewModel = repo.GetAllPeople().Select(PersonViewModel.MapFromPerson()).Where(x => x.Firstname == "First").ToList();
    return View(viewModel);
}

You might expect this to apply the where clause at the SQL level but because the repository returns an IEnumerable<T> rather than an IQueryable<T> the where clause will actually happen at the object level and the SQL query will be as follows:

SELECT
[Extent1].[Id] AS [Id], 
[Extent1].[Firstname] AS [Firstname],
[Extent1].[Lastname] AS [Lastname],
[Extent1].[Address_Id] AS [Address_Id]
FROM [dbo].[People] AS [Extent1]

This could make a big difference to a site's performance since the db will be reading every db row materializing as Entity objects then possibly filtering out a large majority. Simply changing the repository to return an IQueryable<T> instead in the above example results in the following SQL:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Firstname] AS [Firstname],
[Extent1].[Lastname] AS [Lastname],
[Extent1].[Address_Id] AS [Address_Id]
FROM [dbo].[People] AS [Extent1]
WHERE N'First' = [Extent1].[Firstname]

This can be quite useful when used with projection, a new field is added to the PersonViewModel and static method to concatenate the Full address like so:

public static Expression<Func<Person, PersonViewModel>> MapFromPerson() 
{
    return p => new PersonViewModel() 
    {   
        Firstname = p.Firstname,
        Lastname = p.Lastname,
        Address1 = p.Address.Line1,
        Address2 = p.Address.Line2,
        Country = p.Address.Country,
        PostCode = p.Address.PostCode,
        FullAddress = p.Address.Line1 + " " + p.Address.Line2 + " " + p.Address.Country + " " + p.Address.PostCode
     };
}

The FullAddress property does not exist in the Address table but because we have added in a mapping linq to sql can still determine a SQL expression even if it used in a where clause:

public ActionResult FullAddress()
{
     List<PersonViewModel> viewModel = this._context.People.Select(PersonViewModel.MapFromPerson()).Where(x => x.FullAddress == "First Second").ToList();
     return View(viewModel);
}

This results in the following not so attractive SQL query however:

SELECT 
1 AS [C1], 
[Extent1].[Firstname] AS [Firstname],
[Extent1].[Lastname] AS [Lastname],
[Extent2].[Line1] AS [Line1],
[Extent2].[Line2] AS [Line2],
[Extent2].[Country] AS [Country],
[Extent2].[PostCode] AS [PostCode]
CASE WHEN ([Extent2].[Line1] IS NULL) THEN N'' ELSE [Extent2].[Line1] END + N' ' + CASE WHEN ([Extent2].[Line2] IS NULL) THEN N'' ELSE [Extent2].[Line2] END + N' ' + CASE WHEN ([Extent2].[Country] IS NULL) THEN N'' ELSE [Extent2].[Country] END + N' ' + CASE WHEN ([Extent2].[PostCode] IS NULL) THEN N'' ELSE [Extent2].[PostCode] END AS [C2]
FROM [dbo].[People] AS [Extent1]
LEFT OUTER JOIN [dbo].[Addresses] AS [Extent2] ON [Extent1].[Address_Id] = [Extent2].[Id]
WHERE N'First Second' = (CASE WHEN ([Extent2].[Line1] IS NULL) THEN N'' ELSE [Extent2].[Line1] END + N' ' + CASE WHEN ([Extent2].[Line2] IS NULL) THEN N'' ELSE [Extent2].[Line2] END + N' ' + CASE WHEN ([Extent2].[Country] IS NULL) THEN N'' ELSE [Extent2].[Country] END + N' ' + CASE WHEN ([Extent2].[PostCode] IS NULL) THEN N'' ELSE [Extent2].[PostCode] END)

So with Entity Framework thinking about how the queries will end up at the db can mean the difference between good and poor performing sites. But it is good practice to always verify that the resulting SQL queries are not going to be a performance issue.