Skip to content

Instantly share code, notes, and snippets.

@rionmonster
Last active November 9, 2022 16:39
Show Gist options
  • Star 59 You must be signed in to star a gist
  • Fork 13 You must be signed in to fork a gist
  • Save rionmonster/2c59f449e67edf8cd6164e9fe66c545a to your computer and use it in GitHub Desktop.
Save rionmonster/2c59f449e67edf8cd6164e9fe66c545a to your computer and use it in GitHub Desktop.
Resolve the SQL being executed behind the scenes in Entity Framework Core
public static class IQueryableExtensions
{
private static readonly TypeInfo QueryCompilerTypeInfo = typeof(QueryCompiler).GetTypeInfo();
private static readonly FieldInfo QueryCompilerField = typeof(EntityQueryProvider).GetTypeInfo().DeclaredFields.First(x => x.Name == "_queryCompiler");
private static readonly PropertyInfo NodeTypeProviderField = QueryCompilerTypeInfo.DeclaredProperties.Single(x => x.Name == "NodeTypeProvider");
private static readonly MethodInfo CreateQueryParserMethod = QueryCompilerTypeInfo.DeclaredMethods.First(x => x.Name == "CreateQueryParser");
private static readonly FieldInfo DataBaseField = QueryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");
private static readonly FieldInfo QueryCompilationContextFactoryField = typeof(Database).GetTypeInfo().DeclaredFields.Single(x => x.Name == "_queryCompilationContextFactory");
public static string ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class
{
if (!(query is EntityQueryable<TEntity>) && !(query is InternalDbSet<TEntity>))
{
throw new ArgumentException("Invalid query");
}
var queryCompiler = (IQueryCompiler)QueryCompilerField.GetValue(query.Provider);
var nodeTypeProvider = (INodeTypeProvider)NodeTypeProviderField.GetValue(queryCompiler);
var parser = (IQueryParser)CreateQueryParserMethod.Invoke(queryCompiler, new object[] { nodeTypeProvider });
var queryModel = parser.GetParsedQuery(query.Expression);
var database = DataBaseField.GetValue(queryCompiler);
var queryCompilationContextFactory = (IQueryCompilationContextFactory)QueryCompilationContextFactoryField.GetValue(database);
var queryCompilationContext = queryCompilationContextFactory.Create(false);
var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
var sql = modelVisitor.Queries.First().ToString();
return sql;
}
}
@halllo
Copy link

halllo commented Jan 7, 2020

@chrsas The problem with IN restrictions in EF core 3.1 is that it assumes a constant like 5 or "RosiOli", not a parameter: https://github.com/aspnet/EntityFrameworkCore/blob/2e8ef3516d2bed2f934eea6e2cb92f7a9ff40ab3/src/EFCore.Relational/Query/QuerySqlGenerator.cs#L623

You could try to inherit from QuerySqlGenerator and provide your own code generator where you support parameters. I tried it and abandoned it. You would need to create a new SqlParameter for every element in your names collection. This looks like something EF Core 3.2 could add.

@Rubenisme
Copy link

Rubenisme commented May 27, 2020

@chrsas

@RosiOli It does not usable in EF 3.1 with params. https://gist.github.com/rionmonster/2c59f449e67edf8cd6164e9fe66c545a#gistcomment-3059688
My unit test:

[Fact]
public void ToSql_Parameters_HasParameter() {
    using var dbContext = CreateDbContext();
    var names = new[] {"John", "Bob"};
    // Act
    var query = dbContext.Students.Where(s => names.Contains(s.Name));
    var (result, paramList) = query.ToSqlWithParams();
    // Assert
    _output.WriteLine(result);
    paramList.Count.ShouldBe(2);
    result.ShouldContain("WHERE [s].[Name] In");
}

I tested it and this works:

public static readonly string[] Names = { "John", "Bob" };

[Fact]
public void ToSql_Parameters_HasParameter()
{
    using var dbContext = CreateDbContext();

    // Act
    var query = dbContext.Students.Where(s => Names.Contains(s.Name));
    var result = query.ToQueryString();
    // Assert
    Console.WriteLine(result);

    var sql = RemoveWhitespace(result);

    var pattern = @$".*.*SELECT.* FROM \[dbo\].\[Students\] AS \[[a-z0-9]+\] WHERE \[[a-z0-9]+\].\[Name\] IN \('{Names[0]}', '{Names[1]}'\)";
    var regex = new Regex(pattern, RegexOptions.IgnoreCase | RegexOptions.Multiline);
    var match = regex.Match(sql);

    match.Success.Should().BeTrue();
}

private static string RemoveWhitespace(string sqlWithNewLines)
{
    var noNewLines = sqlWithNewLines.Replace(Environment.NewLine, " ");
    return Regex.Replace(noNewLines, @"\s +", " ", RegexOptions.Compiled);
}

I use my own version of the ToSql (renamed it ToQueryString to match EF Core 5 upcoming support for this)

@wadee
Copy link

wadee commented Jun 5, 2020

I try to construct a dynamic expression work with filter in set such as "x => set.Contains(x.attr)" and get the generated SQL using below "ToSQL" method in EF Core 3.1.But it throw an InvalidCastException just like:

