5 Best Practices with Entity Framework Core

Entity Framework Core has huge possibilities. In the last article I mentioned to you that it can generate fast and optimal queries on the database, but for it to do this, it is worth knowing and following at least a few good practices. I will present the 5 most important ones to you in this article using specific examples.

Project Overview

Today we will also work on the project from the previous material (Introduction to Entity Framework Core), namely Bookstore. This project already has the required packages for Entity Framework Core installed.

Context:

namespace Bookstore
{
    class ApplicationDbContext : DbContext
    {
        public DbSet<Book> Books { get; set; }
        public DbSet<Category> Categories { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var builder = new ConfigurationBuilder()
                .AddJsonFile($"appsettings.json", true, true);

            var config = builder.Build();

            optionsBuilder
                .UseSqlServer(config["ConnectionString"]);
        }
    }
}

Entities:

namespace Bookstore
{
    public class Book
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }

        public int? CategoryId { get; set; }
        public Category Category { get; set; }
    }
}

namespace Bookstore
{
    public class Category
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public ICollection<Book> Books { get; set; } = new HashSet<Book>();
    }
}

Console application main class:

namespace Bookstore
{
    class Program
    {
        static async Task Main(string[] args)
        {
        }
    }
}

1. Checking the queries generated by Entity Framework Core

It is a good practice to check the queries generated by Entity Framework Core. There are several ways to do this. The simplest way is to display these queries in the console. All you need to do is call 2 additional methods in the configuration, in our case in the ApplicationDbContext class. First, the LogTo method, where we pass Console.WriteLine as parameters, as well as information about the data we want to log.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var builder = new ConfigurationBuilder()
        .AddJsonFile($"appsettings.json", true, true);

    var config = builder.Build();

    optionsBuilder
        .LogTo(Console.WriteLine, new[] { DbLoggerCategory.Database.Command.Name }, LogLevel.Information)
        .UseSqlServer(config["ConnectionString"]);
}

And also, the second method EnableSensitiveDataLogging(), which will also display all the detailed, more sensitive data, including parameter values, etc.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var builder = new ConfigurationBuilder()
        .AddJsonFile($"appsettings.json", true, true);

    var config = builder.Build();

    optionsBuilder
        .LogTo(Console.WriteLine, new[] { DbLoggerCategory.Database.Command.Name }, LogLevel.Information)
        .EnableSensitiveDataLogging()
        .UseSqlServer(config["ConnectionString"]);
}

Now when we execute a query on the database using Entity Framework Core, it will be displayed in our console.

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace Bookstore
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var books = new List<Book>();

            using (var context = new ApplicationDbContext())
            {
                books = await context.Books.ToListAsync();
            }

            foreach (var item in books)
            {
                Console.WriteLine($"Id: {item.Id}. Book: '{item.Name}' - $ {item.Price:0.00}.");
            }
        }
    }
}

In addition, you can view these queries in SQL Server Profiler, and you can even save them to a file. In this console application, I have already installed the NLog data logging framework and now we can easily configure logging of all queries to a file via Entity Framework Core. If you do not know how to install NLog, I refer you to this NLog article, where I showed you step by step how to do it. So, first in the class where we define the configuration, we need to initialize the logger factory and then pass it in the configuration as a parameter to the UseLoggerFactory() method. The whole thing will look like this:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using NLog.Extensions.Logging;
using System;

namespace Bookstore
{
    class ApplicationDbContext : DbContext
    {
        public static readonly ILoggerFactory _loggerFactory = new NLogLoggerFactory();

        public DbSet<Book> Books { get; set; }
        public DbSet<Category> Categories { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var builder = new ConfigurationBuilder()
                .AddJsonFile($"appsettings.json", true, true);

            var config = builder.Build();

            optionsBuilder
                .UseLoggerFactory(_loggerFactory)
                .LogTo(Console.WriteLine, new[] { DbLoggerCategory.Database.Command.Name }, LogLevel.Information)
                .EnableSensitiveDataLogging()
                .UseSqlServer(config["ConnectionString"]);
        }
    }
}

If you run the application now, something like this should appear in the file:

If you run the application now, then in the file aAnd depending on what you need, you can choose any option. It is worth adding such query recording to your application. You should see something like this:

2. Monitoring the length of query execution

The second best practice is to also monitor the length of query execution. You can add such monitoring only to individual queries, but it is even better to create some wrapper that will monitor all queries in your application in this way. To verify the length of the query, we will use an instance of the Stopwatch class:

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Threading.Tasks;

namespace Bookstore
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var stopwatch = new Stopwatch();

            var books = new List<Book>();

            using (var context = new ApplicationDbContext())
            {
                stopwatch.Start();
                books = await context.Books.ToListAsync();
                stopwatch.Stop();
            }

            foreach (var item in books)
            {
                Console.WriteLine($"Id: {item.Id}. Book: '{item.Name}' - $ {item.Price:0.00}.");
            }

            Console.WriteLine($"Query execution time: {stopwatch.ElapsedMilliseconds} MS.");
        }
    }
}

First we initialize a new stopwatch object. Then, just before the query, we call the Start() method on this object, and after the query is executed, the Stop() method. Thanks to this, we can calculate the query execution time and display it, for example, in Milliseconds below.
efcore-dobre-praktyki-monitorowanie-czasu-zapytania-konsola-2.jpg

3. Blocking object tracking in read-only queries

The third best practice is to block object tracking (i.e. change tracking) for read-only queries. If we execute any query on the database in Entity Framework Core, then by default the retrieved objects are tracked. We can disable it globally for all queries, but a better practice is to block this tracking for read-only queries. To do this, simply call the AsNoTracking() method on our query.

books = await context.Books
    .AsNoTracking()
    .ToListAsync();

