Visual Studio Visualizer: Part 2 - Entity Framework

Introduction

This project creates a Visual Studio visualizer for entity framework queries, views edit and runs the generated SQL. The option to view String, StringBuilder, XDocument, SqlCommand, and OleDbCommand objects is also available.

Project website at http://vsdatawatchers.codeplex.com.

Background

Part 1 is available here.

If you are not familiar with Visual Studio visualizers, I recommend reading part 1.

Part 3 is available here.

Entity Framework

The big focus of this visualizer is the generated SQL of Entity Framework. There are two different base contexts for entity framework objects, the ObjectContext and DbContext.

The ObjectContext is used by the default code generation and the DbContext is used by the default template of Entity Framework 5 code generator.

On each one, there are two different types of operations, the queries on the model, like:

DateTime data = new DateTime(1980, 1, 1);
var t = from e in entities.Employee 
        where e.BirthDate > data
        select e;

Where the object visualized is t.

The other operations are the other CRUD, the update, delete and create, like:

List<EFObjectsOC.Employee> employees = t.ToList();
employees.FirstOrDefault().JobTitle = "Up up";
EFObjectsOC.Employee employeeToDelete = employees.LastOrDefault(); 
entities.Employee.DeleteObject(employeeToDelete);
 
EFObjectsOC.Employee newEmployee = new EFObjectsOC.Employee();
newEmployee.HireDate = DateTime.Now;
newEmployee.Gender = "M";
newEmployee.JobTitle = "new job";
newEmployee.NationalIDNumber = "987654321";
newEmployee.BusinessEntityID = 987654321;
 
EFObjectsOC.Employee newEmployee2 = new EFObjectsOC.Employee();
newEmployee2.HireDate = DateTime.Now;
newEmployee2.Gender = "M";
newEmployee2.JobTitle = "new job";
newEmployee2.NationalIDNumber = "123456789";
newEmployee2.BusinessEntityID = 123456789;
 
entities.AddToEmployee(newEmployee);
entities.AddToEmployee(newEmployee2);

Where the object visualized is entities.

Select Statements

Hover by the t, and selecting the visualizer, we get a SQL editor with the select statement and the possibility to run the query against the database:

For the SQL language, the editor enables some more options:

We can even check the connection string, run the query and see the data returned:

Insert/Update/Delete Statements

To see the SQL from insert/update/delete statements, hover the mouse by the entities and select the visualizer:

There is also an error check for the entities added to the context:

The Code

This visualizer is registered for ObjectQuery, ObjectContext, DbQuery<> and DbContext. The ObjectQuery and DbQuery are for the query statements on the ObjectContext and DbContext context objects respectively, the ObjectContext and DbContext are registered for all other CRUD operations made to the context.

If you aren't familiar with the Visual Studio visualizers, please read this.

The ObjectQuery

The SQLQueryOptions is just a wrapper to the properties that will be presented by the visualizer. First, we will get the generated SQL using the ToTraceString() method, then get all the parameters used and the result will be a valid SQL query:

public static SQLQueryOptions ToSqlString(this ObjectQuery objectQuery)
{
    SQLQueryOptions sqlOptions = new SQLQueryOptions();
 
    string sql = objectQuery.ToTraceString();
 
    StringBuilder sb = new StringBuilder();
    if (objectQuery.Context != null && objectQuery.Context.Connection != null 
                 && objectQuery.Context.Connection is EntityConnection)
        sqlOptions.ConnectionString = 
          (objectQuery.Context.Connection as EntityConnection).StoreConnection.ConnectionString;
 
    foreach (ObjectParameter parameter in objectQuery.Parameters)
    {
        SqlParameter r = new SqlParameter(parameter.ParameterType.FullName, parameter.Value);
 
        sb.AppendLine(string.Format("DECLARE @{0} {1}{2};", 
                      parameter.Name, r.SqlDbType, ContextHelper.GetTypeLength(r.SqlDbType)));
        sb.AppendLine(string.Format("SET @{0} = '{1}';", parameter.Name, parameter.Value));
    }
 
    sb.AppendLine();
    sb.AppendLine(sql);
 
    sqlOptions.SQLCommand = sb.ToString();
 
    return sqlOptions;
}   

The DbQuery<>

For the DbQuery<>, we must get the values using reflection, this code was found in stackoverflow:

