Visual Studio Visualizer: Part 2 - Entity Framework

Introduction

This project creates a Visual Studio visualizer for entity framework queries, view edit and run the generated SQL. It's also available the option to view String, StringBuilder and XDocument objects.

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

Background 

Part 1 is available here: http://www.codeproject.com/Articles/578777/Visual-Studio-Visualizer-Part-1 

If your not familiar with visual studio visualizers, i recommend reading part 1.

Entity Framework

The big focus of this visualizer is the generated sql of Entity Framework. There are two diferent base context 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 diferent 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 agaist 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/delet statements, hover the mouse by the entities and select the visulizer:

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

The code

This visualizer is registed 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 registed for the 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 properites that will be presented by the visualizer. First we will get the genereted 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};", parameter.Name, 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};", parameter.ParameterName, 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;
} 

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 statment, 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.