Constructing Type-Safe SQL Statements from ORM Classes

Suppose you have a database application, and the database tables are mapped onto C# classes as used by an ORM such as Entity Framework or NHibernate.

Suppose you need to construct an SQL statement manually, because your ORM does not support or implement (or interface to) a given SQL feature.

Of course, you can always write the SQL statement manually, and query data using EF’s Database.SqlQuery() or NHibernate’s CreateSQLQuery().

The problem I found is that as soon as the data model changes, these manually crafted SQL statements are bound to fail.

Let’s have a look at a simple SELECT statement involving a JOIN of two tables (of course ORMs manage such a query, this is just an illustration):

SELECT s.Name AS SupplierName, a.ZipCode AS AddressZipCode, 
       a.City AS AddressCity, a.Street AS AddressStreet
FROM  Supplier s
INNER JOIN Address a ON s.AddressId = a.Id

The statement includes table names, table aliases, table column names, and column aliases, and I want to construct the column names for the SQL statement from the ORM’s mapped classes.

Gladly I already have the GetPropertyName() function to retrieve as class’s property names, so we can focus on enumerating them:

public static string EnumerateColumns<T>(
    string tablePrefix, 
    string columnPrefix, 
    params Expression<Func<T, object>>[] columns)
    return string.Join(", ", 
        columns.Select(c =>
            (tablePrefix != null ? (tablePrefix + ".") : "") +
            GetPropertyName(c) +
            (columnPrefix != null 
                ? (" AS " + columnPrefix + GetPropertyName(c)) 
                : "")));

So we have a function handling optional table aliases and optional column name prefixes, which we can invoke for every joined table of our statement:

var sql = "SELECT "
    + EnumerateColumns<Supplier>("s", "Supplier", s => s.Id, s => s.Name)
    + ", "
    + EnumerateColumns<Address>("a", "Address", 
        a => a.ZipCode, a => a.City, a => a.Street)
+ @"
FROM " + nameof(Supplier) + @" s
INNER JOIN " + nameof(Address) + " a ON s." 
    + nameof(Supplier.AddressId) + " = a." + nameof(Address.Id);


There is a little bit of cheating hidden in this code, assuming that table names match their class names, and column names match their property names. If they names differ, you can mark up the class declaration with some attribute and query it using GetCustomAttributes(), or EF’s pluralization service (if it is used).

The full code for this article available on my GitHub.

Integrated Database Versioning and Documentation with dbscript 1.02

dbscript 1.02 adds support for edmx-based projects. If have previously written about generating documentation out of edmx files, and these documentation generators are now part of dbscript.

This is the updated table of Documentation Generators in dbscript 1.02:

Generated Documentation

MS SQL Server Oracle PostgreSql edmx
dbscript HTML view view view
dbscript Single HTML view view view view
HTML (single file) view view view view
MediaWiki view view view view
ScrewTurn Wiki (V2) view view view
ScrewTurn Wiki (V3) view view view
Word HTML view view view view

The XSLT files on which the Documentation Generators depend for their functionality are now computed by a separate tool I wrote for this purpose, making the generated documentation more consistent throughout the different formats and databases.

Developers are free to adjust the shipped XSLT style sheets according to their layout and content needs.

Other features

  • Data diagrams can now be restricted to a certain Object Selection resulting in a diagram showing only a part of the full model.
  • Update notifications are implemented via JSONP
  • Some fixes

dbscript is available for download here.

Executing Oracle Stored Procedures using Entity Framework

In a previous post I discussed an XSLT file which generates code to execute Stored Procedures on an SQL Server database using Entity Framework.

DataDirect produces an Entity Framework provider for Oracle databases, and I found that the same XSLT file also works for edmx files generated by DataDirect. The only difference I found was that output parameters are declared as


in SQL Server, but as


in Oracle. Therefore, some adjustments to the file are necessary.

In the first for-each statement on edm:Parameter

 <xsl:if test="position() != 1">, </xsl:if>
 <xsl:if test="@Mode = 'InOut' or @Mode = 'Out'">out </xsl:if>

in the second statement

 par.DbType = System.Data.DbType.<xsl:value-of select="@Type" />;<xsl:if test="@Mode = 'InOut'">
 par.Direction = System.Data.ParameterDirection.InputOutput;
 </xsl:if><xsl:if test="@Mode = 'Out'">
 par.Direction = System.Data.ParameterDirection.Output;</xsl:if>
 <xsl:if test="@Mode != 'InOut' and @Mode != 'Out'">


 <xsl:if test="@Mode = 'InOut' or @Mode = 'Out'">
 par.Value = System.DBNull.Value;