public static SQLQueryOptions ToSqlString(this IQueryable queryable)
{
    SQLQueryOptions sqlOptions = new SQLQueryOptions();
 
    var iqProp = queryable.GetType().GetProperty("InternalQuery", 
        BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
 
    var iq = iqProp.GetValue(queryable);
 
    var oqProp = iq.GetType().GetProperty("ObjectQuery", 
        BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
 
    var oq = oqProp.GetValue(iq);
 
    var objectQuery = oq as ObjectQuery;
 
    return objectQuery.ToSqlString();
}   

Here, we can use the extension method ToSqlString() develop to the ObjectQuery.

The ObjectContext

The ObjectContext also uses reflection to get the generated SQL:

public static SQLQueryOptions ToSqlString(this ObjectContext objectContext)
{
    SQLQueryOptions sqlOptions = new SQLQueryOptions();
    sqlOptions.ConnectionString = 
      (objectContext.Connection as EntityConnection).StoreConnection.ConnectionString;
    sqlOptions.SQLCommand = ContextHelper.GetSQLCommands(objectContext);
    return sqlOptions;
}

The helper method GetSQLCommands:

internal static string GetSQLCommands(ObjectContext context)
{
    StringBuilder sql = new StringBuilder();
    foreach (DbCommand command in ContextHelper.GetContextCommands(context))
    {
        sql.Append("------------------------------------");
        sql.AppendLine();
        sql.Append("-- Command");
        sql.AppendLine();
        sql.Append("------------------------------------");
        sql.AppendLine();
        foreach (DbParameter parameter in command.Parameters)
        {
            System.Data.SqlClient.SqlParameter r = new System.Data.SqlClient.SqlParameter(
                        parameter.DbType.ToString(), parameter.Value);
            sql.AppendLine(string.Format("DECLARE {0} {1}{2};", 
                           parameter.ParameterName, 
                           r.SqlDbType, ContextHelper.GetTypeLength(r.SqlDbType)));
            sql.AppendLine(string.Format("SET {0} = '{1}';", 
                           parameter.ParameterName, parameter.Value));
        }
        sql.AppendLine();
        sql.Append(command.CommandText);
        sql.AppendFormat("{0}GO{0}{0}", Environment.NewLine);
    } 
    return sql.ToString();
}

The actual reflection is in this piece, this code was found at a Entity Framework forum:

internal static IEnumerable<DbCommand> GetContextCommands(ObjectContext context)
{
    const string EntityAssemblyName = 
      "System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089";
    var entityAssemly = Assembly.Load(EntityAssemblyName);
    var updateTranslatorType = 
      entityAssemly.GetType("System.Data.Mapping.Update.Internal.UpdateTranslator");
    var functionUpdateCommandType = 
      entityAssemly.GetType("System.Data.Mapping.Update.Internal.FunctionUpdateCommand");
    var dynamicUpdateCommandType = 
      entityAssemly.GetType("System.Data.Mapping.Update.Internal.DynamicUpdateCommand");
    var ctorParams = new object[]
    {
        context.ObjectStateManager,
        ((EntityConnection)context.Connection).GetMetadataWorkspace(),
        (EntityConnection)context.Connection,
        context.CommandTimeout
    };
    var updateTranslator = Activator.CreateInstance(
        updateTranslatorType,
        BindingFlags.NonPublic | BindingFlags.Instance,
        null,
        ctorParams,
        null);
    MethodInfo produceCommandsMethod = updateTranslatorType
        .GetMethod("ProduceCommands", BindingFlags.Instance | BindingFlags.NonPublic);
    var updateCommands = produceCommandsMethod.Invoke(updateTranslator, null) as IEnumerable;
    foreach (object o in updateCommands)
    {
        if (functionUpdateCommandType.IsInstanceOfType(o))
        {
            FieldInfo mdbCommandField = functionUpdateCommandType.GetField(
                "m_dbCommand", BindingFlags.Instance | BindingFlags.NonPublic);
            yield return mdbCommandField.GetValue(o) as DbCommand;
        }
        else if (dynamicUpdateCommandType.IsInstanceOfType(o))
        {
            MethodInfo createCommandMethod = dynamicUpdateCommandType.GetMethod(
                "CreateCommand", BindingFlags.Instance | BindingFlags.NonPublic);
            var methodParams = new object[]
            {
                updateTranslator,
                new Dictionary<int, object>()
            };
            yield return createCommandMethod.Invoke(o, methodParams) as DbCommand;
        }
    }
}  

The DbContext

public static SQLQueryOptions ToSqlString(this DbContext dbContext)
{
    SQLQueryOptions sqlOptions = new SQLQueryOptions();
    var objectContext = ((IObjectContextAdapter)dbContext).ObjectContext;
    sqlOptions.ConnectionString = 
      (objectContext.Connection as EntityConnection).StoreConnection.ConnectionString;
    sqlOptions.SQLCommand = ContextHelper.GetSQLCommands(objectContext);
    return sqlOptions;
} 

The ContextHelper.GetTypeLength() method is used to set a default length for the SQL parameter, for example, if the parameter is of type nvarchar, then we get nvarchar(max). Without this length qualifier, the value set to the parameter would get truncated. So the code:

public static string GetTypeLength(SqlDbType type)
{
    switch (type)
    {
        case SqlDbType.Decimal:
            return "(38,38)";
        case SqlDbType.Binary:
        case SqlDbType.Char:
        case SqlDbType.NChar:
            return "(8000)";
        case SqlDbType.NVarChar:
        case SqlDbType.VarBinary:
        case SqlDbType.VarChar:
            return "(MAX)";
        default:
            return "";
    }
}

String, StringBuilder and XDocument

This visualizer also supports String, StringBuilder and XDocument objects. The XDocument visualizer adds a simple XML highlighter:

The String and the StringBuilder are similar, and if the string object is a SQL statement, the user can change the language to SQL, in the Languages menu, and a basic query editor appears:

History

• 2013-05-02: Article upload
• 2013-05-07: Links updated
• 2013-06-10: Links updated. Reference to new objects supported by the visualizer.
• 2013-06-24: Bug correction on SQL parameter generation.