Can I see the SQL in progress when an EntitySqlException is thrown, or can I override an EntityCommandCompilationException?

Question

I've gone down a bit of a rabbit hole trying to execute a window function in Entity Framework. (The project in question is built on EF but would gain a lot from calculating PERCENTILE_DISC on the SQL Server.)

I'm creating a new Convention and adding it to the object model to translate calls to a certain method into SQL that executes the PERCENT_DISC window function.

The created EdmFunction has a CommandText of:

PERCENTILE_DISC (0.8) WITHIN GROUP (ORDER BY o.ExpectedPayment) OVER (PARTITION BY o.OrderType)

But when I do this to execute the function:

var medians = context.Set<UserLocation>().Select(x => CustomFunction.Percentile()).ToList();

This throws an EntityCommandCompilationException with the following message:

System.Data.Entity.Core.EntityCommandCompilationException:
'An error occurred while preparing definition of the function 'ConsoleApp5.Percentile'. See the inner exception for details.'

Inner Exception:
EntitySqlException: The query syntax is not valid. Near identifier 'WITHIN', line 1, column 35.

Yet this direct query gets the expected results:

var p80s = context.Database.SqlQuery<decimal>("SELECT DISTINCT PERCENTILE_DISC (0.8) WITHIN GROUP (ORDER BY o.ExpectedPayment) OVER (PARTITION BY o.OrderType) from Orders o").ToList();

I suspect that this is due to the EF parser not being built to handle window functions. So, I'd like to be able to override that EntityCommandCompilationException and have EF try to execute the query anyhow. Failing that, I'd like to at least see the SQL generated so far to see if there's a different problem causing genuinely invalid SQL. How can I accomplish either of these?

Answer 1

I work in one project that uses one interceptor to register slow queries. Below, I put the code of the interceptor:

using System;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Nash.Data.Interceptors.SlowQuery
{
public sealed class SqlQueryCommandInterceptor : IDbCommandInterceptor
{
    private readonly ISlowQueryLogger _slowQueryLogger;

    public SqlQueryCommandInterceptor(ISlowQueryLogger slowQueryLogger)
    {
        _slowQueryLogger = slowQueryLogger;
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        => interceptionContext.UserState = Stopwatch.StartNew();

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        => LogSlowQuery(interceptionContext, command);

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        => interceptionContext.UserState = Stopwatch.StartNew();

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        => LogSlowQuery(interceptionContext, command);

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        => interceptionContext.UserState = Stopwatch.StartNew();

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        => LogSlowQuery(interceptionContext, command);

    private void LogSlowQuery<T>(DbCommandInterceptionContext<T> interceptionContext, DbCommand dbCommand)
    {
        var debugText = GetDbCommandDebugText(dbCommand);
        var userState = (Stopwatch)interceptionContext.UserState;
        userState.Stop();
        var elapsed = userState.Elapsed;
        if (elapsed > TimeSpan.FromSeconds(2.6))
            _slowQueryLogger.LogSlowQuery(debugText, elapsed);
    }

    private static string GetDbCommandDebugText(DbCommand dbCommand)
    {
        var debugText = dbCommand.CommandText;
        if (dbCommand is SqlCommand && debugText.Contains("@"))
        {
            var matches = Regex.Matches(debugText, @"(\@[\w\.]+)").Cast<Match>().ToArray();
            var paramDict = dbCommand.Parameters.Cast<SqlParameter>()
                .Select(x => new
                {
                    ParameterName = x.ParameterName.StartsWith("@") ? x.ParameterName : "@" + x.ParameterName,
                    Value = x.Value,
                })
                .ToDictionary(x => x.ParameterName, x => x.Value);
            var buffer = new StringBuilder();
            var i = 0;
            foreach (var m in matches)
            {
                if (m.Index > i)
                {
                    buffer.Append(debugText.Substring(i, m.Index - i));
                    i = m.Index;
                }
                var paramName = m.Groups[1].Value;
                if (paramDict.TryGetValue(paramName, out var paramVal))
                    if (paramVal == null || DBNull.Value.Equals(paramVal))
                        buffer.Append($"NULL");
                    else
                        buffer.Append($"'{paramVal}'");
                else
                    buffer.Append(paramName);
                i += m.Length;
            }
            if (i < debugText.Length)
                buffer.Append(debugText.Substring(i, debugText.Length - i));
            debugText = buffer.ToString();
        }
        return debugText;
    }
}
}

To use this interceptor you can code something like this:

using System.Data.Entity.Infrastructure.Interception;
DbInterception.Add(NinjectWebCommon.Kernel.Get<SqlQueryCommandInterceptor>());

In my case, I put this code into my web app startup: Global.asax.cs. And I use Ninject to get one instance of the interceptor.

My code can do more then you need... so you maybe needs to read to a better comprehension and adapt it to your needs.

I hope it can help you.