Compiled Type-Safe Databindings in ASP.Net

If you have a ListView whose DataSource is set to a collection of typed objects, then the Eval() method will use reflection to access the object properties for each processed record. (This does not only apply to ListViews, but to any container capable of handling data bindings in templates)

Since we know what type of data will be displayed in the ListView, we do not need reflection, but can directly address the object underlying the current data item:

protected Foo CurrentItem(object Container)
{
    return (Container as ListViewDataItem).DataItem as Foo;
}

This allows the usual Eval() notation as in

<asp:Label runat="server" Text='<%# Eval("Bar") %>'>

to be replaced by

<asp:Label runat="server" Text='<%# CurrentItem(Container).Bar %>'>

While this works in principle, what is still missing is that the compiler throws an error if the databinding expression is invalid. This is due to the fact that ASP.Net compiles databindings only when the page is requested.

To force the compiler, instead of the runtime, to compile databindings, we need to add aspnet_compiler as a post-build event (thank you StackOverflow):

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_compiler.exe -v / 
    -p "$(ProjectDir)\"

and set the “Run the post-build event” option to “On successful build”.

(Note: .Net 3.5 also uses the 2.0 compiler, so no need to change the path)

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"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx"
    xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/
				EntityStoreSchemaGenerator"
    xmlns:ssdl="http://schemas.microsoft.com/ado/2006/04/edm/ssdl"
    xmlns:cs="urn:schemas-microsoft-com:windows:storage:mapping:CS"
    xmlns:edm="http://schemas.microsoft.com/ado/2006/04/edm"
    xmlns:a="http://schemas.microsoft.com/ado/2006/04/codegeneration"
    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="/">
  <xsl:apply-templates 
    select="edmx:Edmx/edmx:Runtime/edmx:StorageModels/ssdl:Schema" />
</xsl:template>
<xsl:template match="ssdl:Schema">
<html>
  <body>
    <table width="100%">
      <xsl:apply-templates select="ssdl:EntityType" >
        <xsl:with-param name="namespace" select="@Namespace" />
      </xsl:apply-templates>
    </table>
  </body>
</html>
</xsl:template>

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" 
        select="/edmx:Edmx/edmx:Runtime/edmx:Mappings/cs:Mapping/
          cs:EntityContainerMapping/
          cs:EntitySetMapping[cs:EntityTypeMapping/
                            cs:MappingFragment/@StoreEntitySet=$table]" />
      <xsl:variable name="s" select="$map/*/@TypeName" />
      <xsl:variable name="p" 
          select="concat('IsTypeOf(', 
            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)">
      </xsl:variable>
  <tr valign="top">
    <td >
      [[<xsl:value-of select="@Name"/>]]
    </td>
    <td>
      <xsl:value-of select="$class" />
    </td>
  </tr>
</xsl:template>
</xsl:stylesheet>

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

Multi-Version Comparison – Timeline

Results of multi-version comparisons can be displayed as a matrix, where each element displays the number of differences between the versions indicated by row and column. The main diagonal remains empty.

Revised matrix, showing versions in chronological order (instead of reversed order):

If you have available the set of all comparisons, you can also pick out the most relevant (in my opinion): order the versions by date, use only the comparisons between two (chronologically) adjacent versions, and display a timeline of database object changes:

The timeline has a row for each database objects that has ever changed in any of the schemas (= project versions). Symbols in columns indicate whether an object was present in a project version (box), or has changed (create, alter, drop) between two versions.

The symbols will be hyperlinked to existing pages displaying the original information in the database (object in project version or object in comparison).

Calculating Record Size of MS SQL Database Tables

This post introduces a query to determine table record sizes of an MS SQL Server database based on the MSDN article Estimating the Size of a Heap (2008). Record size may not be as relevant in SQL Server 2005+ as in previous versions, as the restriction that a record fit in an 8k page has been dropped in SQL Server 2005.

