Fixing Error NU1202: Package Microsoft.EntityFrameworkCore.Tools 6.0.1 is not compatible with netcoreapp2.2 (.NETCoreApp,Version=v2.2)

Migrating an AspNetCore web and database application from version 2.2 to current .Net 6, I came across an occasional build error:

error NU1202: Package Microsoft.EntityFrameworkCore.Tools 6.0.1 is not compatible with netcoreapp2.2 (.NETCoreApp,Version=v2.2). Package Microsoft.EntityFrameworkCore.Tools 6.0.1 supports: net6.0 (.NETCoreApp,Version=v6.0) 

The error went away by unchecking the Option “Allow NuGet to download missing packages” under “Nuget Package Manager”, so I did not bother any longer.

Unfortunately, the error returned as soon as I ran an Azure Build Task on the project. The task failed with a simple message

##[error]Packages failed to restore

without any indication what exactly failed, what was to be restored, and, especially, why.

Going through the log file, I found that nuget tried to restore packages for .Net 6 (good), but also für .NetCore 2.2 (bad):

2022-04-06T13:59:20.3799748Z          Restoring packages for .NETCoreApp,Version=v6.0...
2022-04-06T13:59:20.4022386Z          Resolving conflicts for .NETCoreApp,Version=v2.2...
2022-04-06T13:59:20.4605261Z          Resolving conflicts for net6.0-windows7.0...
2022-04-06T13:59:20.5840720Z          Checking compatibility of packages on .NETCoreApp,Version=v2.2.
2022-04-06T13:59:20.5886337Z          Checking compatibility for microsoft.entityframeworkcore.tools-netcoreapp2.2-[6.0.1, ) 1.0.0 with .NETCoreApp,Version=v2.2.
2022-04-06T13:59:20.6014642Z          Checking compatibility for Microsoft.EntityFrameworkCore.Tools 6.0.1 with .NETCoreApp,Version=v2.2.
2022-04-06T13:59:20.6016218Z      1>D:\DevOpsAgents\some\path\Project.Data.csproj : error NU1202: Package Microsoft.EntityFrameworkCore.Tools 6.0.1 is not compatible with netcoreapp2.2 (.NETCoreApp,Version=v2.2). Package Microsoft.EntityFrameworkCore.Tools 6.0.1 supports: net6.0 (.NETCoreApp,Version=v6.0) 

I also noticed that the first lines of the log stated

Info: .NET Core SDK/runtime 2.2 and 3.0 are now End of Life(EOL) and have been removed from all hosted agents. If you're using these SDK/runtimes on hosted agents, kindly upgrade to newer versions which are not EOL, or else use UseDotNet task to install the required version.
[command]"C:\Program Files\dotnet\dotnet.exe" restore D:\DevOpsAgents\some\path\Project.sln

By that time, I had verified multiple times that the projects do not use any obsolete 2.2 packages anymore. Where does the reference come from?

Then I remembered that I had experienced *some *sort of build errors, as stated above, and tried to run a dotnet restore on my development machine, disabling parallel execution and turning verbosity to full diagnostics:

dotnet restore my.project.sln --disable-parallel -v diag > dotnet-restore.log

The output is huuuge, so I piped it to a file.

Searching the generated log file for netcoreapp2.2, I finally located these lines

17:37:29.274 10:4>_GenerateDotnetCliToolReferenceSpecs: (Target-ID:26)
    TaskParameter:ToolFramework=netcoreapp2.2 (Task-ID: 17)

I found the target _GenerateDotnetCliToolReferenceSpecs in the file C:\Program Files\dotnet\sdk\6.0.101\NuGet.targets, and it sets the DotnetCliToolTargetFramework build variable to a default value if it is not set already. The default value may be, depending on your installation, netcoreapp1.0, netcoreapp2.2, or some other version.

Which part of the project would trigger an action referring to dotnet CLI tools? It’s the EF Core Tools I needed to install for database migrations:

  <ItemGroup>
	  <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.1"></DotNetCliToolReference>
  </ItemGroup>

This project setting causes dotnet to install the referenced tool, but fails to do so because the default tool framework is incompatible.

This observed behavior is documented in this GitHub issue for NuGet:

• The DotnetCliToolTargetFramework will be interpreted by NuGet restore as the maximum target framework that tools can be restored for
• When restoring .NET CLI tools, NuGet will first download the package, and inspect it to see what it targets. It will choose the maximum target framework the package explicitly supports that is less than or equal to the DotnetCliToolTargetFramework. 

This GitHub issue showed me how to set the framework version, so I added to my csproj file the following lines:

  <PropertyGroup>
    <DotnetCliToolTargetFramework>net6.0</DotnetCliToolTargetFramework>
  </PropertyGroup>

and dotnet restore worked without errors.

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

Console.WriteLine(sql);

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 view
HTML (single file)	view	view	view	view view
MediaWiki	view	view	view	view view
ScrewTurn Wiki (V2)	view	view	view
ScrewTurn Wiki (V3)	view	view	view
Word HTML	view	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

@Mode=”InOut”

in SQL Server, but as

@Mode=”Out”

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'">

and

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

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"
 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="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)]" />
 }
}
 </xsl:template>s

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:choose>
   <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:choose>
  <xsl:text> </xsl:text>
  <xsl:value-of select="@Name"></xsl:value-of>
 </xsl:for-each>
)
{
  System.Data.Common.DbConnection conn = base.Connection;
  conn.Open();
  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>
 <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>
 <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>
  else
    par.Value = System.DBNull.Value;
 </xsl:if>
 <xsl:if test="@Mode = 'InOut'">
  par.Value = System.DBNull.Value;
 </xsl:if>
  cmd.Parameters.Add(par);
 </xsl:for-each>
  cmd.ExecuteNonQuery();
 <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;
  else
   <xsl:value-of select="@Name"/> = null;
  </xsl:if>
 </xsl:for-each>
 conn.Close();
 }
 </xsl:template>

End of stylesheet

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

• List of table mappings (including links to table and view documentation generated by dbscript)
• List of table/column mappings
• generated C# Entity Names

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;
  }
#endif
  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);

  System.IO.File.WriteAllText(
    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()));
}

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

System.IO.File.WriteAllText(
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"
    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.

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>