18 Jun

Entity Framework – Best practices

Entity FrameworkSo lately I’ve been having a lot of performance issue on one of the application I’ve been maintaining. And surprisingly, most issue were related to the way we have been using Entity Framework in the application.
In this post I would like to show you some of my findings which may help you run your application more smoothly.


 

If I would let some of my friends or colleagues answer the above question “The right way to use Entity Framework”, they would probably say “is by not using it”. Because face it, it is slower than some other known frameworks out there. Just a quick Google search and you’ll find more than one post which compares Entity Framework with ADO.Net or Dapper or… And always Entity Framework is the Internet Explorer of the Frameworks.
In this post I would like to show you some best practices I’ve learned while using Entity Framework V6.



My test data

I’ll show you these examples with a simple calendar database structure. Meaning, we’ll have Years, Months and Days.

Year.cs
public class Year
{
public int Id { get; set; }
public int Name { get; set; }
public ICollection<Month> Months { get; set; }
public Year()
{
Months = new List<Month>();
}
}
Month.cs
public class Month
{
public int Id { get; set; }
public string Name { get; set; }
public int YearId { get; set; }
public Year Year { get; set; }
public ICollection<Day> Days { get; set; }
public Month()
{
Days = new List<Day>();
}
}
Day.cs
public class Day
{
public int Id { get; set; }
public string Name { get; set; }
public int MonthId { get; set; }
public Month Month { get; set; }
}

 

Lazy Loading

One of the first things you should start with is disabling Lazy Loading. I don’t know why it’s turned on by default, but I’d rather control my own code then letting it go automatically.
Turning it off can be done when initializing the Context in its constructor:

public Context()
{
Configuration.LazyLoadingEnabled = false;
Configuration.ProxyCreationEnabled = false;
}

 

.AsNoTracking()

When I wanted to write a simple query to get a certain month:

var month = context.Months.Where(x => x.YearId== 10 && x.Name = "March").Single();

Nothing special, it’ll get my month object.
Now, what if I added this:

var year = context.Years.Where(x => x.Id == 10).Single();

You would think this will just get my Year-object and that’s it, but Entity Framework will map the two objects together because it knows that Month-object is linked to Year with Id 10.
I can hear you think “this saves me time mapping the objects myself”, and it does. But think about the amount of times you needed an object and you don’t really care that it’s linked. Mapping all the objects together does slow down Entity Framework, although on small data-sets you will not notice this. Once you will use bigger data-sets you’d want to make sure everything goes as fast as possible.
Entity Framework has a simple solution for this, .AsNoTracking(), and it’s used like this:

var year = context.Years.Where(x => x.Id == 10).AsNoTracking().Single();

Do note that .AsNoTracking() is best used in read-only situations. If you want to update an object you received from the database with .AsNoTracking() you will have to attach it again to the context.



.Include()

At first when I wanted all the Years with all its months and days, I would write something like this:

var years = context.Years.Where(x => x.Name == 2016)
.Include("Months")
.Include("Months.Days")
.Single();

This does work and on a local machine you don not really notice much performance issues either.
Now lets see which query Entity Framework creates to call to the database:

