How to view generated sql command of a query

[gzak]

I could have sworn I was able to do query.ToString() to see the generated SQL query, but that's not working in EF7 on a basic Where query. How do you do it? Should be a super quick question...

I'm using the standard (localdb)\MSSQLLocalDB setup.

[Borlay]

I don't know about current query sql but if you need just log sql queries, i have extension like this:

public static class DbContextExtensions
{
    public static void LogSql(this DbContext context, Action<string> logAction)
    {
        var serviceProvider = context.GetInfrastructure<IServiceProvider>();
        var loggerFactory = serviceProvider.GetService<ILoggerFactory>();
        loggerFactory.AddProvider(new LoggerProvider(logAction));
    }
}

public class LoggerProvider : ILoggerProvider
{
    private readonly Action<string> logAction;

    public LoggerProvider(Action<string> logAction)
    {
        if (logAction == null)
            throw new ArgumentNullException("logAction");

        this.logAction = logAction;
    }

    public ILogger CreateLogger(string categoryName)
    {
        return new Logger(logAction);
    }

    public void Dispose()
    {
    }
}

public class Logger : ILogger
{
    private readonly Action<string> logAction;

    public Logger(Action<string> logAction)
    {
        if (logAction == null)
            throw new ArgumentNullException("logAction");

        this.logAction = logAction;
    }

    public IDisposable BeginScopeImpl(object state)
    {
        return null;
    }

    public bool IsEnabled(LogLevel logLevel)
    {
        return true;

    }

    public void Log(LogLevel logLevel, int eventId, object state, Exception exception, Func<object, Exception, string> formatter)
    {
        var message = formatter(state, exception);
        logAction(message);
    }
}

you need some namespaces:
Microsoft.Data.Entity.Infrastructure;
Microsoft.Extensions.DependencyInjection;
Microsoft.Extensions.Logging;

[rowanmiller]

More info on logging here; http://docs.efproject.net/en/latest/miscellaneous/logging.html (similar to what @Loat described)

At this stage EF Core doesn't have an equivalent to ToString() from EF6.

[Ponant]

Hi Rowan,
I want to use the logger to show me only what SQL is sent to the db from a linq/ef query or creation of rows etc.
I tried (among other things) adding
typeof(Microsoft.EntityFrameworkCore.Storage.DbCommandLogData).FullName
in _catgeories in MyFilteredLoggerProvider but nothing is returned (it even tells me the DbCommandLogData is obsolete, too)

[rowanmiller]

Here is something that works with 1.1.0

public class SqlLoggerProvider : ILoggerProvider
    {
        public ILogger CreateLogger(string categoryName)
        {
            if (categoryName == typeof(Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory).FullName)
            {
                return new SqlLogger(categoryName);
            }

            return new NullLogger();
        }

        public void Dispose()
        { }

        private class SqlLogger : ILogger
        {
            private string _test;

            public SqlLogger(string test)
            {
                _test = test;
            }

            public bool IsEnabled(LogLevel logLevel)
            {
                return true;
            }

            public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
            {
                if (eventId.Id == (int)RelationalEventId.ExecutedCommand)
                {
                    var data = state as IEnumerable<KeyValuePair<string, object>>;
                    if (data != null)
                    {
                        var commandText = data.Single(p => p.Key == "CommandText").Value;
                        Console.WriteLine(commandText);
                    }
                }
            }

            public IDisposable BeginScope<TState>(TState state)
            {
                return null;
            }
        }

        private class NullLogger : ILogger
        {
            public bool IsEnabled(LogLevel logLevel)
            {
                return false;
            }

            public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
            { }

            public IDisposable BeginScope<TState>(TState state)
            {
                return null;
            }
        }
    }

[rowanmiller]

I've opened #7217 to improve this, as it has a number of issues.

[Ponant]

I was about to post a solution for my linq to sql question. The following made it in _categories
typeof(Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory).FullName,
I agree with you, it took me some time to decrypt the debugger. I will try your new improvements, thanks a lot and yes I think it is great you re-opened it.

[juliencousineau]

Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory

Doesnt exist ...

[ErikEJ]

@juliencousineau Of course it does... What EF Core version are you using?

[juliencousineau]

My bad, I didn't add the package EntityFramework.SqlServer

Thanks !

[davidbaxterbrowne]

BTW, I was annoyed by EF Core logging, and wrote a little helper to get back simple EF6-style logging. https://blogs.msdn.microsoft.com/dbrowne/2017/09/22/simple-ef6-style-logging-for-ef-core/

[marcobarbero]

From:
https://expertcodeblog.wordpress.com/2018/01/09/entity-framewor-core-2-0-how-to-view-the-linq-sql-parsed-command-on-asp-net-core-2-0/

You can use the Asp Net Core 2.0 logger to log the SQL command in the output window.
I use it to log Linq command executed on a repository class called from a controller class.

In your Startup.cs of the startup project:

// This method gets called by the runtime.
// Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, 
    IHostingEnvironment env,
    ILoggerFactory loggerFactory, 
    IServiceProvider serviceProvider)
{
    // To setup up settings of the "Logging" element specified 
    // in the appsettings.json
    // Adds a console logger named 'Console' to the factory
    loggerFactory.AddConsole();
    // Adds a debug logger that is enabled for 
    // Microsoft.Extensions.Logging.LogLevel.Information or higher.
    loggerFactory.AddDebug();
    // The other configurations settings // ...
}

In your controller class:

public class SampleController : Controller
{
   private readonly ISampleRepository _sampleRepository;
   private readonly ILogger _logger;
   public SampleController(ISampleRepository sampleRepository,
     ILogger<AllarmiController> logger)
   { _sampleRepository = sampleRepository;
     _logger = logger;
   }
   [HttpGet]
   public async Task<IActionResult> SampleMethod()
   {
       // The method GetDataWithLinqToSQL of ISampleRepository 
       // use Linq to SQL.
       // When you call it, you can se the SQL command parsed 
       // in the output window.
       var sample = await _sampleRepository.GetDataWithLinqToSQL();
       return Ok(sample);
   }
}

I hope this is wath you need.