Niejednokrotnie potrzebujemy podejrzeć zapytanie SQL wygenerowane przez EF. Często zdarza się, że takie zapytanie chcemy odpalić na bazie danych i zobaczyć wynik. Można wtedy podpiąć się profilerem do bazy i przechwycić je w całości. Niestety takie rozwiązanie jest bardzo czasochłonne. Można też wywołać metodę ToString() na obiekcie typu IQuarable, jednakże to rozwiązanie dostarczy nam zapytanie SQL bez parametrów filtrujących. W tym artykule opiszę jak bezpośrednio w kodzie uzyskać pełne zapytanie SQL wraz z parametrami filtrującymi.

Jak wcześniej wspominałem, wywołanie metody ToString() gwarantuje nam tylko połowiczny sukces, który może wyglądać jak poniżej.

SELECT 
    [Project1].[LanguageID] AS [LanguageID], 
    [Project1].[Name] AS [Name], 
    [Project1].[ShortCode] AS [ShortCode]
    FROM ( SELECT 
        [Extent1].[LanguageID] AS [LanguageID], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[ShortCode] AS [ShortCode]
        FROM [dbo].[Languages] AS [Extent1]
        WHERE [Extent1].[LanguageID] = @p__linq__0
    )  AS [Project1]
    ORDER BY [Project1].[Name] ASC

Jak widać wygenerowany SQL nie posiada deklaracji zmiennej filtrującej. Aby dostać się do parametrów zapytania, a tym samym mieć możliwość deklaracji tej zmiennej, należy zrzutować obiekt IQuarable na typ ObjectQuery.

EF 5

W starym EF (tj. do wersji 5) typ ObjectQuery znajduje się w przestrzeni System.Data.Objects. Przykładowa implementacja metody zwracającej zapytanie SQL z parametrami mogła by wyglądać następująco:

public static string ToTraceStringWithParameters(IQueryable @this)
{
	const string LINE = "-----------------------------------------------";

	var objectQuery = @this as ObjectQuery;
	if (objectQuery == null)
	{
		return "QUERY TRACE ERROR";
	}

	var sb = new StringBuilder();
	sb.AppendLine(LINE);
	string sql = objectQuery.ToTraceString();

	if (objectQuery.Parameters.Count > 0)
	{
		// add parameters to query
	}
	sb.AppendLine();
	sb.AppendLine(sql);

	sb.AppendLine(LINE);
	return sb.ToString();
}

Powyższy kod jest stosunkowo łatwy toteż nie pokuszę się o wyjaśnienia.

EF 6

Inaczej natomiast będzie wyglądać implementacja tej metody w EF 6, ponieważ typ ObjectQuery znajduje się w przestrzeni System.Data.Entity.Core.Objects oraz zwykłe zrzutowanie obiektu IQuarable na ObjectQuery zwraca pusty wynik (null).

Przeszukałem pół internetów, aż w końcu natrafiłem na rozwiązanie (link). Otóż, należy skorzystać z refleksji, aby dostać się do właściwego pola, które z powodzeniem da się zrzutować na porządane ObjectQuery. Implementacja wygląda następująco.

public static string ToTraceStringWithParameters(IQueryable @this)
{
	const string LINE = "-----------------------------------------------";

	var internalQueryField = @this.GetType().GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
		.FirstOrDefault(f => f.Name.Equals("_internalQuery"));
	var internalQuery = internalQueryField.GetValue(@this);
	var objectQueryField = internalQuery.GetType().GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
		.FirstOrDefault(f => f.Name.Equals("_objectQuery"));
	var objectQuery = objectQueryField.GetValue(internalQuery) as ObjectQuery;

	if (objectQuery == null)
	{
		return "QUERY TRACE ERROR";
	}

	// rest code
}

Na tym etapie nie pozostaje już nic innego jak tylko zaimplementować brakującą część, czyli logikę odpowiedzialną za właściwą deklarację zmiennych wykorzystywanych w wygenerowanym zapytaniu SQL.

Finalna implementacja

Moja implementacja wygląda jak poniżej.

public static class QueryTraceExtensions
{
    private const string LINE = "-----------------------------------------------";

    public static void TraceQuery(this IQueryable @this)
    {
        if (!Debugger.IsAttached)
        {
            return;
        }

        try
        {
            Debug.WriteLine(ToTraceStringWithParameters(@this));
        }
        catch
        {

        }
    }

    private static string ToTraceStringWithParameters(IQueryable @this)
    {
        var internalQueryField = @this.GetType().GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
            .FirstOrDefault(f => f.Name.Equals("_internalQuery"));
        var internalQuery = internalQueryField.GetValue(@this);
        var objectQueryField = internalQuery.GetType().GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
            .FirstOrDefault(f => f.Name.Equals("_objectQuery"));

        var objectQuery = objectQueryField.GetValue(internalQuery) as ObjectQuery;

        if (objectQuery == null)
        {
            return "QUERY TRACE ERROR";
        }

        var sb = new StringBuilder();
        sb.AppendLine(LINE);
        string sql = objectQuery.ToTraceString();

        if (objectQuery.Parameters.Count > 0)
        {
            var sbParameters = new StringBuilder("DECLARE ");
            foreach (var parameter in objectQuery.Parameters)
            {
                sbParameters.AppendFormat(GetParameterString(parameter, true));
                sbParameters.AppendLine(",");
            }
            sb.AppendLine(sbParameters.ToString().TrimEnd('\r', '\n', ','));
        }
        sb.AppendLine();
        sb.AppendLine(sql);

        sb.AppendLine(LINE);
        return sb.ToString();
    }

    private static string GetParameterString(ObjectParameter parameter, bool inRunFormat)
    {
        if (!inRunFormat)
        {
            return string.Format("@{0} [{1}] = {2}", parameter.Name, parameter.ParameterType.FullName, parameter.Value);
        }

        string typeName = parameter.ParameterType.FullName;
        if (typeName.StartsWith("System.Nullable"))
        {
            typeName = typeName.Substring(typeName.IndexOf("[[") + 2, typeName.IndexOf(",") - typeName.IndexOf("[[") - 2);
        }

        string typeAndValue;
        switch (typeName)
        {
            case "System.Boolean":
                string value;
                if (parameter.Value == null)
                {
                    value = "NULL";
                }
                else
                {
                    value = (bool)parameter.Value ? "1" : "0";
                }
                typeAndValue = string.Format("{0} = {1}", "BIT", value);
                break;

            case "System.String":
                typeAndValue = string.Format("{0} = {1}", "VARCHAR(MAX)", GetStringValue(parameter.Value, true));
                break;

            case "System.DateTime":
                typeAndValue = string.Format("{0} = {1}", "DATETIME", GetStringValue(parameter.Value, true));
                break;

            case "System.Decimal":
            case "System.Double":
                typeAndValue = string.Format("{0} = {1}", "FLOAT", GetStringValue(parameter.Value, false));
                break;

            default:
                typeAndValue = string.Format("{0} = {1}", "INT", GetStringValue(parameter.Value, false));
                break;
        }

        return string.Format("@{0} {1}", parameter.Name, typeAndValue);
    }

    private static string GetStringValue(object value, bool withQuote)
    {
        if (value == null)
        {
            return "NULL";
        }

        return withQuote ? string.Format("'{0}'", value) : value.ToString();
    }
}

Rezultat natomiast wygląda następująco.