in the third statement

<xsl:if test="@Mode = 'InOut' or @Mode = 'Out'">

Modify the original XSLT in Visual Studio, select your edmx file as Input File, and execute.

Executing Stored Procedures using Entity Framework

Entity Framework allows you to map stored procedures onto so-called Function Imports. However, C# code for these function imports is only generated if the function import accepts as parameter or returns as result an entity table. (I noticed this in VS 2008, cannot tell about VS 2010)

Entity Framework provides the DbConnection and DbCommand classes to connection to an edmx database and execute stored procedures.

I’ll show to extract the necessary information from the edmx file and generate C# code to invoke the function imports.  Since an edmx file is in XML format, code generation is handled by an XSLT style sheet.

Let’s start with the XSLT header which defines all namespaces used by edmx:

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<xsl:stylesheet version="1.0"
 xml:space="default" >
 <xsl:output method="text" omit-xml-declaration="yes"   />
 <xsl:template match="/">
// automatically generated by StoredProcedures.xslt

namespace My.Namespace
 public partial class MyEntities 
 <xsl:apply-templates select="/edmx:Edmx/edmx:Runtime/edmx:ConceptualModels/edm:Schema/edm:EntityContainer/edm:FunctionImport[not(@EntitySet)]" />

The namespace and class name of the ObjectContext are not stored in the edmx, so we have to hard-code them in the XSLT. The root template matches all function imports that do not return EntitySets.

The sub-template

  • generates a void method for each function import
  • opens a DbConnection and sets the funtion import name
  • translates the SQL Server data types of the stored procedure’s parameters into C# data types
  • creates a C# parameter for every stored procedure parameter
  • handles Nullable parameters and IN and OUT parameters
<xsl:template match="edm:FunctionImport">
 <xsl:variable name="method" select="@Name"></xsl:variable>
 <xsl:variable name="proc" select="/edmx:Edmx/edmx:Runtime/edmx:Mappings/cs:Mapping/cs:EntityContainerMapping/cs:FunctionImportMapping[@FunctionImportName=$method]/@FunctionName"></xsl:variable>
