Logging to Database using Serilog in ASP.NET Core Web API

Logging to SQL Server Database using Serilog in ASP.NET Core Web API

In this article, I will discuss Logging into a SQL Server Database using Entity Framework Core with Serilog in an ASP.NET Core Web API Application with Examples. Please read our previous article discussing how to Implement Logging using Serilog in an ASP.NET Core Web API Application.

Logging to a SQL Server Database using Entity Framework Core with Serilog in ASP.NET Core Web API

Logging to the SQL Server database using Entity Framework Core (EF Core) with Serilog involves several steps. We need to set up a logging table in the database and configure Serilog to use the EF Core sink. Serilog doesn’t provide a direct EF Core sink, but we can use the Serilog.Sinks.MSSqlServer sinks to log directly to SQL Server, which is a common and efficient way to log from ASP.NET Core applications.

Step 1: Add NuGet Packages

We need to install the following Packages. The following packages are essential for ASP.NET Core applications that require robust logging mechanisms (with Serilog) and data access capabilities using SQL Server (with EF Core).

  1. Serilog.AspNetCore: Enhanced logging capabilities in an ASP.NET Core application.
  2. Serilog.Sinks.MSSqlServer: Allows Serilog to log messages to Microsoft SQL Server databases.
  3. Microsoft.EntityFrameworkCore.SqlServer: Provides Entity Framework Core support for SQL Server. 

We will work with the application that we created in our previous article. That means we have already installed the Serilog.AspNetCore Package. So, please install the following two packages using the Package Manager Console as follows:

Install-Package Serilog.Sinks.MSSqlServer
Install-Package Microsoft.EntityFrameworkCore.SqlServer

Once you have installed the above two packages, your Packages folder should contain the following packages:

Logging to a SQL Server Database using Entity Framework Core with Serilog in ASP.NET Core Web API

Step 2: Configure your Database and Context

Ensure you have an EF Core DbContext set up for your application. Please create a class file named EFCodeDbContext.cs within the Models folder and copy and paste the following code.

using Microsoft.EntityFrameworkCore;

namespace LoggingDemo.Models
{
    public class EFCodeDbContext : DbContext
    {
        public EFCodeDbContext(DbContextOptions<EFCodeDbContext> options)
            : base(options)
        {
        }

        // Your DB sets here
    }
}

Please ensure that your connection string is configured in appsettings.json and that the database EFCoreWebAPIDB exists on the database server.

{
  "ConnectionStrings": {
    "EFCoreDBConnection": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EFCoreWebAPIDB;Trusted_Connection=True;TrustServerCertificate=True;"
  },
  "Serilog": {
    // Serilog configuration will be updated in the next step
  }
}
Step 3: Configure Serilog in appsettings.json

Configure Serilog to log into SQL Server. Modify the appsettings.json to include the MSSqlServer sink configuration:

{
  "ConnectionStrings": {
    "EFCoreDBConnection": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EFCoreWebAPIDB;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;"
  },
  "Serilog": {
    "Using": [ "Serilog.Sinks.Console", "Serilog.Sinks.MSSqlServer" ],
    "MinimumLevel": {
      "Default": "Information",
      "Override": {
        "System": "Warning",
        "Microsoft": "Warning",
        "Microsoft.AspNetCore": "Warning"
      }
    },
    "WriteTo": [
      { "Name": "Console" },
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EFCoreWebAPIDB;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;",
          "sinkOptionsSection": {
            "tableName": "Logs",
            "autoCreateSqlTable": true
          }
        }
      }
    ]
  }
}

In this configuration, autoCreateSqlTable is set to true, instructing Serilog to automatically create the Logs table in your SQL Server database if it doesn’t exist. You can customize the table name and other options as needed.

Note: Please ensure the EFCoreWebAPIDB database exists where it will create the database Logs table.

Step 4: Modify Program.cs

Ensure Program.cs is configured to use Serilog, reading settings from appsettings.json. Please add the following code to the Program.cs class file.

var builder = WebApplication.CreateBuilder(args);
builder.Host.UseSerilog((context, services, configuration) => configuration
                .ReadFrom.Configuration(context.Configuration));

You have to make changes in the .csproj file of your project and need to add or modify the InvariantGlobalization attribute to false in PropertyGroup.

<InvariantGlobalization>false</InvariantGlobalization>

The <InvariantGlobalization>false</InvariantGlobalization> statement in an ASP.NET Core project file is used to control the globalization behavior of the application. Specifically, it configures how the .NET runtime handles globalization-related features, such as string comparisons, date and number formatting, and culture-specific operations.

