Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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;
}
}
@zuosc
Copy link

zuosc commented Oct 26, 2017

Hi~

It's work fine, but when I use 'indexof' in ef,it seems not correct?

Thanks

@psimoneau22
Copy link

psimoneau22 commented Dec 14, 2017

here is an updated version for 2.0

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 PropertyInfo DatabaseDependenciesField = typeof(Database).GetTypeInfo().DeclaredProperties.Single(x => x.Name == "Dependencies");

	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 = (QueryCompiler)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 databaseDependencies = (DatabaseDependencies)DatabaseDependenciesField.GetValue(database);
		var queryCompilationContext = databaseDependencies.QueryCompilationContextFactory.Create(false);
		var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
		modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
		var sql = modelVisitor.Queries.First().ToString();

		return sql;
	}
}

@Bramvanelderen10
Copy link

Bramvanelderen10 commented Mar 7, 2018

An updated version for 2.1

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 FieldInfo QueryModelGeneratorField = QueryCompilerTypeInfo.DeclaredFields.First(x => x.Name == "_queryModelGenerator");

        private static readonly FieldInfo DataBaseField = QueryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");

        private static readonly PropertyInfo DatabaseDependenciesField = typeof(Database).GetTypeInfo().DeclaredProperties.Single(x => x.Name == "Dependencies");

        public static string ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class
        {
            var queryCompiler = (QueryCompiler)QueryCompilerField.GetValue(query.Provider);
            var modelGenerator = (QueryModelGenerator)QueryModelGeneratorField.GetValue(queryCompiler);
            var queryModel = modelGenerator.ParseQuery(query.Expression);
            var database = (IDatabase)DataBaseField.GetValue(queryCompiler);
            var databaseDependencies = (DatabaseDependencies)DatabaseDependenciesField.GetValue(database);
            var queryCompilationContext = databaseDependencies.QueryCompilationContextFactory.Create(false);
            var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
            modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
            var sql = modelVisitor.Queries.First().ToString();

            return sql;
        }
    }

@nprasaanth07
Copy link

nprasaanth07 commented Apr 6, 2018

@Bramvanelderen10 I get an exception on this line : private static readonly FieldInfo QueryModelGeneratorField = QueryCompilerTypeInfo.DeclaredFields.First(x => x.Name == "_queryModelGenerator");

Basically the QueryCompilerTypeInfo.DeclaredFields returns an array of FieldInfo but its length is 0.

I am using EFCore 2.1 preview on .NET framework (not core)

@CutieDaisy
Copy link

CutieDaisy commented May 9, 2018

Thanks! I am also using EFCore 2.1. This really helped.

@danosilva81
Copy link

danosilva81 commented Aug 11, 2018

@psimoneau22 wonderfull! it works like a charm in 2.0! thanks!

@hjkl950217
Copy link

hjkl950217 commented Aug 16, 2018

@Bramvanelderen10 The code works fine in 2.1, thank you very much!

@MeggyPoe
Copy link

MeggyPoe commented Jan 21, 2019

@psimoneau22 This is not working if query contains Include("someEntity"). I only get main query, do you know how to make it work?

@AmarPotki
Copy link

AmarPotki commented Feb 23, 2019

I have the same problem, I think it has problems with one to many and many to many relationships
Did you find a solution @MeggyPoe

@robinrodricks
Copy link

robinrodricks commented Mar 27, 2019

This will give you the SQL query as a parameterized query. Works with EF Core 2.1. You need to install the TSQL.Parser nuget library.

Usage:

Dapper.DynamicParameters parameters;
var sql = query.ToSql(out parameters);

Code:

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 FieldInfo QueryModelGeneratorField = typeof(QueryCompiler).GetTypeInfo().DeclaredFields.First(x => x.Name == "_queryModelGenerator");
private static readonly FieldInfo DataBaseField = QueryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");
private static readonly PropertyInfo DatabaseDependenciesField = typeof(Database).GetTypeInfo().DeclaredProperties.Single(x => x.Name == "Dependencies");

public static string ToSql<TEntity>(this IQueryable<TEntity> query, out DynamicParameters parameters) {

	// convert EF core Linq expression to SQL
	var queryCompiler = (QueryCompiler)QueryCompilerField.GetValue(query.Provider);
	var queryModelGenerator = (QueryModelGenerator)QueryModelGeneratorField.GetValue(queryCompiler);
	var queryModel = queryModelGenerator.ParseQuery(query.Expression);
	var database = DataBaseField.GetValue(queryCompiler);
	var databaseDependencies = (DatabaseDependencies)DatabaseDependenciesField.GetValue(database);
	var queryCompilationContext = databaseDependencies.QueryCompilationContextFactory.Create(false);
	var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
	modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
	var sql = modelVisitor.Queries.First().ToString();

	// replace literals with parameters
	parameters = new DynamicParameters();
	string sql2 = ReverseParameterizeQuery(sql, parameters);

	return sql2;
}

