Get SQL Code from an Entity Framework Core Iqueryable<T>

How do I view the SQL generated by the Entity Framework?

You can do the following:

IQueryable query = from x in appEntities
             where x.id == 32
             select x;

var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();

or in EF6:

var sql = ((System.Data.Entity.Core.Objects.ObjectQuery)query)
            .ToTraceString();

or in EF6.3+:

var sql = ((dynamic)flooringStoresProducts).Sql;

That will give you the SQL that was generated.

Retrieving SQL Generated by Entity Framework Core

You can turn on logging by implementing ILoggerProvider. See details in documentation.

You only need to register the logger with a single context instance. Once you have registered it, it will be used for all other instances of the context in the same AppDomain.

        using (var db = new BloggingContext())
        {
            var serviceProvider = db.GetInfrastructure<IServiceProvider>();
            var loggerFactory = serviceProvider.GetService<ILoggerFactory>();
            loggerFactory.AddProvider(new MyLoggerProvider());
        }

You can also define categories what you want to log.

    private static string[] _categories =
    {
        typeof(Microsoft.Data.Entity.Storage.Internal.RelationalCommandBuilderFactory).FullName,
        typeof(Microsoft.Data.Entity.Storage.Internal.SqlServerConnection).FullName
    };

How do you show underlying SQL query in EF Core?

Update for .NET 6: EF logging is enabled by default in development.

Just add "Microsoft.EntityFrameworkCore.Database.Command": "Information" to appsettings.Development.json so it's only logged in dev mode. You typically don't want to log every query in a production app.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=MyDB-2;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
     ,"Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  },
  "AllowedHosts": "*"
}

The SQL output shows in the command window or VS output window.

See SQL Logging of Entity Framework Core in the official docs. It's a bug that it doesn't log by default, see this GitHub issue.

---

Related Topics