Create a gist now

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.

Show comment
Hide comment
@ykavalenka

ykavalenka Apr 10, 2017

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

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.

Show comment
Hide comment
@rionmonster

rionmonster 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;
Owner

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.

Show comment
Hide comment
@ykavalenka

ykavalenka 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

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

@raykrow

This comment has been minimized.

Show comment
Hide comment
@raykrow

raykrow 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.

raykrow 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.

Show comment
Hide comment
@AndreOdon

AndreOdon Aug 18, 2017

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

Thanks

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

Thanks

@zuosc

This comment has been minimized.

Show comment
Hide comment
@zuosc

zuosc Oct 26, 2017

Hi~

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

Thanks

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.

Show comment
Hide comment
@psimoneau22

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

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.

Show comment
Hide comment
@Bramvanelderen10

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

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.

Show comment
Hide comment
@nprasaanth07

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

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

Show comment
Hide comment
@CutieDaisy

CutieDaisy May 9, 2018

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

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

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