By default, .NET Core uses a globalization mode that might not depend on the operating system’s locale settings. This mode is designed to improve performance and reduce the size of the deployment by not relying on the full set of globalization data and behaviors provided by the operating system.

When you set <InvariantGlobalization> to false in your project file, you are instructing the application to use the non-invariant culture-specific globalization data provided by the operating system. This allows your application to perform culture-aware operations based on the full set of locale-specific data available on the host system, rather than the invariant culture. This is important for applications that need to support locale-specific behaviors, such as formatting dates and numbers according to the user’s locale or performing string comparisons that are culture-aware.

In summary, setting <InvariantGlobalization> to false ensures that your ASP.NET Core application uses the full, culture-specific globalization capabilities of the .NET runtime, which is necessary for applications that need to provide localized experiences to users across different cultures and regions.

Step 5: Logging in Action

Now, when you log messages using Serilog in your application, they will be recorded in the SQL Server Logs table. For a better understanding, please modify the Test Controller as follows:

using Microsoft.AspNetCore.Mvc;

namespace LoggingDemo.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class TestController : ControllerBase
    {
        private readonly ILogger<TestController> _logger;

        public TestController(ILogger<TestController> logger)
        {
            _logger = logger;
        }

        [HttpGet]
        public IActionResult Get()
        {
            try
            {
                _logger.LogTrace("This is a Trace log, the most detailed information.");
                _logger.LogDebug("This is a Debug log, useful for debugging.");
                _logger.LogInformation("This is an Information log, general info about app flow.");
                _logger.LogWarning("This is a Warning log, indicating a potential issue.");
                _logger.LogCritical("This is a Critical log, indicating a serious failure in the application.");

                //Simulating an error situation
                int x = 10, y = 0;
                int z = x / y;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"This is an Error log, indicating a failure in the current operation.");
            }
           
            return Ok("Check your logs to see the different logging levels in action!");
        }
    }
}

Now, run the application. It should create the Logs database table with the following structure: Each column is designed to capture specific information about the log event, making it easier to query and analyze your logs.

Logging to SQL Server Database using Serilog in ASP.NET Core Web API

Understanding what each column represents can help you effectively work with your logged data. Here is an overview of the default columns created by the Serilog.Sinks.MSSqlServer sink:

  • Id: A unique identifier for each log entry. This is typically configured as an auto-incrementing integer.
  • Message: The text of the log message. This column stores the rendered log message, which may include interpolated values.
  • MessageTemplate: The template used to generate the log message. Serilog uses message templates to provide structured logging. The template contains placeholders that are filled in with values when the log message is rendered.
  • Level: The level of the log event (e.g., Trace, Debug, Information, Warning, Error, Fatal). This helps in filtering logs based on their severity.
  • TimeStamp: The date and time at which the log event occurred. This timestamp is usually stored in UTC to standardize the timing across different time zones.
  • Exception: Any exception details associated with the log event. If an exception is logged, this column contains the output of the exception’s ToString() method, which includes the exception message, stack trace, and any inner exceptions.
  • Properties: A JSON object containing the properties associated with the log event. This includes the properties derived from the message template and any additional properties added to the log context. This column allows Serilog to provide structured logging capabilities by storing additional data in a structured format.
Step 6: Verify Logging

Now access the API Endpoint (/api/Test), and check the Logs table in your SQL Server database. You should see the log entries there, as shown in the below image. Based on the Log Level setting, you will see the log messages:

Logging into a SQL Server Database using Entity Framework Core with Serilog in an ASP.NET Core Web API Application with Examples

How to Customize the Serilog Autogenerated Logs table in SQL Server

Customize the auto-generated logs table in SQL Server when using Serilog with Serilog.Sinks.MSSqlServer sink involves specifying custom column options or even adding additional columns to capture specific log properties. This customization lets you handle the logging table to better suit your application’s needs, enabling more effective log data storage and querying.

Define Your Custom Column Options

You can define custom column options directly in your code when configuring the Serilog logger or through the appsettings.json file for more flexibility and ease of changes without code recompilation.

To achieve column customization via appsettings.json, first, ensure you have the following package, which we have already installed in our previous article:

Install-Package Serilog.Settings.Configuration

Then, modify the appsettings.json file as follows:

