Cartesian Explosion problem in Entity Framework

/ Leave a Comment

In relational databases like SQL, you can load related entities using Joins. In case of one-to-many relationships, this would lead to duplicated data returned. For example, entity Book has one-to-many Comments. If a book has 3 comments, the query below will return 3 rows, one for each comment with duplicate data for the book.

Now let’s say the book also has many chapters, if we include chapters in the query and the book has 3 chapters, the query will return 9 rows and lots of duplicate data.

Cartesian Explosion problem is when you load more and more one-to-many relationships, the amount of duplicate data grows and large number of rows are returned. This will have a large impact on the performance of your application. As you can see from the example, this could get really large very quickly.

Entity Framework is a great ORM to use. It makes it very easy for developers to query SQL databases. However, it also makes it very easy to add many joins by using Include and ThenInclude when they need the related entities without thinking about the potential performance impact.

var books = DbContext.Books
    .Include(p => p.Comments)
    .Include(p => p.Chapters)
    .Where(p => p.Id == bookId)
    .ToArray();

One of the ways to fix this issue is to not load all entities in one query. You can load the related entities manually by using the Load() on the dataset

var books = DbContext.Books
    .Where(p => p.Id == bookId)
    .ToArray();
DbContext.Comments.Where(p => p.Id == bookId).Load();
DbContext.Chapters.Where(p => p.Id == bookId).Load();

However, EF has its own way that allows you to load related entities with multiple queries automatically, using Split Queries. This is also my preferred option as it requires a lot less code than loading the related entities manually. All you have to do is specify that you want this to use split queries by adding AsSplitQuery

var books = DbContext.Books
    .Include(p => p.Comments)
    .Include(p => p.Chapters)
    .Where(p => p.Id == bookId)
    .AsSplitQuery()
    .ToArray();

You can set this as per query or you can set this globally for all the queries, see Microsoft documentation.

Split queries is great and does avoid the Cartesian Explosion. However it’s got its own drawbacks such as multiple roundtrips to the database, potentially inconsistent data when there are concurrent updates, etc. You can read more about these here.

Leave a Reply