EF Core Pt. 2 - Configuration

Configuration

To configure your DbContext, you have two options:

• Use dependency injection and add it with AddDbContext
• Override the OnConfiguring method in your DbContext class.

public class BlogArticleDbContext : DbContext
{
...
protected override void OnConfiguring(DbContextOptionsBuilder options)
{
...
}
...
}

// OR in program.cs

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<BlogArticleDbContext>(options =>
{
...
});

var app = builder.Build();

Both of them will give you a DbContextOptionsBuilder object, for you to modify it's behavior.
In the code snippets below the DbContextOptionsBuilder is named "options". 

The first thing you normally do is to configure EF Core to use SqlServer with the UseSqlServer function. This has override version where you get another options builder of type SqlServerDbContextOptionsBuilder, called optionsBuilder in the snippets below.

options.UseSqlServer("YOUR CONNECTIONSTRING", optionsBuilder =>{
});

On the SqlServerDbContextOptionsBuilder you define how EF Core will connect and communicate with the backend data store, in this case a SQL Server.

We start in what options you have in the SqlServerDbContextOptionsBuilder or another type if you use a NoSql data store or in-memory database.

SQL Server configuration

Connection Resiliency

To be able to handle problems with network to the backend DB server you can configure retry handling. 

optionsBuilder.EnableRetryOnFailure();

This will use a predefined execution strategy that handles known errors that are possible to retry on. It will retry up to 6 times with a maximum of wait time of 30 seconds. It has several overrides so you are able to set number of retries, max wait time.

Note that if retry is used the memory usage will increase due to EF needs to store all your commands you want to send.

Having retires enabled could lead to problems if you are using transactions in some scenarios. What happens is that if a connection error occurs EF will try to resend all SaveChanges commands again. To handle this you need to encapsulate all in a transaction through a separate strategy instance like this:

var strategy = dbContext.Database.CreateExecutionStrategy();

strategy.Execute(() =>
{
using var context = new BlogArticleDbContext();
using var transaction = context.Database.BeginTransaction();
...
context.SaveChanges();
...
context.SaveChanges();
transaction.Commit();
});

 

Command Timeout

Another settings that is common to use is CommandTimeout, to set how long time EF will wait until the call is terminated. This example will wait for 30 seconds.

optionsBuilder.CommandTimeout(30);

If this is not set it will wait for until the call is finished.

Batch size

When you are sending your command by calling SaveChanges(), EF Core will batch all commands so not all of them are sent at one time. The default value is 42 commands and the EF Core team as done a lot of testing to reach this optimal value (and as we all know 42 is the answer to the question, coincident or not). You are able to change this value but recommendation is not to.

optionsBuilder.MaxBatchSize(42)

EF History table

EF Core will use one specific table to store information on which migrations that has been applied. The name of this table is "__EfMigrationHistory". You are of course able to change this name, even though there is no actual need to. The only time you want to do it if you want to use a custom EF history table. How to do that is described later in this article. 

optionsBuilder.MigrationsHistoryTable("newTableName");

Query Splitting

When you are working with EF and will join in data from related tables the generated SQL might be really big, complex and inefficient. In EF Core 5 they introduced the option to split the query into several smaller ones that are more efficient. Depending on your query command you are doing this might not always be the case. You are able to set this behavior globally, as follows:

optionsBuilder.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);
Or you can do this on each query you are doing instead to have better control, and only do it where it actually is better:

await dbContext.Customers.Include(x => x.Orders)
.ThenInclude(x => x.Rows)
.ThenInclude(x => x.Product)
.AsSplitQuery()
.ToListAsync();

// You use AsSingleQuery() to force it if the global behavior is SplitQuery

EF Core configuration

Logging

There are several levels of settings to get logging information while EF is doing it's work.
All of these are set on the options (DbContextOptionsBuilder) on the AddDbContext.

options.EnableDetailedErrors()

Will give some more information regarding data value errors.

options.EnableSensitiveDataLogging()

Will include the actual parameters sent in with the query. Without this setting only the dynamic parameter name will be presented. Note that this could be sensitive information and should not be activated in production.

options.LogTo(Console.WriteLine)
options.LogTo(message => Debug.WriteLine(message))

Could be used if you want to have the log in a different location or to for example a Debug listener as above.

Tracking behavior

