CodeX
Published in

CodeX

Best practices with Entity Framework

Entity Framework offers a lot of convenience for developers but also comes with performance penalties for users, in certain cases. It is a developer’s job to make the best use of Entity Framework and still maintain great user experience.

I used to write code using SQL Queries for a long time. But after moving to Entity Framework, I cannot imagine going back. The abstraction of entire database tables in model objects makes it incredibly easy to manage database and roll-out updates without the fear of breaking any functionality.

If you are like me, who likes to keep the majority of database operations inside application code instead of putting it inside database (stored procedures, triggers etc.), you may have also encountered performance issues of Entity Framework. Depending on the use case, the performance penalty is negligible in majority of the cases. For e.g., if you are inserting, updating or deleting a single record, the difference is usually not noticeable. However, when performing bulk operations, Entity Framework may cause serious delays.

Over the years, I have learned some best practices to avoid performance loss while still staying empowered by Entity Framework.

Don’t use RemoveRange, ever!

At times, you need to delete multiple records based on a condition and the default method offered by EntityFramework is RemoveRange.

db.Table.RemoveRange(db.Table.Where(a => a.Price <= 0).ToList())

The problem with above approach is that it fires a delete query for every single record returned by the underlying query. For e.g., if the query db.Table.Where(a => a.Price <= 0) results in 20 records, there will be 20 delete queries fired by the Entity Framework to the database. This is very inefficient and should be completely avoided (it may still be fine if the number is 20, but not when it is 2,000!). There is a Nuget package named Z.EntityFramework.Extensions, which makes this much more robust. Using this extension package, you can write query like this:

db.Table.Where(a => a.Price <= 0).DeleteFromQuery()

This extension has a free version that supports DeleteFromQuery and many more methods. To install the free Nuget, search for z.entity in nuget package manager.

Never loop inside DbContext instance!

Usually, when you start a DbContext instance, it looks like this:

using (AppDbContext db = new AppDbContext())
{
// your code here
}

It is never a good idea to put a for/while loop inside this instance, if there is some Db operation going on inside the loop. Basically what happens is, which each iteration, the Entity Framework keeps tracking the entities (even if you are calling SaveChanges within the loop). As the number of iterations increase, the operation time will keep increasing. If your loop iterates 1,000 times, your 1,000th iteration time may become 10,000 times more than the 1st iteration (That’s not a tested or measured number, just a raw estimate to indicate the impact).

Therefore, it’s always better to instantiate a new instance of DbContext inside your loop:

foreach(var x in items)
{
using (AppDbContext db = new AppDbContext())
{
// your code here
}
}

.NET Core users: If you are using DbContext as a dependency injection, it might be a good idea to avoid using that instance of DbContext for bulk/loop operations. Create another constructor of your DbContext class which you can call directly with using statement shown above.

Use ExecuteSqlCommand only when necessary

ExecuteSqlCommand is equivalent to executing raw SQL queries against the database where no entity tracking/validation is carried out by Entity Framework.

However, there may be certain cases where you cannot avoid using it. For e.g., you may need to archive records older than 6 months on a table. In such cases, it is easier to run a single query and update the relevant column instead of pulling the records in EF and updating one by one. An example could be:

using (AppDbContext db = new AppDbContext())
{
string command = "UPDATE Table1 SET Archived=1
WHERE DateCreated < DATEADD(MONTH, -12, GETUTCDATE())";
db.Database.ExecuteSqlCommand(command);
}

As I mentioned in the beginning, the above command is a raw query and as such, it is error-prone. In case the DB Schema undergoes a change, for e.g., if you rename Table1 or rename any of the columns Archived or DateCreated, the above code will break at runtime. Your code will, however, still compile just fine (the query is just a text string).

To avoid such failures, it is a good idea to tag such code blocks with a unique identifier in your application, like adding a comment //RAW_QUERY. Using such comment, you can find and review all such cases across your application, each time you update the DB Schema and make the necessary changes in the query.

Avoid defining Foreign Keys on Model classes

As per EF documentation, it is recommended to maintain your Foreign Keys at database level. If you are using Code First migrations, then probably you need to define Foreign Key relationships on model classes so that migrations can automatically create them for you. I would still suggest otherwise. If the database schema is relatively smaller, say less than 20–30 tables, it is advisable to create the FK manually on the database and keep your models only as a representation of table columns and their data types.

Defining FKs inside model classes brings many complexities with itself (like Lazy Loading), which are beyond the scope of this article. Even if you do so, there is no way Entity Framework can ensure that your data is not violating any foreign keys.

In a nutshell, let the Database take care of data integrity by way of maintaining relationships, keys, indexes etc. As far as possible, use Entity Framework for CRUD operations only.

Name Your Tables

Entity Framework follows a pluralization pattern to derive database table names from model class names. For e.g., a model class named Customer will be interpreted as Customers table. This is not unpredictable but feels cloudy to me personally. For e.g. a table name CustomerInfo will not become CustomerInfos but it will become CustomerInfoes. Another problem is that your DbSet name can be completely different and EF still query database based on the model class name. Consider this example:

//Model Class
public class Customer
{
public int Id {get; set;}
public string Name {get; set;}
}
//DbSet
public virtual DbSet<Customer> Clients {get; set;}

You see, the model name (Customer), DbSet name (Clients) and actual database table name (Customers) can be 3 different values in above example. You would ask why one would come-up with such a name like ‘Clients’ in the DbSet. It happens, when you are working on a project from months or years, and numerous changes have happened, this is quite possible to happen along the way. Believe me, I am saying from experience.

Now, for the solution.

You can instruct DbContext to not use Pluralization at all. But in that case your model class name should be same as the database table name. This does not appear to be the right approach.

To me, the best method is to declare the table name at top of your model class:

[Table("Customers")]
public class Customer
{
public int Id {get; set;}
public string Name {get; set;}
}

This way, you are 100% sure what the table name would be queried in the database when you fire an Entity Framework command.

Those are my top learnings and best practices for Entity Framework based on past many years of development.

Feel free to let me know your disagreements, improvements or thoughts in the comments.

Software is never finished, only abandoned.

To read all my stories, along-with thousands of other writers on medium, consider opting for medium membership. If you join using my link, I will get a commission from your joining fee. Good Luck!

--

--

--

Everything connected with Tech & Code. Follow to join our 900K+ monthly readers

Recommended from Medium

About Cassandra

Using Helm Deploy A Jenkins Server In K8S Cluster

OSCP Exam — Mistakes and Wins

Why we’re backing Metaflow

Reactive Programming with MVVM for Mobile Apps

AWS Cloud Watch Alarm for EC2 Instance CPU Utilization

[JAVA-2b] Building a Simple Calculator: Asking for Input

How to Sort Arrays in PHP

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Prashant

Prashant

Finance guy who fell in love with technology and switched careers in early 30s. Follow me: https://twitter.com/prashantio Business URL: https://runtime.one

More from Medium

Web Api Versioning

Backup and Restore MySQL database (.sql) using C# .NET

Metacharacters, master your regex. Part 1

[Video] Getting a complex type as a simple type from the query string in a ASP.NET