In this article we’ll go through simple things to keep in mind while using Entity Framework to improve the performance of our code.
Paging with Skip and Take
When dealing with large collections of entities we should avoid returning them all at once. It’s a performance issue, since all of the entities will be loaded to RAM, but also it’s unnecessary if we’re not using them all at once. Let’s say we’re showing only a page of the table at a time.
We should always return only the number of entities we’re using by applying backend pagination using Skip and Take methods.
IEnumerable<Entity> GetEntitiesPaged(int page, int pageSize)
{
return this.context.Entities
.OrderBy(e => e.OrderProp)
.Skip(page * pageSize)
.Take(pageSize);
}Avoiding in-memory executions
When dealing with collections, more often than not, I will see an advice to call ToList() on a query before doing Where(...) since the query itself is unable to run on the DB. Problem with this is that all of the entities will be loaded into RAM and filtering logic will be done locally and not on the DB.
This performance issue should be avoided by changing our query so it’s a valid linq-to-entities expression.
DbFunctions
When filtering entities by date only we would just call .Date on our DateTime property right? Well this isn't a valid linq-to-entities expressions and it's unable to execute against DB. DbFunctions class contains many helper methods we can utilize to perform valid linq-to-entity expressions and DbFunctions.TruncateTime is one of them.
IEnumerable<Entity> GetYesterdayEntities()
{
var yesterday = DateTime.Now.AddDays(-1).Date; return this.context.Entities
.Where(e =>
DbFunctions.TruncateTime(e.DateTimeCreated) == yesterday
);
}AsNoTracking
When using EF we’re able fetch entity from database, change some of the properties and call SaveChanges() on the context and the entity will be updated. The reason EF can do that is that entities we fetch are tracked and cached by DbContext. If we're not going to do any changes on our entities we should fetch untracked entities by calling AsNoTracking() because of the performance benefits.
IEnumerable<Entity> GetByUser(long userId)
{
return this.context.Entities.AsNoTracking()
.Where(e => e.UserId == userId);
}Selecting only needed data
If you’re doing an operation using one property of the entity, it’s beneficial to Select said property instead of fetching the whole entity.
...
foreach(var amount in this.context.Entities.Select(e => e.Amount))
{
// Do something only with "Amount" property
}
...Async code
Using asynchronous code increases performance and enhances responsiveness of our apps. We’re able to use several async equivalents of the synchronous queryable extensions method. Full list, for your version of EF, can be found at QueryableExtensions
async Task<Entity> Update(IEntityUpdate updateModel)
{
var dbModel = await this.context.Entities
.FirstOrDefaultAsync(e => e.Id = updateModel.Id); model.Prop1 = updateModel.Prop1;
model.Prop2 = updateModel.Prop2; await this.context.SaveChangesAsync();
}Avoiding N + 1 problem
N + 1 problem is characterized by additional unnecessary queries to DB being made. For example we want to fetch collection of entities and then foreach entity we would like to calculate something using their navigational property.
...
foreach(var entity in this.context.Entities)
{
entity.AdditionalItems.ForEach(ai => ...)
}
...The first loop (foreach) fetches all entities from database using one query (that’s the 1 in N + 1). Additionally we want to access AdditionalItems property of each entity. This fetching of the AdditionalItems will be made N times for each entity in a separate SQL query (that’s the N in N + 1).
Solution to this problem is that if we’re aware that we’ll be accessing additional data for each entity we fetch — we should fetch it with the original query for entities. With this approach only one DB query will be executed.
... var entitiesQuery = this.context.Entities
.Include(e => e.AdditionalItems);foreach(var entity in entitiesQuery)
{
entity.AdditionalItems.ForEach(ai => ...)
}
...Conclusion
These were simple and easy to implement tips for improving your code while using Entity Framework. If you have any suggestions or questions please let me know in the responses section.
Source: Medium - Josip Paladin
The Tech Platform
Comments