Introduction

Usually when a query fails to present expect results or simply unexpected results with EF Core a developer will setup a breakpoint, run code and examine results in Microsoft Visual Studio local window. Viewing results in the local window does not help to figure out the issue while using proper logging can point to the issue which can lead to modifications in code or farther research to fix the problem or fail to see what is wrong. This is were logging can assist and rather than to the console or Visual Studio output window, logging to a file not only shows results but also keeps a record of what was attempted.

Learn how isolate EF Core logging to a file with sensitive information for development environment and stage and production environment without sensitive information to figure out queries that do not return expected results.

Source code

For those who would like an NuGet package

EntityCoreFileLogger NuGet package

Setup for logging to a file

Folder structure

In Visual Studio with solution explorer open, click on the project node of the project to add file logging. This opens the project file. At end of the file add the following (this is also done in provided source code).



<Target Name="MakeLogDir" AfterTargets="Build">
   <MakeDir Directories="$(OutDir)LogFiles\$([System.DateTime]::Now.ToString(yyyy-MM-dd))" />
</Target>

On first build of the project, a folder named LogFiles is created with a sub folder for today's date as follows.

Logging class

Next, create a folder named Classes and add the following class in the folder and change the namespace to match your project's namespace.



using System.Diagnostics;
using static System.DateTime;

namespace YourNamespaceGoesHere.Classes;

public class DbContextToFileLogger
{
    private readonly string _fileName = 
        Path.Combine(AppDomain.CurrentDomain.BaseDirectory, 
            "LogFiles", $"{Now.Year}-{Now.Month:D2}-{Now.Day:D2}", 
            "EF_Log.txt");

    public DbContextToFileLogger(string fileName)
    {
        _fileName = fileName;
    }

    public DbContextToFileLogger()
    {
        if (!File.Exists(_fileName))
        {
            using (StreamWriter w = File.AppendText(_fileName)) ;
        }
    }

    [DebuggerStepThrough]
    public void Log(string message)
    {

        if (!File.Exists(_fileName))
        {
            File.CreateText(_fileName).Close();
        }

        StreamWriter streamWriter = new(_fileName, true);
        streamWriter.WriteLine(message);
        streamWriter.WriteLine(new string('-', 40));
        streamWriter.Flush();
        streamWriter.Close();
    }
}

If the intent is to use this form of logging in more than one project, consider creating a separate class project for this class. Next set the class project as a local NuGet package source.

Setup DbContext using the above class

The following setup will log sensitive information to the log file if running the project inside of Visual Studio and will not log sensitive information outside of Visual Studio which equates to a production environment.

In your DbContext replace _OnConfiguring _method as follows for a console or similar project.



protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    if (Debugger.IsAttached)
    {
        optionsBuilder.UseSqlServer(ConnectionString())
            .EnableSensitiveDataLogging()
            .LogTo(new DbContextToFileLogger().Log,
                new[] { DbLoggerCategory.Database.Command.Name }, 
                Microsoft.Extensions.Logging.LogLevel.Information);
    }
    else
    {
        optionsBuilder.UseSqlServer(ConnectionString())
            .LogTo(new DbContextToFileLogger().Log,
                new[] { DbLoggerCategory.Database.Command.Name }, 
                Microsoft.Extensions.Logging.LogLevel.Information);
    }

}

For ASP.NET Core, add the following after var builder = WebApplication.CreateBuilder(args); in Program.cs




builder.Services.AddDbContextPool<Context>(options =>
    options.UseSqlServer(configuration.GetConnectionString("DefaultConnection"))
        .EnableSensitiveDataLogging()
        .LogTo(new DbContextToFileLogger().Log,
            new[] { DbLoggerCategory.Database.Command.Name },
            LogLevel.Information));

Side note: See the following project which uses the above to log to a file and SeriLog to log everything else not EF Core.

Example usage

Get all tracks for LedZeppelin IV Album



using var context = new Context();

int albumIdentifier = 129;

var LedZeppelin_IV_Album = context
    .Album
    .AsNoTracking()
    .AsSplitQuery()
    .Include(a => a.Track.OrderBy(track => track.Name))
    .Include(album => album.Artist)
    .TagWith("LedZeppelin_IV_Album")
    .FirstOrDefault(a => a.AlbumId == albumIdentifier);

The log entry in the log file.



      Executed DbCommand (39ms) [Parameters=[@__albumIdentifier_0='129'], CommandType='Text', CommandTimeout='30']
      -- LedZeppelin_IV_Album

      SELECT TOP(1) [a].[AlbumId], [a].[ArtistId], [a].[Title], [a0].[ArtistId], [a0].[Name]
      FROM [Album] AS [a]
      INNER JOIN [Artist] AS [a0] ON [a].[ArtistId] = [a0].[ArtistId]
      WHERE [a].[AlbumId] = @__albumIdentifier_0
      ORDER BY [a].[AlbumId], [a0].[ArtistId]

Note
Note there is an ORDER BY which was not specified, does not harm here but could be a problem in certain circumstances, the point here is setting a breakpoint and examining in Visual Studio local window may not stick out.

Note
The use of TagWith which can be useful if there are similar queries this makes it easy to have a specific query easy to locate in the log file.

Tip

While running code, open the log file in Microsoft Visual Code to see immediate results. This could also be done by linking a log file in Microsoft Visual Studio but each time the log file changes, Visual Studio will prompt to reload the file while Microsoft VS Code will not prompt to reload.

Summary

In this article has taught how to setup a method to log EF Core operations to a file were a new file is created for each day. This can assist in figuring out problems when a query produces incorrect results that can not be learned from viewing query results in Visual Studio's local or output window.

Code

Clone the following GitHub repository which is dedicated to EF Core and open the project EntityFrameworkChinookLoggingSample.

Requires

Microsoft Visual Studio 2022 or higher
.NET Core 8 Framework

Setup

Create a database named Chinook under SQLEXPRESS or under LocalDb or a named instance. If not under SQLEXPRESS alter the connection string in appsettings.json
Run the script under the scripts folder populate.sql