How can I see the SQL generated by Entity Framework Core?

Asked

Viewed 367 times

2

I am using version 3.1 of EF Core, and I am trying to see the SQL generated from this code:

 var query = Db.Set<Area>()
    .Include(i => i.Extratos)
    .Include(i => i.Coordenadas)
    .Where(x => x.Ativo && x.FazendaId == fazendaId)
    .Select(x=> new Area {
        Coordenadas = x.Coordenadas.Where(x => x.Ativo),
        Extratos = x.AreaExtratos.Where(x => x.Ativo),
        Id = x.Id
    });

I added this code snippet to check which SQL EF Core generated.

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

But you’re making me this mistake:

Unable to cast Object of type 'Microsoft.EntityFrameworkCore.Query.Internal.Entityqueryable`1 XXX' to type 'System.Data.Entity.Core.Objects.Objectquery'.

I tried that solution too:

System.Diagnostics.Trace.WriteLine(query.ToString());

But it only appears the SQL in the console when a problem occurs.

c# sql .net entity-framework-core

  • If you are running in a real database, you can use SQL Profiler to view the query.

  • 1

    @Gabrielcoletta does not want to try to add an answer?

  • I could, but I don’t know if I’d add anything more to what’s already in the documentation. If you would like more details, just browse the documentation for SQL Profiler: https://docs.microsoft.com/pt-br/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver15

1 answer

2


In fact in Core the behavior is not the same as the normal EF and there is no ready form, this is one of its disadvantages in relation to the full, at least until now.

I found some solutions at Soen that show how to do it. It’s not a simple code, but it’s for you to create as a library for your use. I will reproduce here the solution that seemed most interesting:

using System.Linq;
using System.Reflection;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Query;

public static string ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class {
    var enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator();
    var relationalCommandCache = enumerator.Private("_relationalCommandCache");
    var selectExpression = relationalCommandCache.Private<SelectExpression>("_selectExpression");
    var factory = relationalCommandCache.Private<IQuerySqlGeneratorFactory>("_querySqlGeneratorFactory");
    var sqlGenerator = factory.Create();
    var command = sqlGenerator.GetCommand(selectExpression);
    return command.CommandText;
}

private static object Private(this object obj, string privateField) => obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
private static T Private<T>(this object obj, string privateField) => (T)obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);

I put in the Github for future reference.

Then you can apply that method to query that will do what the ToString() did in EF 6. I think even more correct the form of Core, only that this method should be present in the ready-to-use Entity Framework.

If you do not need in the code have tools to see this, if it is only analysis and not part of the application can be even more interesting.

Browser other questions tagged c# sql .net entity-framework-core

You are not signed in. Login or sign up in order to post.