Thanks to this, our query will be much faster, first of all. This can be seen especially in larger queries, where there is more data. Also, if you know that a given query is read-only. That is, you only want to display some data to the user for review, you will not make any changes to it, then the best practice is not to block object tracking.

4. Filtering data on the server side

The fourth best practice is filtering data on the server side. Sometimes I come across queries that first fetch all records from the database, and only then are filtered in memory. See this example:

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;

namespace Bookstore
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var stopwatch = new Stopwatch();

            var books = new List<Book>();

            using (var context = new ApplicationDbContext())
            {
                stopwatch.Start();

                books = await context.Books
                    .AsNoTracking()
                    .ToListAsync();

                books = books
                    .Where(x => x.Name == "Book 1")
                    .ToList();

                stopwatch.Stop();
            }

            Console.WriteLine($"Query execution time: {stopwatch.ElapsedMilliseconds} MS.");
        }
    }
}

efcore-dobre-praktyki-filtrowanie-po-stronie-klienta-3.jpg
So first we get all the data and then filter it by name. Such filtering will work, but as I mentioned, all the records will be downloaded from the database first, and only then they will be additionally filtered in memory. Of course, such a query will be much slower than when we perform such filtering directly on the database, and not on the collection in memory.

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;

namespace Bookstore
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var stopwatch = new Stopwatch();

            var books = new List<Book>();

            using (var context = new ApplicationDbContext())
            {
                stopwatch.Start();

                books = await context.Books
                    .AsNoTracking()
                    .Where(x => x.Name == "Book 1")
                    .ToListAsync();

                stopwatch.Stop();
            }

            Console.WriteLine($"Query execution time: {stopwatch.ElapsedMilliseconds} MS.");
        }
    }
}

If we call the query this way, then one query will be called on the database, which will immediately filter all records on the server side. This query will then be much faster (also even more noticeable with a larger amount of data). This is what the query will look like:
efcore-dobre-praktyki-filtrowanie-po-stronie-serwera-4.jpg

5. Minimize the commands sent to the database

The fifth best practice I would like to mention is to minimize the commands sent to the database. This is especially true when adding multiple records in a loop using the Add() method or using AddRange(), both versions are bad. See this example:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Threading.Tasks;

namespace Bookstore
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var stopwatch = new Stopwatch();

            var books = new List<Book>();

            for (int i = 0; i < 10000; i++)
            {
                books.Add(new Book { Name = $"Book {i}", Price = 120 });
            }

            using (var context = new ApplicationDbContext())
            {
                stopwatch.Start();

                foreach (var item in books)
                {
                    context.Books.Add(item);
                }

                await context.SaveChangesAsync();

                stopwatch.Stop();
            }

            Console.WriteLine($"Query execution time: {stopwatch.ElapsedMilliseconds} MS.");
        }
    }
}

As you can see, there are a lot of queries being made on the database, this data is added in batches, so it takes a long time and is not very optimal.

You can also use the AddRange method, but as you will see in a moment, it is not a much better solution.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Threading.Tasks;

namespace Bookstore
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var stopwatch = new Stopwatch();

            var books = new List<Book>();

            for (int i = 0; i < 10000; i++)
            {
                books.Add(new Book { Name = $"Book {i}", Price = 120 });
            }

            using (var context = new ApplicationDbContext())
            {
                stopwatch.Start();

                context.Books.AddRange(books);

                await context.SaveChangesAsync();

                stopwatch.Stop();
            }

            Console.WriteLine($"Query execution time: {stopwatch.ElapsedMilliseconds} MS.");
        }
    }
}

efcore-dobre-praktyki-minimalizowanie-komend-addrange-6.jpg

As you can see, the query is already a bit faster, but only a bit. Next, the data is transferred in batches, although in a slightly more optimal way, but a lot of commands are called on the database. Both the Add and AddRange methods are not optimal.

To add multiple records, it is best to use one of the many free extensions available for Entity Framework Core, for example one called EFCore.BulkExtensions. The entire documentation is available here: EFCore.BulkExtensions . All you need to do is install the package via nuget: EFCore.BulkExtensions.

efcore-dobre-praktyki-minimalizowanie-komend-efcore-bulk-extensions-7.jpg

Then you can invoke the command like this:

using EFCore.BulkExtensions;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Threading.Tasks;

namespace Bookstore
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var stopwatch = new Stopwatch();

            var books = new List<Book>();

            for (int i = 0; i < 10000; i++)
            {
                books.Add(new Book { Name = $"Book {i}", Price = 120 });
            }

            using (var context = new ApplicationDbContext())
            {
                stopwatch.Start();

                await context.BulkInsertAsync(books);

                stopwatch.Stop();
            }

            Console.WriteLine($"Query execution time: {stopwatch.ElapsedMilliseconds} MS.");
        }
    }
}

efcore-dobre-praktyki-minimalizowanie-komend-efcore-bulkextensions-add-8.jpg

As you can see, the query is much faster, the entire query is more optimal and does not execute as many separate commands. Thanks to this package, we can also update and delete many data in Entity Framework Core in a similar way.

Entity Framework Core School

If you would like to learn Entity Framework Core from the inside, how to create fast applications and efficient database queries, consider joining the Entity Framework Core School - [here].

SUMMARY

In this material, I have presented you with 5 best practices in my opinion when working with Entity Framework Core. Remember to:

Check generated queries.
Block object tracking for read-only queries.
Filter data on the server side.
Minimize sending commands to the database.

If you remember these rules, your queries will certainly be faster and you will not have problems with Entity Framework Core.

If you liked this article, be sure to join my community. Sign up for the free newsletter, where every week I share valuable materials, especially regarding C# and the .NET platform (free subscription - newsletter).