Entity Designer opens edmx file only once, and other VS bugs

I found that Entity Designer in Visual Studio 2008 has some strange bugs.

→ One issue is that the installation of Visual Studio can open an .edmx file only once. Double-clicking on the edmx in the Solution Explorer after closing the file will simply expand and collapse the edmx node, but the file does not open. (Strangle, a fellow programmer never experienced this behavior on his machine).

But, I’m not alone, and somebody else already described a solution for the bug:

Right-click the edmx file, choose Open with… and select XML Editor to open the file. Close it, then the Entity Designer will open the next time (once).

→ Another issue is that VS freezes if you open the edmx in Entity Designer and navigate through it when the file is not checked out from TFS.

→ And then I wonder why, as Entity Designer implements auto-routing of foreign keys, it sometimes loses some connection points, and the connections/relations end somewhere in the diagram, instead of ending at the referencing entities, and the lines are criss-cross instead of horizontal or vertical.

→ And if you delete an entity from the diagram, the underlying object still remains in the edmx, and there is no way the Designer allows you to add the deleted entity. You either have to re-create it manually, which is cumbersome.

The solution I found was to rename the object in the database, execute Update Model so that Designer drops the object, rename the object back to its original name, and Update Model again.

→ And if you create a function mapping for a stored procedure, and define the return type as any of the entities, then the stored procedure needs to return a SELECT with the *mapped column names* instead of the original datamodel column names. In my opinion, this behavior breaks the concept of “mapping”.

→ And if you create a function mapping for a stored procedure with a SELECT that is not mappable to an entity, or returns no result or an integer result, you won’t find the mapped function in your C# code, because the generator does not create any code for it. The same is true for user defined SQL functions.

(Actually I found a solution for the missing stored procedure code generation, which I will post here soon)

Some of these issues can also be found on Connect. If you have a solution, please let me know.

Certain things will always remain a mystery to me

Shared aspx and ascx Files in Visual Studio and TFS

In a Visual Studio (2008) project, we have two web applications that should share some aspx pages and ascx controls.

I found this tutorial Creating and Using User Control Libraries, which works in principal, but has left TFS integration out of the solution.

You start with two (or more) applications which have common elements. First create another Web Application Project and create a new folder named “shared” (or “common” or whatever your preferred name is).

The directory hierarchy under “shared” in the common project should be the same as expected by the referencing projects.

Now move the common files into this folder. Make sure you rename the namespace declarations in the moved files to the new web application’s namespace. Check the files in TFS.

Delete the copies of the common files from the referencing projects. After updating from TFS (Get Latest Version), make sure the files are really deleted in the file system. (I had several occurrences where deletions were not executing in the directories)

In the referencing projects, add as Pre-build event command line the following lines:

xcopy /s /y "$(SolutionDir)shared\*.ascx" "$(ProjectDir)"
xcopy /s /y "$(SolutionDir)shared\*.aspx" "$(ProjectDir)"

Adjust the path after $(SolutionDir) to match the path to the common project. You may also use $(ProjectDir) as base source path, and ..\ for parent directories.

Save the project, check in all pending changes, and build, which will execute the pre-build events, but will (most likely) fail, since you are still missing some files.

Next, use Project, Show All Files to display files in the Solution Explorer which are not part of the project.

Find the xcopied files, right-click each file individually and select Include in Project. This will add the ascx and aspx files in the project, and mark them Added in TFS.

Since we already have the files in TFS in the common project, and simply copy them into the referencing projects during build, we don’t want them in TFS in the referencing projects.

Select the added files in the Pending Changes window (i.e. all files except for the project file), right-click and execute Undo. Thus the files will not end up in TFS.

Check in the project file, and build.

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)

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

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

• xmlns:edm=”http://schemas.microsoft.com/ado/2006/04/edm”
• xmlns:a=”http://schemas.microsoft.com/ado/2006/04/codegeneration”

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

• 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”

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>

One Year devioblog – a Summary

I started this blog one year ago to write about topics that I deal with in my software projects, mostly about MS SQL Server and Asp.Net programming.

Since September 2007, this activity generated 71 posts (I did not realize I was publishing an article about every 5 days!) and 20.000 views (says my stats page).

In this time, I also released 3 freeware programs to the public: SchemaFind, graspx, and SMOscript (downloads here).

From the list of Top Posts, my personal favorites are those about automatically building Visual Studio solutions and automated project releases here, here, here, and here.

Sometimes I also documented software installation procedures if I thought I had run into unusual problems: TRAC, Bugzilla, or GForge.

And occasionally I was simply enjoying working with Visual Studio (2005), SQL Server (2005), and C#.

To be continued…

Building Visual Studio Solutions from Batch File

In my previous post, Building Visual Studio Solutions from the Command Line, I described the basic steps to be performed when one of my projects is to be built completely, or code needs to be generated.

This post gives a more detailed view of what is going on in the batch file. (Each batch file needs to be adjusted to the project it is used for in terms of functionality)

Handle batch parameters

Each function of the batch file can be invoked by a separate parameter, and a list of variables needs to keep track of whether the function is activated:

set dogenc=
... more variables

If no parameters are passed, show the help screen:

if not "%1"=="" goto params

echo.
echo parameters:
echo.
echo genc ... generate C#
... more info
goto end

Parse each command line parameter:

:params
if "%1"=="" goto endparams

if "%1"=="genc" (set dogenc=1& shift & goto params)
... more parameters
if "%1"=="all" (shift & set dogenc=1 & ... & set doall=1 & goto params)