public void <xsl:value-of select="@Name"/>(
 <xsl:for-each select="edm:Parameter">
  <xsl:if test="position() != 1">, </xsl:if>
  <xsl:if test="@Mode = 'InOut'">out </xsl:if>
   <xsl:when test="@Type = 'String'">string</xsl:when>
   <xsl:when test="@Type = 'Binary'">byte[]</xsl:when>
   <xsl:otherwise>global::System.Nullable&lt;System.<xsl:value-of select="@Type"/>&gt;</xsl:otherwise>
  <xsl:text> </xsl:text>
  <xsl:value-of select="@Name"></xsl:value-of>
  System.Data.Common.DbConnection conn = base.Connection;
  System.Data.Common.DbCommand cmd = conn.CreateCommand();
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  cmd.CommandText = "MyEntities.<xsl:value-of select="@Name"/>"; 
  System.Data.Common.DbParameter par;
 <xsl:for-each select="edm:Parameter">
  par = cmd.CreateParameter();
  par.ParameterName = "<xsl:value-of select="@Name" />";
  par.DbType = System.Data.DbType.<xsl:value-of select="@Type" />;<xsl:if test="@Mode = 'InOut'">
  par.Direction = System.Data.ParameterDirection.InputOutput;
 <xsl:if test="@Mode != 'InOut'">
  <xsl:if test="@Type != 'String'">
  if (<xsl:value-of select="@Name"></xsl:value-of>.HasValue)
    par.Value = <xsl:value-of select="@Name"></xsl:value-of>.Value;
 <xsl:if test="@Type = 'String'">
  if (<xsl:value-of select="@Name"></xsl:value-of> != null)
    par.Value = <xsl:value-of select="@Name"></xsl:value-of>;</xsl:if>
    par.Value = System.DBNull.Value;
 <xsl:if test="@Mode = 'InOut'">
  par.Value = System.DBNull.Value;
 <xsl:for-each select="edm:Parameter">
  <xsl:if test="@Mode = 'InOut'">
  if (cmd.Parameters["<xsl:value-of select="@Name" />"].Value != System.DBNull.Value)
   <xsl:value-of select="@Name"/> = <xsl:choose>
    <xsl:when test="@Type = 'Binary'">(byte[])</xsl:when>
    <xsl:otherwise>(System.<xsl:value-of select="@Type" />)</xsl:otherwise>
   </xsl:choose>cmd.Parameters["<xsl:value-of select="@Name" />"].Value;
   <xsl:value-of select="@Name"/> = null;

End of stylesheet


Load this XSLT in Visual Studio, select your edmx file as Input File, and execute.

Generating Documentation of Entity Framework edmx Files

The upcoming version 1.02 of dbscript supports uploading, versioning and documenting Entity Framework Model Files (extension .edmx).

I have described earlier how to write XSLT files to convert the contents of edmx files into MediaWiki markup.

Follow these links to view sample output generated by dbscript:

Tracing SQL Statements generated by Entity Framework

The DataContext of Linq2Sql provides a Log property that one can assign a TextWriter to retrieve the text of the SQL statements generated by Linq. The ObjectContext of Entity Framework does not contain such an easy way to trace generated SQL statements.

An MSDN blog provides a solution in the form of an Entity Framework provider which simply wraps the existing EF data provider.

Integration into an existing project is pretty simple and straight forward (see detailed discussion on MSDN):

  • Register Provider Factories in web.config or app.config
  • Derive a class (ExtendedEntities) from the generated Entities class
  • Use the ExtendedEntities class throughout the code

To be able to switch between logging and non-logging code, I found it helpful to modify this approach a bit.

First, all “extended” functionality is activated in the code using a #define symbol. If the symbol is defined, there will also be a compiler warning to notify the developer whether the build is for debug or production.

Second, the tracing functionality will only be activated if the config file contains an AppSetting named “EFTraceDir”.

Next, I replaced the entity context creation code from

using (var context = new Entities())

to a factory pattern

using (var context = Entities.Create())

with the method

static Entities Create()
#if trace
  if (TraceEnabled)
    ExtendedEntities context = new ExtendedEntities();
    context.CommandFinished += EFCommandFinished;
    return context;
  return new Entities();

CommandFinished is an event defined by the EFTracingProvider, and will be use to log the generated SQL to files.

Statements generated by Linq do not have a name, so it would be hard to associate any generated SQL statement with the original Linq definition.

Thus we need to find out where in the code the statement was invoked.

A quick search on StackOverflow resulted in the .Net classes StackTrace and StackFrame. This little routine list the whole stack and returns the class name causing the invocation, which corresponds to the aspx page name (in case you want to trace an ASP.Net application):

private static StringBuilder ListStack(out string sType)
  StringBuilder sb = new StringBuilder();
  sType = "";

  StackTrace st = new StackTrace(true);
  foreach (StackFrame f in st.GetFrames())
    MethodBase m = f.GetMethod();
    if (f.GetFileName() != null)
      sb.AppendLine(string.Format("{0}:{1} {2}.{3}",
        f.GetFileName(), f.GetFileLineNumber(),
        m.DeclaringType.FullName, m.Name));

      if (!string.IsNullOrEmpty(m.DeclaringType.Name))
        sType = m.DeclaringType.Name;

  return sb;

Omitting the condition (f.GetFileName() != null) will also list the .Net system assemblies and methods.

Putting it all together, we simply write the command, its parameters, and the stack trace into a file. The file is named after the aspx page name (i.e. the bottom-most type name on the stack), the statement length, and the statement ID.

private static void EFCommandFinished(object sender, CommandExecutionEventArgs e)
  string sType;
  StringBuilder sb = ListStack(out sType);

    Path.Combine(EFTraceDir, string.Format("{2} {0:D9} {1:D9}.txt",
      e.Command.CommandText.Length, e.CommandId, sType)),
      e.Duration, e.ToTraceString(), sb.ToString()));

Choice of the file name pattern makes it easy to sort files by originating aspx name and statement length.

Multiple files with equal length (as shown in the filename) indicate that the same statement is used several times, a good indicator to check database access code and cache results.

The generated SQL also gives you an idea of how your Linq queries are translated, and whether you need to optimize them or rewrite them to a) reduce statement length and b) reduce the amount of selected data.

private static void EFCommandFinished(object sender, CommandExecutionEventArgs e)
string sType;
StringBuilder sb = ListStack(out sType);

Path.Combine(EFTraceDir, string.Format(“{2} {0:D9} {1:D9}.txt”, e.Command.CommandText.Length, e.CommandId, sType)),
string.Format(“{0}\r\n{1}\r\n{2}\r\n”, e.Duration, e.ToTraceString(), sb.ToString()));