EF will as default track all results returned from queries, for changes etc. In many cases this will be an performance overhead. In many cases you are doing simple read queries and there is no need to track changes, and to do that you need to add AsNoTracking() on all queries.

You are able to change this behavior so it will not track changes as default. To set this you add this to the options.

options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking)

Doing so you need to AsTracking() on the queries you want to be tracked instead.

In EF Core 5 they introduced NoTrackingWithIdentityResolution as an extra option. The difference is that it will reuse the entities returned, say for example if you including related objects e.g. get all orders and include the customer.

await dbContext.Orders.Include(x => x.Customer).ToListAsync()

The customer can be on many orders. With NoTracking it will create a new customer object per order, with NoTrackingWithIdentityResolution it will reuse the customer. So the new option is more memory efficient.

Interceptors

You are able to add interceptors on the EF Core context when it is sending it's operations. There is both for low-level operations where you are able to modify the SQL or high-level for operations like SaveChanges(). One common scenario is to add auditing on SaveChanges and it could look like this:

First create the interceptor, in this case inherit from SaveChangesInterceptor and only override the methods you want.

public class AuditInterceptor : SaveChangesInterceptor
{
public override InterceptionResult<int> SavingChanges(
DbContextEventData eventData,
InterceptionResult<int> result)
{
...
}

public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
DbContextEventData eventData,
InterceptionResult<int> result,
CancellationToken cancellationToken = new CancellationToken())
{
...
}
}

Second add the interceptor to the EF Core configuration

options.AddInterceptors(new AuditInterceptor());

Dependency Injection

The default behavior when using dependency injection and adding the DbContext with AddDbContext is that the DbContext is treated as ServiceLifeTime.Scoped. 

To change this behavior you need to pass in the wanted ServiceLifeTime as a second parameter in the AddDbContext call.

builder.Services.AddDbContext<BlogArticleDbContext>(options =>
{
...
}, ServiceLifetime.Transient);
 

Custom EF History table

If you are using several different DbContexts in your application, the existing EF Migration history are missing some information. Information like what DbContext the migration belongs to and maybe the date and time it was run.

To change this behavior we need to override an internal Repository

public class CustomEfHistoryRepository : SqlServerHistoryRepository
{
public CustomEfHistoryRepository(HistoryRepositoryDependencies dependencies)
: base(dependencies) { }

protected override void ConfigureTable(EntityTypeBuilder<HistoryRow> history)
{
base.ConfigureTable(history);
history.Property<string>("Context")
.HasMaxLength(300)
.IsRequired()
.HasDefaultValue("Unknown");
}

public override string GetInsertScript(HistoryRow row)
{
var stringTypeMapping = Dependencies.TypeMappingSource
.GetMapping(typeof(string));

return new StringBuilder().Append("INSERT INTO ")
.Append(SqlGenerationHelper.DelimitIdentifier(TableName, TableSchema))
.Append($" (" +
$"{SqlGenerationHelper.DelimitIdentifier(MigrationIdColumnName)}, " +
$"{SqlGenerationHelper.DelimitIdentifier(ProductVersionColumnName)}, " +
$"[Context])")
.Append($"VALUES (" +
$"{stringTypeMapping.GenerateSqlLiteral(row.MigrationId)}, " +
$"{stringTypeMapping.GenerateSqlLiteral(row.ProductVersion)}, " +
$"{stringTypeMapping.GenerateSqlLiteral(Dependencies.CurrentContext.Context.GetType().Name)})")
.AppendLine(SqlGenerationHelper.StatementTerminator)
.ToString();
}
}

In this repository, first we override the creation of the history table. Second we override and construct a different SQL that will run instead. In example above a column named Context is added that will contain the name of the DbContext class the migration is run on.

Final step is to configure to use this repository instead.

services.AddDbContext<DemoDbContext>(options =>
{
options.UseSqlServer("CONNECTIONSTRING", optionsBuilder =>
{
optionsBuilder.MigrationsHistoryTable("__CustomEfMigrationsHistory");
});
options.ReplaceService<IHistoryRepository, CustomEfHistoryRepository>();
}

On the optionsBuilder we tell EF what table name it should use instead of the original one. Recommendation is to create a new table instead of modifying existing. And finally we tell EF which repository to use by adding ReplaceService on the options.

Note that the SqlServerHistoryRepository is internal and they might later on set this as sealed or change it's behavior, but as for now this is the only way to modify this.