How To Access SQL Generated By Entity Framework Core 3

July 28, 2020
 569
How To Access SQL Generated By Entity Framework Core 3
[DISPLAY_ULTIMATE_SOCIAL_ICONS] Entity Framework Core (EF) converts expressions into SQL at runtime. In earlier versions, it was straight forward to get the SQL. In Entity Framework Core 3, you must access the SQL using ILogger. This article explains how to access the SQL generated and gives some example code to access the output of queries made behind the scenes. This article works with Entity Framework Core 3. Note: Microsoft is about to release Entity Framework Core 5 soon. This version will have an easier method to get at the SQL. This is the interface method. Many developers may feel uncomfortable if they do not know what SQL EF executes behind the scenes. There's a good reason for this! Expressions may not map on to SQL very easily. You may end up executing SQL that doesn't take advantage of indexes, or the expression may end up filtering records after the data was selected from the database. In older versions of EF you could use ToTraceString()but this no longer exists in EF Core 6. There may even be other reasons to access the SQL. Perhaps your want to convert EF expressions to SQL. This is all possible in EF Core. Grab the full sample here. The Basics The key to making entity framework log SQL queries is to provide it with a logging factory: optionsBuilder.UseLoggerFactory(_loggerFactory); And, the factory must have a filter like so: var loggerFactory = LoggerFactory.Create(builder => { builder .AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information); }); That's it. If you add console logging, SQL will be logged to the console when the SQL executes. var loggerFactory = LoggerFactory.Create(builder => { builder .AddConsole((options) => { }) .AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information); }); This is an example query: using (var ordersDbContext = new OrdersDbContext(loggerFactory)) { var orderLines = ordersDbContext.OrderLines.Where(o => o.Id == Guid.Empty).ToList(); orderLines = ordersDbContext.OrderLines.ToList(); } This is the console output: info: Microsoft.EntityFrameworkCore.Database.Command[20101]Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND rootpage IS NOT NULL;info: Microsoft.EntityFrameworkCore.Database.Command[20101]Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']SELECT o.Id, o.Count, o.ItemFROM OrderLines AS oWHERE o.Id = '00000000-0000-0000-0000-000000000000'info: Microsoft.EntityFrameworkCore.Database.Command[20101]Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']SELECT o.Id, o.Count, o.ItemFROM OrderLines AS o Incidentally, it looks as though the where clause is not parameterized. Is this a security hole in Entity Framework Core for SQLite? Here is some further documentation on this approach. Getting More Detail You may need more detail. Or, you may want to use EF to generate SQL for some reason. You can achieve that with this code. This is an implementation of ILogger that will allow you to hook into an action when SQL runs. public class EntityFrameworkSqlLogger : ILogger { #region Fields Action _logMessage; #endregion #region Constructor public EntityFrameworkSqlLogger(Action logMessage) { _logMessage = logMessage; } #endregion #region Implementation public IDisposable BeginScope(TState state) { return default; } public bool IsEnabled(LogLevel logLevel) { return true; } public void Log(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func formatter) { if (eventId.Id != 20101) { //Filter messages that aren't relevant. //There may be other types of messages that are relevant for other database platforms... return; } if (state is IReadOnlyList> keyValuePairList) { var entityFrameworkSqlLogMessage = new EntityFrameworkSqlLogMessage ( eventId, (string)keyValuePairList.FirstOrDefault(k => k.Key == commandText).Value, ...

Hot Vacancies

DevOps Engineer

Private company, Azure

Our team is urgently looking for a DevOps Engineer for a direct 6-month contract. The client is based in the Netherlands, and the domain is healthcare. The primary task is to assist with the migration from a local data center to Azure.

.NET Developer

American startup, .NET

A developer is needed for an American startup that manages the operation and maintenance of residential complexes. This is a new project from scratch with a temporary integration of the old system (Web Forms, no code access).

.NET Backend Developer

Field Complete, .NET

Field Complete is a team of passionate, young & fun-loving professionals looking to change the uneffective way that Servicing Industry works on US markets. Field Complete is growing really fast. We are looking for a Back End Developer to build a top-level modern API, ready for high load. Strong expertise with:

Senior Xamarin Developer

DraftKings, Mobile

You will join a mobile team which is working on two very exciting projects, Sportsbook and Casino. The apps are used by users in the US, where we are working on the regulated markets. We are releasing apps every two weeks. Our apps are generating almost 75% of the company revenue and the user base is growing daily. Technical stack on the project: Xamarin.Forms, MVVM with DI, NewRelic, Azure + App Center etc. Switching to .Net MAUI in the nearest 2-3 months.

Senior .NET Engineer

DraftKings, .NET

You will be working in a large US-oriented company that puts as a priority: security, performance, and stability. The candidate will work on pushing a huge number of changes (several thousand per sec) to several thousand clients in a near real-time manner.