{SELECT
[Project1].[f_id] AS [f_id],
[Project1].[f_name] AS [f_name],
[Project1].[C2] AS [C1],
[Project1].[f_id1] AS [f_id1],
[Project1].[f_name1] AS [f_name1],
[Project1].[f_year_id] AS [f_year_id],
[Project1].[C1] AS [C2],
[Project1].[f_id2] AS [f_id2],
[Project1].[f_name2] AS [f_name2],
[Project1].[f_month_id] AS [f_month_id]
FROM ( SELECT
[Extent1].[f_id] AS [f_id],
[Extent1].[f_name] AS [f_name],
[Join1].[f_id1] AS [f_id1],
[Join1].[f_name1] AS [f_name1],
[Join1].[f_year_id] AS [f_year_id],
[Join1].[f_id2] AS [f_id2],
[Join1].[f_name2] AS [f_name2],
[Join1].[f_month_id] AS [f_month_id],
CASE WHEN ([Join1].[f_id1] IS NULL) THEN CAST(NULL AS int) WHEN ([Join1].[f_id2] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
CASE WHEN ([Join1].[f_id1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
FROM [dbo].[t_year] AS [Extent1]
LEFT OUTER JOIN (SELECT [Extent2].[f_id] AS [f_id1], [Extent2].[f_name] AS [f_name1], [Extent2].[f_year_id] AS [f_year_id], [Extent3].[f_id] AS [f_id2], [Extent3].[f_name] AS [f_name2], [Extent3].[f_month_id] AS [f_month_id]
FROM [dbo].[t_month] AS [Extent2]
LEFT OUTER JOIN [dbo].[t_day] AS [Extent3] ON [Extent2].[f_id] = [Extent3].[f_month_id] ) AS [Join1] ON [Extent1].[f_id] = [Join1].[f_year_id]
WHERE 2016 = [Extent1].[f_name]
) AS [Project1]
ORDER BY [Project1].[f_id] ASC, [Project1].[C2] ASC, [Project1].[f_id1] ASC, [Project1].[C1] ASC}

Now this is just plain ugly. Not only is this a big query, it could also get really complex if you would introduce more .Includes or where-expressions. Also imagine if this query would run on a Azure SQL database, which is much less forgiving then a local or normal SQL server instance. Performance would be bad and disastrous for your application.
Now the solution to this is actually really simple.
Entity Framework is running faster if you would get all the objects separately. Entity Framework will also map all the objects together (as explained with .AsNoTracking()), which returns the same result, but the queries are much smaller and less complex then the other one.

var year = context.Years.Single(x => x.Name == 2016);
var months = context.Months.Where(x => x.YearId == year.Id).ToList();
var monthIds = months.Select(x => x.Id).ToList();
var days = context.Days.Where(x => monthIds.Contains(x.MonthId)).ToList();

Notice how there is not going to be any join-expression and that we’re using all indexed keys. This is really going to boost your performance, especially on big databases and on Azure SQL.

 

Loops

As shown above there are some ways to improve the performance of Entity Framework, but you also have to make sure the way you code is optimal.

var weekends = new string[] { "Saturday", "Sunday" };
var months = context.Months.Where(x => x.YearId == 20);
foreach (var month in months)
{
var days = context.Days.Where(x => x.MonthId == month.Id && weekends.Contains(x.Name)).ToList();
//do some stuff...
}

This code is purely as an example of going to the database in a foreach loop.
We all know that going to the database in a loop is never a good idea. Every time a connection to the database has to be made, every time an almost same query has to be executed,… This will just slow down you application.
Instead, just do one bigger call outside the loop.

var weekends = new string[] { "Saturday", "Sunday" };
var months = context.Months.Where(x => x.YearId == 20);
var monthIds = months.Select(x => x.Id).ToList();
var days = context.Days.Where(x => monthIds.Contains(x.MonthId) && weekends.Contains(x.Name)).ToList();
foreach (var month in months)
{
var monthDays = days.Where(x => x.MonthId == month.Id).ToList();
//do some stuff...
}



Bulk actions

When you want to process a large amount of data through EF, it could take you a lot of time. The reason for this is because EF is doing a lot of background checks which most of the time you don’t really need.
Some of these checks can be easily avoided.

Update

Updating is easy. You just need to make sure that you track your changes (So no ‘AsNoTracking()’). By doing this, EF will know what as changed. When you call ‘SaveChanges();’ EF will update all your data.

var days = daysRepo.GetByYear("2018");
foreach (var day in days)
    day.Name = "day";
daysRepo.SaveChanges();

Create

To bulk create you need to disabled EF’s changetracking. This can easily be done by disabled AutoDetectChanges.
Once you’re done, you need to turn Changetracking back on otherwise you might have some unwanted behavior.

Configuration.AutoDetectChangesEnabled = false;
foreach (var newDay in daysOf2019)
    dayRepo.Add(newDay);
ChangeTracker.DetectChanges();
SaveChanges();

Remove

When you want to remove a large amount of data, you just need to make sure that you remove data by object, not by Id. Otherwise you’ll be calling the database everytime you want to remove an object. Better to call it once, and then remove.

var daysToRemove = daysRepo.GetByIds(ids);
foreach (var day in daysToRemove)
    daysRepo.Remove(day);
SaveChanges();

 

Database schemas

You should also watch out when using different schemas within Entity Framework. EF will allocate a certain amount of your internal memory to save the schema structure.
Logically, the more schemas you have, the more memory will be allocated. This could, depending on the amount of schemas, cause your server to run low on internal memory.



Sources

 

I’ll update this post once I have more best practices for Entity Framework.

Please let me know what you think about this post. Do you have any comments or suggestions, let me know by writing them down in the comments below. I’ll gladly investigate them and if needed update this post.

2 thoughts on “Entity Framework – Best practices

  1. if you are talking about best practises, turn off lazy loading
    Configuration.LazyLoadingEnabled = false;
    Configuration.ProxyCreationEnabled= false;
    https://alexandrebrisebois.wordpress.com/2013/08/29/friends-dont-let-friends-use-lazy-loading-on-windows-azure/

    also these tips are for L2S, but they also apply for EF
    http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html
    and here is another link for EF bulk inserts
    https://weblog.west-wind.com/posts/2013/Dec/22/Entity-Framework-and-slow-bulk-INSERTs

    Hope it helps 🙂

    • Of course the Lazy Loading option should be turned off, how could I forget to add it :). Lucky it is turned off in our application though.

      I’ll have a look at the other blogs and see if I can add some points into my post.

      Thanks again!

Leave a Reply

Your email address will not be published. Required fields are marked *