Logging sql query parameter values

Question

I use nlog and following setting to log sql queries:

<logger name="Microsoft.EntityFrameworkCore.*" 
        minlevel="Trace" writeTo="sqllogfile" final="true" />

It works as expected, but doesn't log parameter values, the queries look like this:

2017-07-31 13:49:03.8836|  INFO  |Microsoft.EntityFrameworkCore.Internal.InterceptingLogger`1.Log|Executed DbCommand (8ms) [Parameters=[@__get_Item_0='?' (Size = 450)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [e].[Id], [e].[AccessFailedCount], [e].[ConcurrencyStamp], [e].[Email], [e].[EmailConfirmed], [e].[HeliosLoginId], [e].[LockoutEnabled], [e].[LockoutEnd], [e].[Name], [e].[NormalizedEmail], [e].[NormalizedUserName], [e].[PasswordHash], [e].[PhoneNumber], [e].[PhoneNumberConfirmed], [e].[SecurityStamp], [e].[TwoFactorEnabled], [e].[UserName]
FROM [AspNetUsers] AS [e]
WHERE [e].[Id] = @__get_Item_0 

Is it possible to show value of the @__get_Item_0 parameter ?

Thank you

Answer 1

By default, EF Core hides the data in the SQL which gets logged. Queries can have various data some of which can be sensitive information (like customer's social security number or credit card information). Therefore logs have ? instead of actual values.

Though at times, developer may want to see the values especially while debugging nasty bugs. To enable logging actual values, you need to configure your dbcontext.

You need to call EnableSensitiveDataLogging() on your DbContextOptionsBuilder. Since there are multiple ways to configure db context options, easiest way to get hold of it would be where you are configuring your provider with connection string (e.g. UseSqlServer) you can chain it right after it.

Example

optionsBuilder
    .UseSqlServer("connectionstring")
    .EnableSensitiveDataLogging();