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.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.