{
  "ConnectionStrings": {
    "EFCoreDBConnection": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EFCoreWebAPIDB;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;"
  },
  "Serilog": {
    "Using": [ "Serilog.Sinks.Console", "Serilog.Sinks.MSSqlServer" ],
    "MinimumLevel": {
      "Default": "Information",
      "Override": {
        "System": "Warning",
        "Microsoft": "Warning",
        "Microsoft.AspNetCore": "Warning"
      }
    },
    "WriteTo": [
      { "Name": "Console" },
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EFCoreWebAPIDB;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;",
          "sinkOptionsSection": {
            "tableName": "Logs",
            "autoCreateSqlTable": true
          },
          "columnOptionsSection": {
            "additionalColumns": [
              {
                "ColumnName": "UniqueId",
                "DataType": "nvarchar",
                "DataLength": 50
              }
            ]
          }
        }
      }
    ]
  }
}

Note: When AutoCreateSqlTable is set to true, Serilog will attempt to create the Logs table upon startup. If you’re adding custom columns after the table has already been created, you might need to add these columns manually to the database or drop and recreate the table if it’s safe to do so (e.g., in a development environment).

Ensure Properties Are Logged

To ensure the custom properties (like UserId) are logged, you must include them in your log events. You can do this using the ForContext method to add context to your logs or by including the property in your log message templates.

using Microsoft.AspNetCore.Mvc;
using Serilog;

namespace LoggingDemo.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class TestController : ControllerBase
    {
        private readonly ILogger<TestController> _logger;

        public TestController(ILogger<TestController> logger)
        {
            _logger = logger;
        }

        [HttpGet]
        public IActionResult Get()
        {
            string UniqueId = Guid.NewGuid().ToString();
            try
            {
                _logger.LogInformation("{UniqueId} This is an Information log, general info about app flow.", UniqueId);
                _logger.LogWarning("This is a Warning log, indicating a potential issue.{UniqueId}", UniqueId);
                _logger.LogCritical("This is a Critical log, indicating a serious failure in the application.");

                //Another Approach
                Log.ForContext("UniqueId", UniqueId).Information("Processing Request Information");
                Log.ForContext("UniqueId", UniqueId).Warning("Processing Request Warning");

                //Simulating an error situation
                int x = 10, y = 0;
                int z = x / y;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "{UniqueId} This is an Error log, indicating a failure in the current operation.", UniqueId);
                Log.ForContext("UniqueId", UniqueId).Error("Processing Request Error");
            }
           
            return Ok("Check your logs to see the different logging levels in action!");
        }
    }
}

Note: Before running and testing the Table customization, first delete the database table that has already been created in the database. Once you delete the Logs database table, run the application, and access the API Endpoint (/api/Test), you should see the changes as expected.

How to Customize the Serilog Autogenerated Logs table in SQL Server

Structured Logging into the Database table:

Let us understand how to implement Structured logging with Serilog into the SQL Server database. This approach makes logs more queryable and allows for more meaningful analysis of log data. Using the Serilog.Sinks.MSSqlServer sink, you can configure Serilog to log structured data into an SQL Server database.

Configure Serilog in appsettings.json

In your appsettings.json, configure Serilog to use the MSSqlServer sink and specify your database connection string and the table name. Also, define the column options to store structured data, typically in a column that can hold JSON-formatted data.

{
  "ConnectionStrings": {
    "EFCoreDBConnection": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EFCoreWebAPIDB;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;"
  },
  "Serilog": {
    "Using": [ "Serilog.Sinks.Console", "Serilog.Sinks.MSSqlServer" ],
    "MinimumLevel": {
      "Default": "Information",
      "Override": {
        "System": "Warning",
        "Microsoft": "Warning",
        "Microsoft.AspNetCore": "Warning"
      }
    },
    "WriteTo": [
      { "Name": "Console" },
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EFCoreWebAPIDB;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;",
          "sinkOptionsSection": {
            "tableName": "Logs",
            "autoCreateSqlTable": true
          },
          "columnOptionsSection": {
            "addStandardColumns": [ "LogEvent" ],
            "removeStandardColumns": [ "Properties" ],
            "additionalColumns": [
              {
                "ColumnName": "Properties",
                "DataType": "nvarchar(max)",
                "AllowNull": true,
                "DataLength": -1
              },
              {
                "ColumnName": "UniqueId",
                "DataType": "nvarchar",
                "DataLength": 50
              }
            ]
          }
        }
      }
    ]
  }
}

