C# .NET: Get SQL String With Parameters from IQueryable Using Entity Framework

9/10/2014 3:25:19 AM

After many many hours, I have finally figured out how to take an IQueryable object and get the actual SQL statement and parameters. I am using Entity Framework 6.0 (EF 6.0). Now this is not a valid runnable SQL statement. But I did not need that. I only needed the "uniqueness" of the IQueryable object.

Had lots of fustration. Hope this will help alleviate some frustration for others.

Big thanks to Arthununesq at http://social.msdn.microsoft.com/Forums/en-US/91c7fb6d-d1b8-4a7f-aec9-16336dbd619b/access-to-dbquery-parameters-for-logging?forum=adodotnetentityframework

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Linq.Expressions;

...

public static string ToTraceString<T>(IQueryable<T> query)
{
	var internalQueryField = query.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_internalQuery")).FirstOrDefault();

	var internalQuery = internalQueryField.GetValue(query);

	var objectQueryField = internalQuery.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_objectQuery")).FirstOrDefault();

	var objectQuery = objectQueryField.GetValue(internalQuery) as System.Data.Entity.Core.Objects.ObjectQuery<T>;

	return ToTraceStringWithParameters<T>(objectQuery);
}


public static string ToTraceStringWithParameters<T>(System.Data.Entity.Core.Objects.ObjectQuery<T> query)
{
	System.Text.StringBuilder sb = new StringBuilder();

	string traceString = query.ToTraceString() + Environment.NewLine;

	foreach (var parameter in query.Parameters)
	{
		traceString += parameter.Name + " [" + parameter.ParameterType.FullName + "] = " + parameter.Value + "\n";
	}

	return traceString;
}