Click here to Skip to main content
15,867,835 members
Articles / Web Development / ASP.NET

Accessing SQL from Entity Framework Core Queries in ASP.NET Core

Rate me:
Please Sign up or sign in to vote.
4.20/5 (2 votes)
19 Oct 2016CPOL2 min read 14.7K   4   1
How to access SQL from Entity Framework Code Queries in ASP.NET core

Accessing SQL from Entity Framework Core Queries in ASP.NET Core

ORMs like Entity Framework have always been shrouded in a bit of controversy from SQL purists who aren't in control of the types of queries that these tools are building behind the scenes. While this is a perfectly valid stance to take, these same tools can often assist in getting things done much faster than tinkering with an endless chain of JOINs, sub-queries, etc.

But what if you want to know exactly what is going on behind the scenes? What SQL is being generated by Entity Framework? And is it efficient enough to suit your needs, or do you need to handle writing some yourself?

This post will cover a quick tip that will allow you to see the SQL behind the scenes and judge for yourself using Entity Framework Core.

How Did This Work Prior to Entity Framework Core?

Previously, you could use Reflection to create an ObjectQuery object and then call the ToTraceString() method to actually store the query results as seen below:

C#
// Build a query using Entity Framework
var query = _context.Widgets.Where(w => w.IsReal && w.Id == 42);  
// Get the generated SQL
var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();  

And that's really it. The result of the ToTraceString() call will return a string variable containing the entire SQL query being executed.

Options for Entity Framework Core

The previous approach no longer works within the Entity Framework Core (EF7) world, so we have to resort to one of three options, which may vary depending on your needs:

  • Using Built-in or Custom Logging. Logging the executing query using your logger of choice or the built-in Logger in .NET Core as mentioned in this tutorial.
  • Using a Profiler. Using an SQL Profiler like MiniProfiler to monitor the executing query.
  • Using Crazy Reflection Code. You can implement some custom reflection code similar to the older approach to perform the same basic concept.

Since both of the first two options are fairly well documented, we will be focusing on the crazy reflection approach.

Getting Behind the Scenes in Entity Framework Core

Using the following snippets of code, which rely on Reflection to resolve information about the compiler, parser, database, and fields being targeted, we can use those things to reconstruct what is actually going on behind the scenes.

NOTE: Since this relies on Reflection, it may be subject to breaking in the future, especially with any API changes.

You can thank fellow Microsoft MVP Ricardo Peres for this magic:

C#
public class IQueryableExtensions  
{
    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;
    }
}

And as far as actual usage goes, you would simply call the ToSql() method to return your SQL query string:

C#
// Build a query using Entity Framework
var query = _context.Widgets.Where(w => w.IsReal && w.Id == 42);  
// Get the generated SQL
var sql = query.ToSql();  

You can find a gist of the above code on GitHub here.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United States United States
An experienced Software Developer and Graphic Designer with an extensive knowledge of object-oriented programming, software architecture, design methodologies and database design principles. Specializing in Microsoft Technologies and focused on leveraging a strong technical background and a creative skill-set to create meaningful and successful applications.

Well versed in all aspects of the software development life-cycle and passionate about embracing emerging development technologies and standards, building intuitive interfaces and providing clean, maintainable solutions for even the most complex of problems.

Comments and Discussions

 
QuestionHow can i use it ?? Pin
Member 1276984622-Oct-16 21:23
professionalMember 1276984622-Oct-16 21:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.