Click here to Skip to main content
Click here to Skip to main content
Go to top

Visual Studio Visualizer: Part 2 - Entity Framework

, 23 Jun 2013
Rate this:
Please Sign up or sign in to vote.
This project creates a Visual Studio visualizer for entity framework queries, view edit and run the generated SQL.

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, XDocument, SqlCommand, and OleDbCommand 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.

Part 3 is available here: http://www.codeproject.com/Articles/599504/Visual-Studio-Visualizer-Part-3-Collection-visuali 

Entity Framework

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

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 registered 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 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Frederico Regateiro
Software Developer
Portugal Portugal
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 PinmemberMihai MOGA13-Jun-13 20:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 24 Jun 2013
Article Copyright 2013 by Frederico Regateiro
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid