dotnet
Option to enable `DECLARE @x AS type = value` style logging of parameter values
What problem are you trying to solve?
I want to go to my log of what SQL EFC has generated and copy it, paste it into SSMS and run it to assess the query plan etc
EFC's logging format could make my life a lot easier in this regard
Describe the solution you'd like
To enable an option that makes parameter value logging be an SQL flavoured declaration of the paremeter
For example at the moment the code might look like:
context.Blogs.Where(b => b.Id == 123 && b.Other == "Thing").ToList();
The log might look like:
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (11ms)
[Parameters=[@__id_0=123, @__other_1 = "Thing"], CommandType='Text', CommandTimeout='30]
SELECT * FROM Blogs WHERE Id = @__id_0 AND Other = @__other_1
If EFC could log parameters like this (e.g. for SQLS) it would be marvellous:
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (11ms)
[CommandType='Text', CommandTimeout='30, Parameters=[DECLARE @__id_0 INT = 123; DECLARE @__other_1 NVARCHAR(5) = N'Thing'; ]
SELECT * FROM Blogs WHERE Id = @__id_0 AND Other = @__other_1
I'd only need to strip out one char to have a fully functioning SQL.. It does have a small extra bit of info in the datatype, which the existing logging doesn't have (other than a nod to use of ' ' for strings etc) but I suppose EF might know that from the DbParameter object, if it's available when the log is formed?
Yep, that seems mostly duplicated - I didn't find that dupe when I searched, thanks for turning it up. I did think about the issue it mentions, which is (if I understand it correctly) that some people might be suggesting to log statements like:
DECLARE @__id_0 INT = 123;
DECLARE @__other_1 NVARCHAR(5) = N'Thing'; ]
SELECT * FROM Blogs WHERE Id = @__id_0 AND Other = @__other_1
Though this SQL is never actually executed by EFC, which bothered me some; my suggestion to tweak the Parameters=[ ...] list to add datatypes and DECLARE was to address that - we're already used to seeing the param list there when enable sensitive logging is on, to tweak the format a little doesn't lead me (personally) to assume that EFC must send DECLARE statements to the server
Would it be difficult to expose the class that formats the log message so it could be replaced by me/I could write my own custom formatter? This would also help me in situations where I use multiple different contexts to hit different DBs with similar tables; I can potentially more easily see which DB was executed against if I tweak the log message to include some create-time constant that I vary when I associate the formatter with EF
Perhaps I can already do something like this with interceptors
We discussed this and we believe that the combination of consistent logging across providers together with executable SQL in the debug view/strings is a good balance.
@ChairmanMawh You can always create a database interceptor and generate your own logging.