Using Serilog.Sinks.MsSqlServer with Entity Framework Core

Question

I'm using Entity Framework Core and code first. And I'd like to start logging to my database with Serilog.Sinks.MsSqlServer.

Since I may want to query the logs, I'm thinking about creating the Logs table via EF Core, and then telling Serilog not to create the table.

Does anyone have experience with creating the Logs table with Entity Framework? I haven't been able to find any examples of this. I don't know if Serilog.Sinks.MsSqlServer has tools for querying the logs. Does it make sense to create the table and then to query it myself?

Answer 1

First of all it can make sense to create the table with EF Core to query it. I have done that before to query some logs (e.g in an admin dashboard) and its quite easy to do so:

1. Log Entity Definition:

public class LogEntry
{
    public int Id { get; set; }
    public string Message { get; set; }
    public string MessageTemplate { get; set; }
    public string Level { get; set; }
    public DateTime TimeStamp { get; set; }
    public string? Exception { get; set; }
    public string? Properties { get; set; }
    public string? LogEvent { get; set; }
}

This class has all the properties the Serilog sink needs to write your logs to the database.

2. DbContext configuration:

public class ApplicationDbContext : DbContext
{
    public DbSet<LogEntry> Logs { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) :base(options)
    {
        
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(Program).Assembly);
    }
}

I always use ApplyConfigurationsFromAssembly() to configure my database entities so I also need to create a configuration class for the LogEntry that looks like this:

public class LogEntryConfiguration : IEntityTypeConfiguration<LogEntry>
{
    public void Configure(EntityTypeBuilder<LogEntry> builder)
    {
        builder.ToTable("Logs");
        
        builder.HasKey(p => p.Id);

        builder.Property(p => p.Exception)
            .IsRequired(false);
        
        builder.Property(p => p.Properties)
            .IsRequired(false);
        
        builder.Property(p => p.LogEvent)
            .IsRequired(false);
        
        builder.Property(p => p.TimeStamp)
            .HasColumnType("datetime");
        
        builder.Property(p => p.Level)
            .HasMaxLength(16);
    }
}

Of course you do not have to do the configuration like this but the properties need to be configured like this.

3. Configure the Serilog sink:

var logger = new LoggerConfiguration()
    .ReadFrom.Configuration(builder.Configuration)
    .Enrich.FromLogContext()
    .WriteTo.MSSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"),
        new MSSqlServerSinkOptions()
    {
        AutoCreateSqlDatabase = false,
        AutoCreateSqlTable = false,
        TableName = "Logs"
    })
    .CreateLogger();
builder.Logging.ClearProviders();
builder.Logging.AddSerilog(logger);

For this example I configured Serilog from code but it can also be done in the appsettings.json as described in the documentation here. The important things here are AutoCreateSqlDatabase = false, AutoCreateSqlTable = false so the sink does not try to create the Logs table by itself.

After that the sink writes to our created Logs table and you can query the table with EF Core like you would do with an other table.