echo unknown parameter "%1"
goto endend

:endparams

Add code to handle the various functions, such as checking, building, publishing, as described in the previous post.

Building Setup Projects

As msbuild does not build Setup Projects, we have to take a different approach:

"C:\path to\Microsoft Visual Studio 8\Common7\IDE\devenv"
    C:\path to\project.sln /build "Debug"
    /project C:\path to\setup.vdproj /projectconfig "Debug"

Generating Database Script

SQL Server 2000 provides a simple tool to create a SQL script with the definition of all database objects called scptxfr.

"C:\path to\Microsoft SQL Server\MSSQL\Upgrade\scptxfr"
    /s [server] /d [database] /p [password for sa] /f [filename] /a /h

Zip All

Finally, source code, executables, generated scripts and so on are zipped into version-specific archives using 7-zip, which can be controlled from the command line.

Summary

The batch file for my first project of this kind provides the following functionality:

• generate table triggers based on metamodel information
• generate C# constant definitions
• build executables (web, service)
• publish web applications
• build setup projects
• backup database (both as backup and as CREATE script)
• generate wiki information for developers
• generate wiki page stubs for online help
• create version-specific zip files of everything

Building Visual Studio Solutions from the Command Line

Sometimes there are solutions that are technologically so simple that you don’t even think about them.

I develop a couple of projects, some of which are rather complex to build, as they require to generate code from database content.

Build tools can be complex and have a steep learning curve, Powershell might be an overkill since it’s useful if you handle .Net objects and containers, and I found it amazing what you can achieve with a little batch file.

This is a sketch of a project-specific build script:

First, declare log files and required tools. I found tee.pl to both display output on screen and write it to a log file.

set log=C:\path to build\build.log
set tee=perl C:\path to script\tee.pl
set osql=osql -S host -U user -P password -d database -n -w 1000

echo. > %log%

Next, check ASP.Net code for XHTML compliance and other criteria using graspx:

pushd \inetpub\wwwroot\path to web
(call ..\check2) | %tee% %log%
popd

One of my projects uses a metamodel to generate triggers, so generate a T-SQL script to drop and create triggers, and run that script using osql (SQL 2000) or sqlcmd (SQL 2005):

echo generating database stuff from metamodel | %tee% %log%
%osql% -Q "exec Generate_SQL_Code" -o sqlcode.sql
echo run generated code | %tee% %log%
%osql% -i sqlcode.sql | %tee% %log%

Next, generate C# const declarations from database content. (I find it’s a good idea to keep generated in a separate directory):

echo generating Consts.cs | %tee% %log%
%osql% -Q "exec dev_Generate_Consts" -o "C:\path to project\Generated\Consts.cs

The C# source code now matches the constants defined in the database. So we can now initialize the VS environment and build the project:

setlocal
call "C:\path to\Microsoft Visual Studio 8\VC\vcvarsall.bat" x86
pushd c:\temp
msbuild "C:\path to solution\project.sln"
popd
endlocal

If we build a web project, delete the PrecompileWeb directory that msbuild created:

rmdir /s /q "C:\path to\Visual Studio 2005\Projects\project\PrecompiledWeb"

Finally, update the online help wiki to include new aspx pages:

call createwiki.cmd

What have we got now?

• checked the source code
• generated database objects
• generated C# const definitions
• built VS projects

Next steps are backing up the development database and publishing the application.

Publishing ASP.Net Applications from the Command Line

Visual Studio 2005 has a built-in Publish command which generates a set of (more or less) randomly named DLLs, instead of a single DLL that was generated back in the good old days of ASP.Net 1.1.

An add-on, the VS 2005 Web Deployment Projects, offers the functionality to generate a single assembly for the web application, among other useful things, and includes the aspnet_merge tool.

The publishing of an application usually always takes the same steps with the same options, and can take a couple of minutes (depending on your project). Since repetitive tasks are a source for errors, I wanted to automate this procedure.

I wanted to take a look under the hood and create a batch file which compiles a web application from source files using aspnet_compiler and then calls aspnet_merge.

The first problem I met was that aspnet_merge was not in the PATH. Rather it is being installed into

C:\Program Files\MSBuild\Microsoft\WebDeployment\v8.0

which is a localized path, so you either change the PATH variable, or copy aspnet_merge into a directory in the PATH.

I created a batch file called publish.cmd, which looks like this:

if "%1"=="" goto errnoparam
if not exist c:\inetpub\wwwroot\%1\nul goto errnotfound

setlocal
call "C:\Program Files\Microsoft Visual Studio 8\VC\vcvarsall.bat" x86
echo on 

set base=c:\archive\publish\%1
if not exist %base%\nul md %base%
rd /s /q %base%

echo compiling to %base%
aspnet_compiler -v /%1 -f -d %base%
aspnet_merge %base% -w %1 -debug 

endlocal
echo web compiled to %base%
goto end

:errnoparam
echo usage: %0 web
goto end

:errnotfound
echo web %1 not found
goto end

:end

First, the script checks whether the name for the web application is provided, and whether it is a valid existing name. Next, the VS2005 environment variables are initialized.

Finally it calculates the name of the output directory, and invokes aspnet_compiler and aspnet_merge.

The result:

• beautifully named DLL files
• empty .aspx and .ascx files (so nobody steals your markup )
• ugly .compiled files in the bin directory (cannot be deleted without breaking the application)