Entity Framework Database Call Issues

Photo: Mika Baumeister - Unsplash

Entity Framework (EF) is an abstraction over database queries and interactions. It is billed as more than another Object Relationship Mapper. It has enabled developers to quickly create data-driven applications. But, as a wise person once said: “with great power comes the need to know how your queries are created and executed by Entity Framework”. Or at least something like that.

There are a host of issues that I actually have with the use of EF. These are not all entirely fair given that if you take your time to learn the library and its intricacies it is very powerful. One of the most useful conversations I had was interviewing someone whose best friend was in the EF team. But, a large number of less experienced developers I work with don’t do put the effort in to learn the library. They read the examples and start to build code. In providing the abstraction Microsoft has removed the need for developers to know about the database.

When you mix in the Linq helpers you start to see code like makes many database calls.

var items = _dbContext.Items.Select(x => x.Type == 1).ToList();
        
// Some Other code


var subSetOfItems = _dbContext.Items.Select(x => x.Type == 1 && x.SubType == 4).ToList();

I call this the “Getting it as you need it problem”. The developer is getting objects as they need them. There is one more DB call there than needed.

var items = _dbContext.Items.Select(x => x.Type == 1).ToList();
// Some Other code 

var subSetOfItems = items.Where(x => x.SubType == 4).ToList();

One less DB call and the same result (in this case).

The point here is not really EF doing anything wrong but that it makes throw away database calls to easy to not spot. If you are working with a set of objects throughout your Class or Method then get the largest subset of data once and use Linq To Objects to get your subsets.

The other problem that is far worse than I see is what I call “The Database Death Loop”

var items = _dbContext.Items.Select(x => x.Type == 1).ToList();

foreach(var item in items)
{
    var relatedItem = _dbContext.RelatedItems.Select(x => x.Id = item.RelatedItemId).FirstorDefault();
    // Do something with objects

}

Do you see the problem? I see this a surprising amount and when you point it out to developers they generally let out a nervous laugh. If you have not called an EF execution query execution method (such as ToList()) to execute the query, you instead create a deferred query. That is that a query will be executed for each matching item in the Select statement. This means that you return a list of 500 items and go off to loop through them and get the Related Items and you create 500 DB calls. I pity your database and network.

As a slight pro tip, if you are only reading data with no intention of writing it back then research the NoTracking option with EF. This might be a blog post itself soon.

These are only a couple of the common issues that I see with EF. I may write about the others soon. The point I am trying to make is that you need to take time and learn EF at a deeper level that then example articles. If your application requires performance of any kind then think about how to create your queries and execute them.

Please share your thoughts around the issues you see with inexperienced developers using EF with me via twitter or email.