In this configuration:

  • The LogEvent column is added to store the complete log event data.
  • The original Properties column (typically used for structured data) is removed and redefined to ensure it can store large JSON data (nvarchar(max)).
  • The UniqueId column stores a Request’s unique ID, which we can use to fetch all the logs of a single request.
Logging Structured Data

We are going to use the book model class that we created in our previous article. If you have not created it yet, please create the following Book model class within the Models folder:

namespace LoggingDemo.Models
{
    public class Book
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Author { get; set; }
        public int YearPublished { get; set; }
    }
}

Next, modify the BooksController as follows:

using LoggingDemo.Models;
using Microsoft.AspNetCore.Mvc;
using Serilog;

namespace LoggingDemo.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class BooksController : ControllerBase
    {
        private static readonly List<Book> Books = new List<Book>()
        {
            new Book(){Id = 1001, Title = "ASP.NET Core", Author = "Pranaya", YearPublished = 2019},
            new Book(){Id = 1001, Title = "SQL Server", Author = "Pranaya", YearPublished = 2022}
        };
        private readonly ILogger<BooksController> _logger;

        public BooksController(ILogger<BooksController> logger)
        {
            _logger = logger;
        }

        [HttpPost]
        public IActionResult AddBook([FromBody] Book book)
        {
            Books.Add(book);

            string UniqueId = Guid.NewGuid().ToString();

            //Structured Logging, i.e., JSON Format
            _logger.LogInformation("{UniqueId}, Added a new book {@Book}", UniqueId, book);

            //Another Approach
            Log.ForContext("UniqueId", UniqueId).Information("Added a new book {@Book}", book);
            return Ok();
        }

        [HttpGet]
        public IActionResult GetBooks()
        {
            string UniqueId = Guid.NewGuid().ToString();

            //Structured Logging, i.e., JSON Format
            _logger.LogInformation("Retrieved all books. Books: {@Books}", Books);

            //Another Approach
            Log.ForContext("UniqueId", UniqueId).Information("Retrieved all books. Books: {@Books}", Books);
            return Ok(Books);
        }
    }
}

Note: Before running and testing the table with structured data, first delete the database table that has already been created in the database. Once you delete the Logs database table, then run the application and make a POST request to https://localhost:7223/api/books with the following JSON body:

{
    "id": 1003,
    "title": "C#.NET",
    "author": "Pranaya",
    "yearPublished": 2023
}

Now, you can verify the database table, and you should see the logs as expected.

When Should We Log to a SQL Server Database using Serilog in ASP.NET Core Web API?

Choosing when to log into a SQL Server database involves considering several factors, including the nature of the data, performance implications, and the ease of querying logged data. Here’s a guide on when it might be appropriate to log to a SQL Server database using Serilog in an ASP.NET Core Web API:

  • Error and Exception Logging: Log errors and exceptions that occur within your application. This helps in diagnosing issues that affect the application’s stability or correctness. SQL Server can be a good choice for storing such logs because it allows for complex queries to analyze error patterns or recurring issues.
  • Audit Trails: For applications that require auditing user actions for compliance or security reasons, logging these events to SQL Server can be beneficial. SQL Server provides the necessary features to store, manage, and query large volumes of audit logs effectively.
  • Performance Metrics: Logging performance metrics, such as response times and resource usage, can help identify bottlenecks or areas for optimization in your application. While SQL Server can store this information, consider the volume and frequency of the data to avoid impacting database performance.
  • Business Intelligence: Logging events related to how users interact with your application can provide valuable insights for business intelligence purposes. SQL Server’s querying capabilities make it easier to analyze this data for trends and patterns.
Considerations for Logging to SQL Server
  • Debugging Information: In a development or staging environment, detailed debug logs can be invaluable for diagnosing problems. However, be cautious when logging trace and debug information in a production environment to an SQL server database, as it can quickly grow in size and impact performance.
  • Log Rotation and Retention: SQL Server databases can grow significantly due to logging. Implement log rotation and retention policies to manage the size of the database.
  • Security and Access Control: Ensure that logs stored in SQL Server, especially those containing sensitive information, are protected with appropriate security measures.

In the next article, I will discuss how to implement logging using nLog in the ASP.NET Core Web API Application, along with examples. In this article, I explain how to log into an SQL Server Database using Entity Framework Core with Serilog in an ASP.NET Core Web API Application with examples. I hope you enjoy this article, Logging to a SQL Server Database using Entity Framework Core with Serilog in ASP.NET Core Web API.

Leave a Reply

Your email address will not be published. Required fields are marked *