rionmonster/IQueryableExtensions

zuosc · 2017-10-26T03:45:27Z

Hi~

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

Thanks

psimoneau22 · 2017-12-14T14:42:28Z

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 · 2018-03-07T08:24:09Z

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 · 2018-04-06T02:43:39Z

@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 · 2018-05-09T20:41:23Z

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

danosilva81 · 2018-08-11T16:07:31Z

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

hjkl950217 · 2018-08-16T14:28:16Z

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

MeggyPoe · 2019-01-21T23:51:58Z

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

AmarPotki · 2019-02-23T06:24:49Z

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 · 2019-03-27T05:59:24Z

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 · 2019-04-10T00:21:34Z

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

glikoz · 2019-07-18T11:13:27Z

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 · 2019-09-25T17:58:01Z

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

RosiOli · 2019-09-30T02:39:53Z

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 · 2019-10-03T01:01:26Z

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

borisdj · 2019-10-03T17:45:55Z

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 · 2019-10-12T05:26:40Z

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 · 2019-10-18T23:07:24Z

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 · 2019-10-24T19:15:47Z

thank you @RosiOli... exeactly what I was looking for. https://gist.github.com/rionmonster/2c59f449e67edf8cd6164e9fe66c545a#gistcomment-3040767

RosiOli · 2019-10-24T23:21:52Z

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

halllo · 2019-12-12T10:46:23Z

@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 · 2020-01-07T10:56:20Z

@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 · 2020-01-07T15:39:51Z

@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 · 2020-05-27T11:44:25Z

@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 · 2020-06-05T01:24:34Z

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 · 2020-09-01T08:15:22Z

@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 · 2020-09-08T02:04:58Z

@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 · 2020-12-15T07:04:07Z

@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 · 2021-05-20T14:02:39Z

@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 · 2022-04-03T08:34:39Z

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?

rionmonster/IQueryableExtensions

zuosc commented Oct 26, 2017

psimoneau22 commented Dec 14, 2017 •

edited

Loading

Bramvanelderen10 commented Mar 7, 2018 •

edited

Loading

nprasaanth07 commented Apr 6, 2018

CutieDaisy commented May 9, 2018

danosilva81 commented Aug 11, 2018

hjkl950217 commented Aug 16, 2018

MeggyPoe commented Jan 21, 2019

AmarPotki commented Feb 23, 2019

robinrodricks commented Mar 27, 2019 •

edited

Loading

zuice32 commented Apr 10, 2019

glikoz commented Jul 18, 2019 •

edited

Loading

borisdj commented Sep 25, 2019

RosiOli commented Sep 30, 2019 •

edited

Loading

BobbyCannon commented Oct 3, 2019

borisdj commented Oct 3, 2019 •

edited

Loading

yangzhongke commented Oct 12, 2019

RosiOli commented Oct 18, 2019 •

edited

Loading

felixlindemann commented Oct 24, 2019 •

edited

Loading

RosiOli commented Oct 24, 2019

halllo commented Dec 12, 2019

chrsas commented Jan 7, 2020 •

edited

Loading

halllo commented Jan 7, 2020 •

edited

Loading

Rubenisme commented May 27, 2020 •

edited

Loading

wadee commented Jun 5, 2020 •

edited

Loading

CRidge commented Sep 1, 2020

tdstkt commented Sep 8, 2020

J-W-Chan commented Dec 15, 2020

mjeson commented May 20, 2021 •

edited

Loading

lonix1 commented Apr 3, 2022

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

rionmonster/IQueryableExtensions

zuosc commented Oct 26, 2017

psimoneau22 commented Dec 14, 2017 • edited Loading

Bramvanelderen10 commented Mar 7, 2018 • edited Loading

nprasaanth07 commented Apr 6, 2018

CutieDaisy commented May 9, 2018

danosilva81 commented Aug 11, 2018

hjkl950217 commented Aug 16, 2018

MeggyPoe commented Jan 21, 2019

AmarPotki commented Feb 23, 2019

robinrodricks commented Mar 27, 2019 • edited Loading

zuice32 commented Apr 10, 2019

glikoz commented Jul 18, 2019 • edited Loading

borisdj commented Sep 25, 2019

RosiOli commented Sep 30, 2019 • edited Loading

BobbyCannon commented Oct 3, 2019

borisdj commented Oct 3, 2019 • edited Loading

yangzhongke commented Oct 12, 2019

RosiOli commented Oct 18, 2019 • edited Loading

felixlindemann commented Oct 24, 2019 • edited Loading

RosiOli commented Oct 24, 2019

halllo commented Dec 12, 2019

chrsas commented Jan 7, 2020 • edited Loading

halllo commented Jan 7, 2020 • edited Loading

Rubenisme commented May 27, 2020 • edited Loading

wadee commented Jun 5, 2020 • edited Loading

CRidge commented Sep 1, 2020

tdstkt commented Sep 8, 2020

J-W-Chan commented Dec 15, 2020

mjeson commented May 20, 2021 • edited Loading

lonix1 commented Apr 3, 2022

psimoneau22 commented Dec 14, 2017 •

edited

Loading

Bramvanelderen10 commented Mar 7, 2018 •

edited

Loading

robinrodricks commented Mar 27, 2019 •

edited

Loading

glikoz commented Jul 18, 2019 •

edited

Loading

RosiOli commented Sep 30, 2019 •

edited

Loading

borisdj commented Oct 3, 2019 •

edited

Loading

RosiOli commented Oct 18, 2019 •

edited

Loading

felixlindemann commented Oct 24, 2019 •

edited

Loading

chrsas commented Jan 7, 2020 •

edited

Loading

halllo commented Jan 7, 2020 •

edited

Loading

Rubenisme commented May 27, 2020 •

edited

Loading

wadee commented Jun 5, 2020 •

edited

Loading

mjeson commented May 20, 2021 •

edited

Loading