Advanced Entity Framework Core - Output EF SQL Queries To The Console And Tips To Setup Dbcontext With The Development In Mind

Advanced Entity Framework Core Tips In Practice

• DbContext separation, Fluent API, and Entities configuration
• Concurrency And SaveChanges Method Abilities💪
• Context pooling, Lazy vs Eager loading, Single vs. Split Queries, Tracking vs. No-Tracking Queries
• Extract data access layer with migrations to the library project and Execute migrations from the command line
• Output EF SQL Queries to the Console and tips to setup DbContext with the development in mind

A microservice template that uses concepts described in the articles can be found here.

Output SQL Queries to the Console

When you work with abstractions over SQL Database you may be wondering what does Entity Framework constructs from your Linq queries. To get a clue on it here are 3 options.

3 options to output EF queries to the Console,

DbContextOptionsBuilder.UseLoggerFactory()

public void ConfigureServices(IServiceCollection services) {
    ...services.AddDbContext < AppDbContext > (o => {
        ...o.UseLoggerFactory(_loggerFactory);
    });
}
private static readonly ILoggerFactory _loggerFactory = LoggerFactory.Create(builder => builder.AddConsole().AddDebug());

Set "Microsoft.EntityFrameworkCore.Database.Command": "Information" in appsettings.Development.json for default logging provider as well as Serilog

"Logging": {
  "LogLevel": {
    ...,
    "Microsoft.EntityFrameworkCore.Database.Command": "Information"
  }
}

"Serilog": {
  "MinimumLevel": {
    "Default": "Information",
    "Override": {
      ...,
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  },
},

query.ToQueryString() can be executed during debug or development

public async Task<IEnumerable<TodoItem>> Get()
{
    var query = _db.TodoItems
        .AsQueryable()
        .Where(x => x.Done)
        .OrderBy(x => x.Title);

    var sql = query.ToQueryString(); // also can be executted in watch debug window
/*
SELECT t."Id", t."Done", t."Title"
FROM "TodoItems" AS t
WHERE t."Done"
ORDER BY t."Title"
*/
    return await query.ToListAsync();
}

Tips to setup DbContext with the development in mind

• for development purposes, you may sometimes decide to use an in-memory database
• for all environments including production, you may set up HealthChecks, below shows how to add health checks for the DbContext

Also for the development environment good to have set o.EnableDetailedErrors(); and o.EnableSensitiveDataLogging();

public static IServiceCollection AddDatabase(this IServiceCollection services, IConfiguration configuration) {
    var options = configuration.GetSection(DatabaseOptions.SectionName).Get < DatabaseOptions > ();
    if (!options.Enable) return services;
    if (options.HealthChecks?.Enable ?? false) services.AddHealthChecks().AddDbContextCheck < AppDbContext > (tags: options.HealthChecks.Tags);
    if (options.UseInMemory) return services.AddDbContext < AppDbContext > (o => {
        o.UseInMemoryDatabase("TemplateServiceDb");
        if (!options.DetailedErrors) return;
        o.EnableDetailedErrors();
        o.EnableSensitiveDataLogging();
    });
    return services.AddDbContext < AppDbContext > (o => {
        o.UseNpgsql();
        //o.UseSqlServer();
        if (!options.DetailedErrors) return;
        o.EnableDetailedErrors();
        o.EnableSensitiveDataLogging();
    });
}

To manipulate with all these options you may consider adding configuration options

  "Database": {
    "Enable": true,
    "UseInMemory": false,
    "DetailedErrors": true,
    "ConnectionString": "Host=localhost;Port=5432;Database=TemplateServiceDb;Username=postgres;Password=password",
    "HealthChecks": {
      "Enable": true,
      "Tags": [ "ready" ]
    }
  },

public class DatabaseOptions
{
    internal const string SectionName = "Database";
    public bool Enable { get; set; }
    public bool UseInMemory { get; set; } = true;
    public bool DetailedErrors { get; set; }
    public string ConnectionString { get; set; } = null!;
    public HealthChecksOptions HealthChecks { get; set; } = null!;
}

public class HealthChecksOptions
{
    public bool Enable { get; init; } = true;
    public string[] Tags { get; init; } = Array.Empty<string>();
}

A working example can be found here.