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;
}
}
@ykavalenka

This comment has been minimized.

Copy link

@ykavalenka ykavalenka commented Apr 10, 2017

QueryCompilerTypeInfo not supproted at ASP.NET CORE application with build core1.1 version. Mb exist library where containst QueryCompilerTypeInfo class ?

@rionmonster

This comment has been minimized.

Copy link
Owner Author

@rionmonster rionmonster commented Apr 10, 2017

Hmmm.

I currently have this working as expected using EF Core 1.1.1 and building without any compilation errors.

Ensure that you have all of the following using statements added:

using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using System.Reflection;
using System.Linq;
@ykavalenka

This comment has been minimized.

Copy link

@ykavalenka ykavalenka commented Apr 12, 2017

Yes, i'm use this using statements but its not worked.
Mb you using specific package version EFCore or another?

using System;
using System.Linq;
using System.Reflection;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using Remotion.Linq.Parsing.Structure;

Core version 1.1

screen

@rayepps

This comment has been minimized.

Copy link

@rayepps rayepps commented Apr 14, 2017

Really appreciate this, I'm using Azure making profiling a pain so this was much needed. Could you please elaborate on the condition to throw an exception.

if (!(query is EntityQueryable<TEntity>) && !(query is InternalDbSet<TEntity>))
{
    throw new ArgumentException("Invalid query");
}

When I ran this it threw the exception. Naturally I commented it out and tried again just to see. To my surprise it worked, still produced the sql. Why is it required here that my query be of either type EntityQueryable<TEntity> or InternalDbSet<TEntity>? Becuase my code tripped this logic and still worked I'm very curious.

@AndreOdon

This comment has been minimized.

Copy link

@AndreOdon AndreOdon commented Aug 18, 2017

Hi
It's work fine, but doesn't log my included relations. How i can solve this?

Thanks

@zuosc

This comment has been minimized.

Copy link

@zuosc zuosc commented Oct 26, 2017

Hi~

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

Thanks

@psimoneau22

This comment has been minimized.

Copy link

@psimoneau22 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

This comment has been minimized.

Copy link

@Bramvanelderen10 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

This comment has been minimized.

Copy link

@nprasaanth07 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

This comment has been minimized.

Copy link

@CutieDaisy CutieDaisy commented May 9, 2018

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

@danosilva81

This comment has been minimized.

Copy link

@danosilva81 danosilva81 commented Aug 11, 2018

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

@hjkl950217

This comment has been minimized.

Copy link

@hjkl950217 hjkl950217 commented Aug 16, 2018

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

@MeggyPoe

This comment has been minimized.

Copy link

@MeggyPoe 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

This comment has been minimized.

Copy link

@AmarPotki 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

This comment has been minimized.

Copy link

@robinrodricks 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

This comment has been minimized.

Copy link

@zuice32 zuice32 commented Apr 10, 2019

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

@glikoz

This comment has been minimized.

Copy link

@glikoz 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

This comment has been minimized.

Copy link

@borisdj borisdj commented Sep 25, 2019

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

@RosiOli

This comment has been minimized.

Copy link

@RosiOli 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

This comment has been minimized.

Copy link

@BobbyCannon BobbyCannon commented Oct 3, 2019

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

@borisdj

This comment has been minimized.

Copy link

@borisdj 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

This comment has been minimized.

Copy link

@yangzhongke 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

This comment has been minimized.

Copy link

@RosiOli 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

This comment has been minimized.

Copy link

@felixlindemann felixlindemann commented Oct 24, 2019

@RosiOli

This comment has been minimized.

Copy link

@RosiOli 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

This comment has been minimized.

Copy link

@halllo 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

This comment has been minimized.

Copy link

@chrsas 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

This comment has been minimized.

Copy link

@halllo 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

This comment has been minimized.

Copy link

@Rubenisme 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

This comment has been minimized.

Copy link

@wadee 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

This comment has been minimized.

Copy link

@CRidge 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

This comment has been minimized.

Copy link

@tdstkt 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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.