Exception: System.InvalidCastException: Unable to cast object of type 'System.Collections.Generic.List`1[System.Int32]' to type 'System.Collections.Generic.IEnumerable`1[System.Object]'.
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitIn(InExpression inExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GetCommand(SelectExpression selectExpression)
   at Microsoft.EcoManager.Domain.Core.QueryableExtensions.ToSql[TEntity](IQueryable`1 query)

Here is my code constructing filter set dynamic expression:

private static Expression<Func<TData, bool>> CreateSetFilterExpression<TData, TProperty>(string property, IEnumerable<TProperty> values)
        {
            var type = typeof(TData);
            var arg = Expression.Parameter(type, "x");

            var propertyInfo = type.GetProperty(property);
            Expression exp = Expression.Property(arg, propertyInfo);
            exp = Expression.Convert(exp, typeof(TProperty));

            var methodInfo = typeof(Enumerable)
                .GetMethods()
                .Single(x => x.Name == nameof(Enumerable.Contains) && x.IsGenericMethodDefinition && x.GetGenericArguments().Length == 1 && x.GetParameters().Length == 2)
                .MakeGenericMethod(typeof(TProperty));
            var valuesExpr = Expression.Constant(values);

            exp = Expression.Call(null, methodInfo, valuesExpr, exp);

            var resultLambda = Expression.Lambda<Func<TData, bool>>(exp, arg);
            return resultLambda;

It seems related to the VisitIn method of QuerySqlGenerator in EF core:
https://github.com/dotnet/efcore/blob/2e8ef3516d2bed2f934eea6e2cb92f7a9ff40ab3/src/EFCore.Relational/Query/QuerySqlGenerator.cs#L624
For example, when I try to construct the filter set dynamic expression using CreateSetFilterExpression<SomeEntity, int>(SomePropertyName, values), what the values' type is List<int>. it will throw InvalidCastException when executing to the line "sqlGenerator.GetCommand" in ToSQL():

Exception: System.InvalidCastException: Unable to cast object of type 'System.Collections.Generic.List`1[System.Int32]' to type 'System.Collections.Generic.IEnumerable`1[System.Object]'.
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitIn(InExpression inExpression)
...

I search about int cast to object, and check the boxing and unboxing concept in C#, but I still have no idea how to fix it.

Is that anyone has encountered the same problem?
Please help me if there is any clue about this problem?

@RosiOli I updated your code to work with EFCore 3.1

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);

    string sql = command.CommandText;
    return sql;
}

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);

@CRidge
Copy link

CRidge commented Sep 1, 2020

@wadee I'm having the same issue. Did you ever figure out a way around this problem?

Thanks for finding the exact spot where this blows up - I don't see an obvious way around it, but at least I know what the problem is.

@tdstkt
Copy link

tdstkt commented Sep 8, 2020

@wadee I'm having the same issue. Did you ever figure out a way around this problem?

Thanks for finding the exact spot where this blows up - I don't see an obvious way around it, but at least I know what the problem is.

https://github.com/borisdj/EFCore.BulkExtensions/blob/master/EFCore.BulkExtensions/IQueryableExtensions.cs
I use ToParametrizedSql function from EFCore.BulkExtensions, it's work fine.

@J-W-Chan
Copy link

@wadee I'm having the same issue. Did you ever figure out a way around this problem?
Thanks for finding the exact spot where this blows up - I don't see an obvious way around it, but at least I know what the problem is.

https://github.com/borisdj/EFCore.BulkExtensions/blob/master/EFCore.BulkExtensions/IQueryableExtensions.cs
I use ToParametrizedSql function from EFCore.BulkExtensions, it's work fine.

it's work fine too. thanks.

@mjeson
Copy link

mjeson commented May 20, 2021

@rionmonster, what are the using statements for this code ? I am copy pasting that to a .NET Standard 2.0 and it is missing INodeTypeProvider, IQueryParser, and RelationalQueryModelVisitor

Here are my usings. They are incomplete for .NET Standard 2.0

using System;
using System.Linq;
using System.Reflection;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.Internal;

Update 1.
This one below works for .NET Standard 2.0 . Thanks to this stackoverflow

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

namespace MyNamespace
{
    public static class EfUtility
    {
        public static string ToSql<TEntity>(IQueryable<TEntity> query) where TEntity : class
        {
            using (IEnumerator<TEntity> enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator())
            {
                object relationalCommandCache = enumerator.Private("_relationalCommandCache");
                SelectExpression selectExpression = relationalCommandCache.Private<SelectExpression>("_selectExpression");
                IQuerySqlGeneratorFactory factory = relationalCommandCache.Private<IQuerySqlGeneratorFactory>("_querySqlGeneratorFactory");

                QuerySqlGenerator sqlGenerator = factory.Create();
                IRelationalCommand command = sqlGenerator.GetCommand(selectExpression);

                string sql = command.CommandText;
                return sql;
            }
        }

        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);
    }
}

@lonix1
Copy link

lonix1 commented Apr 3, 2022

In EF Core 5+ we have ToQueryString which easily gives us the sql query. But we still don't have access to parameters.

Does anyone have an approach that includes parameters?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment