Click here to Skip to main content
15,867,488 members
Articles / Programming Languages / C#

Visual Studio Visualizer: Part 2 - Entity Framework

Rate me:
Please Sign up or sign in to vote.
5.00/5 (15 votes)
23 Jun 2013CPOL3 min read 56.8K   68   7
This project creates a Visual Studio visualizer for entity framework queries, views edit and runs the generated SQL.

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.

Image 1

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:

C#
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:

C#
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:

Image 2

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

Image 3

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

Image 4

Insert/Update/Delete Statements

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

Image 5

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

Image 6

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:

C#
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:

C#
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:

C#
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:

C#
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:

C#
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

C#
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:

C#
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:

Image 7

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:

Image 8

Image 9

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)


Written By
Software Developer
Portugal Portugal
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
_Vitor Garcia_25-Jun-13 6:15
_Vitor Garcia_25-Jun-13 6:15 
GeneralMy vote of 5 Pin
Sharath279024-Jun-13 0:31
Sharath279024-Jun-13 0:31 
GeneralMy vote of 5 Pin
Rui Jarimba20-Jun-13 1:07
professionalRui Jarimba20-Jun-13 1:07 
GeneralMy vote of 5 Pin
Uday P.Singh17-Jun-13 20:44
Uday P.Singh17-Jun-13 20:44 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA13-Jun-13 20:46
professionalȘtefan-Mihai MOGA13-Jun-13 20:46 
GeneralMy vote of 5 Pin
Prasad Khandekar7-May-13 3:29
professionalPrasad Khandekar7-May-13 3:29 
Looks very good!
GeneralMy vote of 5 Pin
Marc Clifton2-May-13 1:04
mvaMarc Clifton2-May-13 1:04 

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

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