private static string ReverseParameterizeQuery(string sql, DynamicParameters parameters) {

	// parse SQL string
	var parsed = TSQL.TSQLTokenizer.ParseTokens(sql, false, true);

	// replace literals with parameters
	var result = new StringBuilder();
	var pc = 1;
	for (int t = 0; t < parsed.Count; t++) {
		var token = parsed[t];
		if (token.Type == TSQL.Tokens.TSQLTokenType.NumericLiteral || token.Type == TSQL.Tokens.TSQLTokenType.StringLiteral) {

			// parameterize it
			result.Append("@p");
			result.Append(pc);

			// save the parameter value
			if (token is TSQL.Tokens.TSQLStringLiteral) {
				parameters.Add("@p" + pc, ((TSQL.Tokens.TSQLStringLiteral)token).Value);
			} else {
				parameters.Add("@p" + pc, Convert.ToDouble(token.Text));
			}
			pc++;

		} else {
			result.Append(token.Text);
		}
	}

	return result.ToString();
}

@zuice32
Copy link

zuice32 commented Apr 10, 2019

Where is the .net core 2.2 version. Apparently "RelationalQueryModelVisitor" no longer exists in "Microsoft.EntityFrameworkCore.Query" namespace

@glikoz
Copy link

glikoz commented Jul 18, 2019

Where is the .net core 2.2 version. Apparently "RelationalQueryModelVisitor" no longer exists in "Microsoft.EntityFrameworkCore.Query" namespace

It is in the Microsoft.EntityFrameworkCore.Relational nuget package.

@borisdj
Copy link

borisdj commented Sep 25, 2019

Any ideas how to implement it for v3.0 where 'QueryModelGenerator' and 'RelationalQueryModelVisitor' do not exist.

@RosiOli
Copy link

RosiOli commented Sep 30, 2019

This is how I did it in .NET Core 3. pretty dirty...

        public static string ToSql<TEntity>(this IQueryable<TEntity> query)
        {
            var enumerator = query.Provider
                .Execute<IEnumerable<TEntity>>(query.Expression)
                .GetEnumerator();
            var enumeratorType = enumerator.GetType();
            var selectFieldInfo = enumeratorType.GetField("_selectExpression", BindingFlags.NonPublic | BindingFlags.Instance) ?? throw new InvalidOperationException($"cannot find field _selectExpression on type {enumeratorType.Name}");
            var sqlGeneratorFieldInfo = enumeratorType.GetField("_querySqlGeneratorFactory", BindingFlags.NonPublic | BindingFlags.Instance) ?? throw new InvalidOperationException($"cannot find field _querySqlGeneratorFactory on type {enumeratorType.Name}");
            var selectExpression = selectFieldInfo.GetValue(enumerator) as SelectExpression ?? throw new InvalidOperationException($"could not get SelectExpression");
            var factory = sqlGeneratorFieldInfo.GetValue(enumerator) as IQuerySqlGeneratorFactory ?? throw new InvalidOperationException($"could not get IQuerySqlGeneratorFactory");
            var sqlGenerator = factory.Create();
            var command = sqlGenerator.GetCommand(selectExpression);
            var sql = command.CommandText;
            return sql;
        }

@BobbyCannon
Copy link

BobbyCannon commented Oct 3, 2019

RosiOli, thanks for this. It's working for my needs.

@borisdj
Copy link

borisdj commented Oct 3, 2019

Great work RosiOli.
Would you happen to know how to do same from ToParametrizedSql (borisdj/EFCore.BulkExtensions#231)
The code you gave makes Sql string with names of parameters already embedded.
So the only remaining issue is how to get List of those Parameters with their values, that are to be returned in tupple.
This was the method in Core 2:

internal static (string, IEnumerable<SqlParameter>) ToParametrizedSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class
{
	var queryCompiler = (QueryCompiler)QueryCompilerField.GetValue(query.Provider);
	var modelGenerator = (QueryModelGenerator)QueryModelGeneratorField.GetValue(queryCompiler);
	var parameterValues = new SimpleParameterValues();
	var diagnosticsLogger = new DiagnosticsLogger<DbLoggerCategory.Query>(new LoggerFactory(), null, new DiagnosticListener("Temp"));
	var parameterExpression = modelGenerator.ExtractParameters(diagnosticsLogger, query.Expression, parameterValues);
	var queryModel = modelGenerator.ParseQuery(parameterExpression);
	var database = (IDatabase)DataBaseField.GetValue(queryCompiler);
	var databaseDependencies = (DatabaseDependencies)DatabaseDependenciesField.GetValue(database);
	var queryCompilationContext = databaseDependencies.QueryCompilationContextFactory.Create(false);
	var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();

	modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
	
	string sql = modelVisitor.Queries.First().ToString();
	return (sql, parameterValues.ParameterValues.Select(x => new SqlParameter(x.Key, x.Value)));
}

@yangzhongke
Copy link

yangzhongke commented Oct 12, 2019

I have refined the code and replaced the hack code(Reflection) of smitpatel with GetService()
https://github.com/yangzhongke/ZackData.Net/blob/master/Tests.NetCore/IQueryableExtensions.cs

To be clear, it doesn't support EF Core 3.0.

RosiOli's code support EF Core 3.0, but it uses hack codes and needs to execute the IQueryable.

@RosiOli
Copy link

RosiOli commented Oct 18, 2019

hey mate, sorry for the delay - inspect the 'command' variable. there is a 'Parameters' property on there you can use I think.

ok, try this @borisdj for .NET Core 3.0

        public static (string, IReadOnlyDictionary<string, object>) ToSqlWithParams<TEntity>(this IQueryable<TEntity> query)
        {
            var enumerator = query.Provider
                .Execute<IEnumerable<TEntity>>(query.Expression)
                .GetEnumerator();
            const BindingFlags bindingFlags = BindingFlags.NonPublic | BindingFlags.Instance;
            var enumeratorType = enumerator.GetType();
            var selectFieldInfo = enumeratorType.GetField("_selectExpression", bindingFlags) ?? throw new InvalidOperationException($"cannot find field _selectExpression on type {enumeratorType.Name}");
            var sqlGeneratorFieldInfo = enumeratorType.GetField("_querySqlGeneratorFactory", bindingFlags) ?? throw new InvalidOperationException($"cannot find field _querySqlGeneratorFactory on type {enumeratorType.Name}");
            var queryContextFieldInfo = enumeratorType.GetField("_relationalQueryContext", bindingFlags) ?? throw new InvalidOperationException($"cannot find field _relationalQueryContext on type {enumeratorType.Name}");

            var selectExpression = selectFieldInfo.GetValue(enumerator) as SelectExpression ?? throw new InvalidOperationException($"could not get SelectExpression");
            var factory = sqlGeneratorFieldInfo.GetValue(enumerator) as IQuerySqlGeneratorFactory ?? throw new InvalidOperationException($"could not get SqlServerQuerySqlGeneratorFactory");
            var queryContext = queryContextFieldInfo.GetValue(enumerator) as RelationalQueryContext ?? throw new InvalidOperationException($"could not get RelationalQueryContext");

            var sqlGenerator = factory.Create();
            var command = sqlGenerator.GetCommand(selectExpression);

            var parametersDict = queryContext.ParameterValues;
            var sql = command.CommandText;
            return (sql, parametersDict);
        }


just returning a Dictionary rather than specific SqlParameter type. you can convert easily enough.
i tested it with this. but yeah - give it a proper test and see what you think.

        static void Main(string[] args)
        {
            var age = 45;
            using (var context = new MyDbContext())
            {
                var query = context.People
                    .Where(p => p.Name == "Kate" && p.Age >= age)
                    .OrderBy(p => p.Age)
                    .Select(p => new { p.Name });
                var temp = query.ToSqlWithParams();
            }
        }
`` `

@felixlindemann
Copy link

felixlindemann commented Oct 24, 2019

@RosiOli
Copy link

RosiOli commented Oct 24, 2019

@felixlindemann - you'll prob want to use this one: https://gist.github.com/rionmonster/2c59f449e67edf8cd6164e9fe66c545a#gistcomment-3059688 it returns the parameter values.

@halllo
Copy link

halllo commented Dec 12, 2019

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

@chrsas
Copy link

chrsas commented Jan 7, 2020

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

Stack:

System.InvalidCastException : Unable to cast object of type 'Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlParameterExpression' to type 'Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlConstantExpression'.
   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 System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GetCommand(SelectExpression selectExpression)
   at Sunlight.EFCore.Extensions.QueryableExtensions.ToSqlWithParams[TEntity](IQueryable`1 query)

@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

J-W-Chan commented Dec 15, 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.

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