Integrating Serilog with Entity Framework and custom Entities

A short article outlining a basic Serilog setup together with Entity Framework Core using custom Entities.

This article assumes a basic knowledge of Entity Framework Core and the packages used in this article:

.Net 8
EntityFrameworkCore: 8.0.1
Serilog: 3.1.1
Serilog.Sinks.MSSqlServer: 6.5.1

To start we first need to figure out our goal; for this article, I want to be able to log events together with a username. This could be done in order to filter and search the logs or just save the information for later use. The custom entity we are going to create will follow Serilogs LogEvent.cs with some custom fields:

public class LogEntity 
{
    [Key]
    public int Id { get; set; }
    public string Message{ get; set; }
    public string? UserName{ get; set; }
}

So this is our goal, get the logger to create a database entry with an extra UserName column.

Following the standard Serilog setup we use the LoggerConfigurationto setup some basics. We start by importing configuration from appsettings.json that is saved as configuration , we also tell it to use a context which we will get back to later. Then we need to configure our database connection; depending on your setup this might look different, the only important part for this article here is the GetColumnOptions() which we are also going to create.

var logger = new LoggerConfiguration()
    .ReadFrom.Configuration(configuration)
    .Enrich.FromLogContext()
    .WriteTo.MSSqlServer(
        connectionString: configuration.GetConnectionString("DefaultConnection"),
        sinkOptions: new MSSqlServerSinkOptions
        {
            TableName = "Logs",
            SchemaName = "dbo",
            AutoCreateSqlDatabase = false,
            AutoCreateSqlTable = true,
        }, 
        columnOptions: GetColumnOptions() // created in next code block
    ).CreateLogger();

builder.Logging.ClearProviders();
builder.Logging.AddSerilog(logger);
builder.Services.AddSingleton<Serilog.ILogger>(logger);

Using the ColumnOptions we can modify the standard column setup for Serilog and also add our own columns. The only thing we need to do is to add a list of SqlColumn to the AdditionalColumns property on ColumnOptions.

// can be created in Program.cs or any other file
ColumnOptions GetColumnOptions()
{
    var columnOptions = new ColumnOptions();

    columnOptions.AdditionalColumns = new List<SqlColumn>
    {
        new SqlColumn { 
            DataType   = SqlDbType.VarChar,  
            ColumnName = "UserName",     
            DataLength = 250, 
            AllowNull  = true },

        // add more columns here if needed
    };
    return columnOptions;
}

For this to work with Entity Framework and Migrations we need to tell Entity Framework not to create the table, because it will be created by Serilog instead. This is an easy fix by just using the ExcludeFromMigrations function on the Table during the onModelCreating in the database context where your database creation is defined.

// DatabaseContext.cs
public class DatabaseContext : DbContext
{
  public DbSet<LogEntity> Logs { get; set; }

  protected override void OnModelCreating(ModelBuilder builder)
  {
    base.OnModelCreating(builder);

    builder.Entity<LogEntity>()
      .ToTable("Logs", table => table.ExcludeFromMigrations());
  }
}

Usage

To populate this new column we have a couple of options, we can either:

1. Create a middleware that sets the username in the log context that would be used for the entirety of the request.
2. We can set the username in a using scope
3. We can directly insert it into a log function call.

Using ASP.NET Core Identity or any other identity framework that gives out the users context is probably the easiest and could look something like this:

// LogContextMiddleware.cs
public class LogContextMiddleware(
  Serilog.ILogger logger, 
  RequestDelegate next)
{
    public async Task Invoke(
      HttpContext context, 
      UserManager userManager)
    {
        var user = await userManager.GetUserAsync(context.User);
        
        using (LogContext.PushProperty("Method", context.Request.Method))
        using (LogContext.PushProperty("UserName", user?.Email ?? "Anonymous"))
        using (LogContext.PushProperty("UserId", user?.Id ?? "Anonymous"))
        {
            await next(context);
        }
    }
}

// program.cs
app.UseMiddleware<LogContextMiddleware>();

Using a middleware like this also allows us to set other properties we want to store for later use, such as the method used in the HTTPContext.Request or even the UserId.

Overriding context

If you however want to override the properties when you log something we can easily do this with the ForContext method on the Logger object. For example setting the UserName to “System” when running scheduled jobs we can easily just set the values manually.

logger.ForContext("UserName", "System")
      .ForContext("Item", "Something")
      .Information("{UserName:l} created {Item}");

Keeping track of a standardized naming scheme for these variables can be tiresome, so an alternative is to create extension methods for the ILogger that add a certain context:

public static class LogExtensions
{
    public static ILogger AddItem(this ILogger logger, string itemName)
    {
        return logger.ForContext("Item", itemName);
    }
}

// usage
logger.AddItem("something").Information("{UserName:l} created {Item}")

Log formatting

We have several formatting options but let's start with the Serilog Properties column; It is by default a XML object which contains every Property, Context, and Rendering option we set during logging. If we instead want this as a JSON object we can replace the Properties with LogEvents in the GetColumnOptions as such:

columnOptions.Store.Add(StandardColumn.LogEvent);
columnOptions.Store.Remove(StandardColumn.Properties);

When Serilog creates the Message column from the MessageTemplate we provide in the logger.Information() it puts " around our text, to stop this we simply add :l behind our property name:

logger.Information("{UserName} logged in");
// "John Doe" logged in

logger.Information("{UserName:l} logged in");
// John Doe logged in

Minimum Level

We can also turn off Microsofts inbuilt information logging, which tends to log a lot of unrelated stuff, and in my usecase we are only interested in the error messages. This can be done together with other configurations in the appsettings.json file.

{
  "Serilog": {
    "MinimumLevel": {
      "Default": "Information",
      "Override": {
        "Microsoft": "Error",
      }
    }
  },
  "ConnectionStrings": {
      "DefaultConnection": "..."
  }
}

Timezone

To configure the timezone for logging you can either implement the CompactJsonFormatter and change this line to match your timezone

// default
output.Write(logEvent.Timestamp.LocalDateTime.ToString("O"));

// UTC time
output.Write(logEvent.Timestamp.UtcDateTime.ToString("O"));

or change the WEBSITE_TIME_ZONE app setting in the app or in Azure like this:

Reading logs

With this additional data we can easily create an overview of our logs in the frontend for administrators. In our repository we simply return logs from the DatabaseContext as such, we can also order it by newest to oldest and select an amount of logs.

public class LogRepository(DatabaseContext context) : ILogRepository
{
  public<IEnumerable<LogEntity>> GetLogs(int amount)
  {
    return context.Logs
      .OrderByDescending(log => log.TimeStamp)
      .Take(amount)
      .ToList();
  }
}

Frontend created with React and MUI Core

With this basic setup I am getting everything the users are doing, and some error messages that explain what's going wrong. Since we are saving the UserId we can easily add an option to filter on all logs related to that user, and this is easily expandable to other data sources.