The query consists of 3 CTEs and a final SELECT:

  • OCols determines the effective maximum length in bytes for each column
  • OColSum counts columns and sums the lengths grouped by table and nullability
  • Bytes adds the values of OColSum based on the formulas in the MSDN article.
  • SELECT lists the Bytes values and calculates the total record length
WITH 
ocols AS (
	SELECT o.object_id, c.column_id, o.name AS oname, c.name AS cname, 
		t.name AS tname, t.max_length, c.is_nullable,
	CASE 
		WHEN t.name IN ('char', 'binary') AND c.max_length >= 0 
			THEN c.max_length
		WHEN t.name IN ('varchar', 'varbinary', 'nvarchar') 
			AND c.max_length >= 0 THEN c.max_length + 2
		WHEN t.name IN ('nchar') THEN c.max_length / 2
		WHEN t.name IN ('bit') THEN 1
		WHEN t.name IN ('decimal', 'numeric', 'float', 'real') 
			THEN c.max_length
		WHEN t.name IN ('sql_variant', 'xml') THEN 0
		WHEN c.max_length = -1 THEN 0
		ELSE t.max_length
	END AS eff_length
	FROM sys.objects o
	INNER JOIN sys.columns c ON o.object_id = c.object_id
	INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
	WHERE o.schema_id = 1
	AND o.type = 'U'
), 
ocolsum (object_id, column_count, is_nullable, eff_length) 
AS (
	SELECT object_id, count(column_id), is_nullable, sum(eff_length)
	FROM ocols
	GROUP BY object_id, is_nullable
),
Bytes AS (
	SELECT o.object_id, o.name, 
		ISNULL(ocolsum.column_count,0) + 
			ISNULL(ocolsumnull.column_count,0) AS NumCols,
		ISNULL(ocolsum.eff_length,0) AS FixedDataSize,
		ISNULL(ocolsumnull.column_count,0) AS NumVariableCols,
		ISNULL(ocolsumnull.eff_length,0) AS MaxVarSize,
		2 + ((ISNULL(ocolsum.column_count,0) + 
			ISNULL(ocolsumnull.column_count,0)  + 7) / 8 ) 
				AS NullBitmap,
		2 + (ISNULL(ocolsumnull.column_count,-1) * 2) + 
			ISNULL(ocolsumnull.eff_length,0) AS VarDataSize
	FROM sys.objects o
	LEFT OUTER JOIN ocolsum 
		ON o.object_id = ocolsum.object_id 
		AND ocolsum.is_nullable = 0
	LEFT OUTER JOIN ocolsum ocolsumnull 
		ON o.object_id = ocolsumnull.object_id 
		AND ocolsumnull.is_nullable = 1
	WHERE o.type = 'U'
)
SELECT name, NumCols, FixedDataSize, NumVariableCols, MaxVarSize, 
	NullBitmap, VarDataSize,
	FixedDataSize + VarDataSize + NullBitmap + 4 AS RowSize
FROM Bytes
ORDER BY name

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:

    <edmx:Mappings>
      <cs:Mapping Space="C-S" >
        <cs:EntityContainerMapping
            StorageEntityContainer="MyNamespaceEntitiesStoreContainer"
            CdmEntityContainer="MyEntities">
          <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:MappingFragment>
            </cs:EntityTypeMapping>
          </cs:EntitySetMapping> 

          <cs:AssociationSetMapping Name="FK_TABLEFOO_TABLEBAR"
            TypeName="My.Namespace.Entities.FK_TABLEFOO_TABLEBAR"
            StoreEntitySet="TABLEFOO">
            <cs:EndProperty Name="TABLEFOO">
              <cs:ScalarProperty Name="ID" ColumnName="ID" />
            </cs:EndProperty>
            <cs:EndProperty Name="TableBar">
              <cs:ScalarProperty Name="ID" ColumnName="BAR_ID" />
            </cs:EndProperty>
            <cs:Condition ColumnName="BAR_ID" IsNull="false" />
          </cs:AssociationSetMapping>

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