Generating Wiki Documentation from Entity Framework edmx File

After introducing the XML format of Entity Framework’s edmx files, let’s use that knowledge to create a small XSLT style sheet which displays the mappings of tables and entities in a Wiki-style table (which can be used in MediaWiki and SharePoint wikis).

In the XSLT root, we need to declare all namespaces used by the edmx to access nodes and attributes:

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<xsl:stylesheet version="1.0"
    xml:space="default" >
<xsl:output method="html" omit-xml-declaration="yes"  />

<!-- input file is C:\path\to\Model.edmx -->

This XSLT does not start with the mappings section, but with the tables and views inside the Schema definition, and then looks up their Mappings definition:

<xsl:template match="/">
    select="edmx:Edmx/edmx:Runtime/edmx:StorageModels/ssdl:Schema" />
<xsl:template match="ssdl:Schema">
    <table width="100%">
      <xsl:apply-templates select="ssdl:EntityType" >
        <xsl:with-param name="namespace" select="@Namespace" />

This code creates a table row for each database table and its class:

<xsl:template match="ssdl:EntityType" >
  <xsl:param name="namespace"></xsl:param>
      <xsl:variable name="table" select="@Name" ></xsl:variable>
      <xsl:variable name="map" 
                            cs:MappingFragment/@StoreEntitySet=$table]" />
      <xsl:variable name="s" select="$map/*/@TypeName" />
      <xsl:variable name="p" 
            substring($namespace, 1, string-length($namespace) - 5))" />
      <xsl:variable name="class" 
          select="substring($s, string-length($p) + 1, 
            string-length($s) - string-length($p) - 1)">
  <tr valign="top">
    <td >
      [[<xsl:value-of select="@Name"/>]]
      <xsl:value-of select="$class" />

The [[ ]] notation creates a wiki hyperlink that allows developers to document tables and entities, and link to other documentation.

Structure and Analysis of edmx Files (3)

The Entity Designer of Visual Studio 2008 stores its data in an XML file with the extension .edmx. In the first part of this series I covered the Storage Model, the second part dealt with the Conceptual Model.

This part covers the Mappings section with regard to C# mappings. The Mapping section uses the xmlns:cs namespace

  • xmlns:cs=”urn:schemas-microsoft-com:windows:storage:mapping:CS”

For tables and views, the mapping section defines the mappings from database object to C# class (EntitySets and EntityTypes), along with their scalar properties:

      <cs:Mapping Space="C-S" >
          <cs:EntitySetMapping Name="TableFooSet">
            <cs:EntityTypeMapping TypeName="IsTypeOf(My.Namespace.Entities.TableFoo)">
              <cs:MappingFragment StoreEntitySet="TABLEFOO">
                <cs:ScalarProperty Name="ID" ColumnName="ID" />
                <cs:ScalarProperty Name="Name" ColumnName="NAME" />

          <cs:AssociationSetMapping Name="FK_TABLEFOO_TABLEBAR"
            <cs:EndProperty Name="TABLEFOO">
              <cs:ScalarProperty Name="ID" ColumnName="ID" />
            <cs:EndProperty Name="TableBar">
              <cs:ScalarProperty Name="ID" ColumnName="BAR_ID" />
            <cs:Condition ColumnName="BAR_ID" IsNull="false" />

          <cs:FunctionImportMapping FunctionImportName="SPBarFromFoo"
            FunctionName="My.Namespace.Entities.Store.SP_BAR_FROM_FOO" />

End of Mapping section, end of Runtime declarations


The rest of the edmx file contains the edmx:Designer section with layout information (shapes, connectors).

Structure and Analysis of edmx Files (2)

The Entity Designer of Visual Studio 2008 stores its data in an XML file with the extension .edmx. In the first part of this series I covered the Storage Model.

The Conceptual Model describes the EntitySets, Entities, AssociationSets and Associations as .Net classes and attributes in the xmlns:edm and xmlns:a schemas

      <edm:Schema Namespace="My.Namespace.Entities" Alias="Self" xmlns="">
        <edm:EntityContainer Name="MyEntities">
          <edm:EntitySet Name="TableFooSet" EntityType="My.Namespace.Entities.TableFoo" />
          <edm:AssociationSet Name="FK_TABLEFOO_TABLEBAR" Association="My.Entities.FK_TABLEFOO_TABLEBAR">
            <edm:End Role="TableBar" EntitySet="TableBarSet" />
            <edm:End Role="TableFoo" EntitySet="TableFooSet" />
          <edm:FunctionImport Name="SPBarFromFoo">
            <edm:Parameter Name="FooID" Mode="In" Type="Int32" />
            <edm:Parameter Name="BarID" Mode="InOut" Type="Int32" />

The Entity Container lists all sets. Then the entity description is listed in terms of Properties (columns) and Navigational Properties (foreign keys). NavigationalProperties are present in both tables of a foreign key relation:

        <edm:EntityType Name="TableFoo">
            <edm:PropertyRef Name="ID" />
          <edm:Property Name="ID" Type="Int32" Nullable="false" />
          <edm:Property Name="Serial" Type="String" Nullable="false"
            MaxLength="20" Unicode="true" FixedLength="false" />
          <edm:NavigationProperty Name="Bar" Relationship="My.Namespace.Entities.FK_TABLEFOO_TABLEBAR"
            FromRole="TableFoo" ToRole="TableBar" />
        <edm:Association Name="FK_TABLEFOO_TABLEBAR">
          <edm:End Type="My.Namespace.Entities.TableBar" Role="TABLEBAR"
            Multiplicity="1" />
          <edm:End Type="My.Namespace.Entities.TableFoo" Role="TABLEFOO"
            Multiplicity="*" />

The xmlns:a schema is used if property getter or setter have been assigned non-public visibility:

<edm:NavigationProperty Name="TABLEBAR" 
  a:GetterAccess="Private" a:SetterAccess="Private" />

The end of the Conceptual Model


Structure and Analysis of edmx Files (1)

The Entity Designer of Visual Studio 2008 stores its data in an XML file with the extension .edmx.

The file contains the Storage Model, the Conceptual Model, and Mappings between both models, and also layout information of the diagram. (The inclusion of layout data in a model file is really a conceptual mess).

Each section has its one or more XML namespaces. The ones I found necessary for parsing the XML data are:

This is the hierarchical structure of edmx. The first part describes the Storage Model which contains the selected objects from a SQL Server database: Tables, Views, Stored Procedures.

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx >
  <!-- EF Runtime content -->
    <!-- SSDL content -->
      <ssdl:Schema Namespace="My.Namespace.Entities.Store">
        <ssdl:EntityContainer Name="MyEntitiesStoreContainer">
          <ssdl:EntitySet Name="MY_TABLE_NAME"
            store:Type="Tables" Schema="dbo" />

          <ssdl:EntitySet Name="MY_VIEW_NAME"
            store:Type="Views" store:Schema="dbo"
            <ssdl:DefiningQuery>SELECT FOO FROM BAR

The foreign keys are listed as Association Sets:

          <ssdl:AssociationSet Name="FK_TABLEFOO_TABLEBAR"
            <ssdl:End Role="TABLEBAR" EntitySet="TABLEBAR" />
            <ssdl:End Role="TABLEFOO" EntitySet="TABLEFOO" />

After the Entity Container, all Entity Types and Associations are declared with their names and attributes:

        <ssdl:EntityType Name="TABLEFOO">
            <ssdl:PropertyRef Name="ID" />
          <ssdl:Property Name="ID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
          <ssdl:Property Name="NAME" Type="nvarchar" Nullable="false" MaxLength="150" />
          <ssdl:Property Name="BAR_ID" Type="int" Nullable="true" />
        <ssdl:Association Name="FK_TABLEFOO_TABLEBAR">
          <ssdl:End Role="TABLEBAR" Type="My.Namespace.Entities.Store.TABLEBAR"
            Multiplicity="1" />
          <ssdl:End Role="TABLEFOO" Type="My.Namespace.Entities.Store.TABLEFOO"
            Multiplicity="*" />
            <ssdl:Principal Role="TABLEBAR">
              <ssdl:PropertyRef Name="ID" />
            <ssdl:Dependent Role="TABLEFOO">
              <ssdl:PropertyRef Name="BAR_ID" />

Stored Procedures can also be included in the Storage Model:

        <ssdl:Function Name="SP_BAR_FROM_FOO" Aggregate="false" BuiltIn="false" 
          NiladicFunction="false" IsComposable="false" 
          ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
          <ssdl:Parameter Name="FOO_ID" Type="int" Mode="In" />
          <ssdl:Parameter Name="BAR_ID" Type="int" Mode="InOut" />

The end of the Storage Model section