End of Mapping section, end of Runtime declarations

        </cs:EntityContainerMapping>
      </cs:Mapping>
    </edmx:Mappings>
  </edmx:Runtime>

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

Multi-Version Comparison

If you want to compare multiple versions of a database schema, for example to get an overview of which table or view was created or dropped in which version, you need to compare each version with every other version, resulting in n*(n-1) or n*(n-1)/2 comparisons, depending on what your requirements are.

In my first steps toward multi-version comparison I created a list editor to assign all the project versions (schema versions) to be compared, and display the result in a matrix containing the number of differences.

This screenshot displays the comparison matrix of dbscript versions 0.91 through 0.98.

In case you wonder why the matrix is not symmetrical: CREATE and DROP have a different number of differences. Whereas a dropped table is a difference of 1, a created table usually also has a primary key, constraints, indexes, which add to the number of differences.

The goal of this new functionality is to also include a display similar to the two-version comparison result, but covering all versions.

It’s not very clear to me yet, so your ideas are welcome 😉

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

    <edmx:ConceptualModels>
      <edm:Schema Namespace="My.Namespace.Entities" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
        <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:AssociationSet>
          <edm:FunctionImport Name="SPBarFromFoo">
            <edm:Parameter Name="FooID" Mode="In" Type="Int32" />
            <edm:Parameter Name="BarID" Mode="InOut" Type="Int32" />
          </edm:FunctionImport>
        </edm:EntityContainer>

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:Key>
            <edm:PropertyRef Name="ID" />
          </edm:Key>
          <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:EntityType>
        <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="*" />
        </edm:Association>

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

<edm:NavigationProperty Name="TABLEBAR" 
  Relationship="My.Namespace.Entities.FK_TABLEBAR_TABLEBLAH" 
  FromRole="TABLEBLAH" ToRole="TABLEBAR" 
  a:GetterAccess="Private" a:SetterAccess="Private" />

The end of the Conceptual Model

      </edm:Schema>
    </edmx:ConceptualModels>

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 -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <ssdl:Schema Namespace="My.Namespace.Entities.Store">
        <ssdl:EntityContainer Name="MyEntitiesStoreContainer">
          <ssdl:EntitySet Name="MY_TABLE_NAME"
            EntityType="My.Namespace.Entities.Store.MY_TABLE_NAME"
            store:Type="Tables" Schema="dbo" />

          <ssdl:EntitySet Name="MY_VIEW_NAME"
            EntityType="My.Namespace.Entities.Store.MY_VIEW_NAME"
            store:Type="Views" store:Schema="dbo"
            store:Name="MY_VIEW_NAME">
            <ssdl:DefiningQuery>SELECT FOO FROM BAR
            </ssdl:DefiningQuery>
          </ssdl:EntitySet>

The foreign keys are listed as Association Sets:

          <ssdl:AssociationSet Name="FK_TABLEFOO_TABLEBAR"
            Association="My.Namespace.Entities.Store.FK_TABLEFOO_TABLEBAR">
            <ssdl:End Role="TABLEBAR" EntitySet="TABLEBAR" />
            <ssdl:End Role="TABLEFOO" EntitySet="TABLEFOO" />
          </ssdl:AssociationSet>
        </ssdl:EntityContainer>

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

        <ssdl:EntityType Name="TABLEFOO">
          <ssdl:Key>
            <ssdl:PropertyRef Name="ID" />
          </ssdl:Key>
          <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:EntityType>
        <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:ReferentialConstraint>
            <ssdl:Principal Role="TABLEBAR">
              <ssdl:PropertyRef Name="ID" />
            </ssdl:Principal>
            <ssdl:Dependent Role="TABLEFOO">
              <ssdl:PropertyRef Name="BAR_ID" />
            <ssdl:/Dependent>
          </ssdl:ReferentialConstraint>
        </ssdl:Association>

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" />
        </ssdl:Function>

The end of the Storage Model section

      </ssdl:Schema>
    </edmx:StorageModels>