Table-valued functions represent a useful mechanism to return custom-shaped data or data processed in some custom way. Unfortunately many people does not use them (they even don’t know about them) and instead use classic stored-procedures with wild SELECTs issued at the end (sometimes even in the middle of them).

The catch is however that Entity Framework tools does not support table-valued functions (TVF). If we use the Entity Data Model Wizard on the AdventureWorksLT database, it recognizes stored-procedures and even scalar-valued function but not TVF:

To confirm that look in the Management Studio:

The only scalar-valued function ufnGetSalesOrderStatusText is available for import as well as two store procedures uspLogError and uspPrintError but not two TVFs ufnGetAllCategories and ufnGetCustomerInformation. Note that I strongly discourage you from using the ufn and usp prefixes that this Microsoft sample database uses as it only clatters your identifier space similarly as the infamous Hungarian notation.

To confirm the sad fact even further you can start up the SQL Server Profiler before running the Entity Data Model Wizard and see how it talks to the database -- it issues three queries to the selected database – the first gets all tables, the second returns all views and the third some of the functions and stored procedures through the standard meta-view INFORMATION_SCHEMA.ROUTINES with one of its WHERE conditions: … AND (DATA_TYPE != 'TABLE' OR DATA_TYPE is null).

So any workaround? Yes – the Fuction SSDL element. Let’s make our hands dirty and open the EDMX file. Insert the following element under the Schema element:

<Function Name="GetCategories" IsComposable="false">
  <CommandText>
    SELECT ParentProductCategoryName, ProductCategoryName, ProductCategoryId
    FROM dbo.ufnGetAllCategories()
  </CommandText>
</Function>

WARNING: The current implementation of the EF EDMX generator is flawed. When you update the EDXM through the “Update Model from Database”, the whole SSDL part is regenerated which means that all customizations including our Function element are lost. This is one the biggest complaints from customers and it’s expected that this gets fixed in the VS 2010 SP1. Until then you can either

1. manually reapply your changes after you update the model,
2. or reapply the changes with a small ad-hoc program or PowerShell utility using some XDocument or XSLT magic,
3. or don’t even use the model update function at all (you can update manually or synchronize with the Huagati DBML/EDMX Tools).

Close the EDMX file if you edit it in the Visual Studio and reopen the EF designer. Next create a new complex type for holding the results of the function:

(data types are string, int, string).

Finally import the function as any other stored procedure in the Model Browser:

Note that there is nothing stopping you to return a collection of any of your entities provided your function returns the correspondingly shaped table.

A small test

static void Main(string[] args)
{
    using (var cx = new AdventureWorksLT2008Entities())
    {
        var categories = cx.GetCategories();
        foreach (var c in categories)
        {
            Console.WriteLine("{0} / {1}", c.ParentProductCategoryName, c.ProductCategoryName);
        }
    }
}

confirms we have done it well:

Entity Framework 4 brought a new way of dealing with references – directly through their foreign key value. While it takes off some abstraction, it’s more friendly to common situations in practice (e.g. selecting from drop down lists) and also it enables you to change a reference without having to load either the old referenced entities or the new one. Also note that this feature is optional; you won’t have your model polluted with foreign keys if you do not want.

This brings some new challenges in my scripts and old code dealing with EF metadata. Now I have to differentiate between “ordinary” simple properties and foreign key properties. Fortunately, it’s not that difficult.

You can get the matching foreign key property to a given navigation property by calling the GetDependentProperties() method which returns exactly entity properties which are dependent ones in the relationship. Note that we’re playing in the conceptual model area (which is what I want) and the foreign key column as implemented in the database could have different name. Also note that if there is a foreign key property, the method should return only value. If there isn’t (maybe you unchecked the “Include foreign key columns in the model” option in the Data Model Wizard), the collection will be empty.

And as usual some code for demonstration. It just outputs the list of all navigation properties in all entities and their corresponding foreign key property names:

var cx = new ModelContainer();
var mw = cx.MetadataWorkspace;
var entities = mw.GetItems<EntityType>(DataSpace.CSpace);

foreach (var en in entities)
{
    Console.WriteLine(en.FullName);
    foreach (var np in en.NavigationProperties)
    {
        Console.WriteLine("- navigation property found: " + np.Name);
        if (np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One || np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One)
        {
            Console.WriteLine("-- is to one");
            Console.WriteLine("--- its FK property is " + np.GetDependentProperties().ToList()[0].Name);
        }
    }
}

Every project targeting Microsoft ADO.NET Entity Framework requires creating a conceptual entity data model, a storage model describing physical data source and a mapping between these two. Each of these metadata resides in its own XML file with its own XML schema (CSDL, SSDL and MSL, respectively). Today I want to spend a few words about what possibilities you have regarding deployment of them.

Besides very simple scenarios I suppose that all Entity Framework models will be defined within standalone projects that are part of a bigger solution to preserve modularity and maintainability. Such a project contains one or more .edmx files created and edited in the Visual Studio EDM designer most of the time. An .edmx file is just a blend of CSDL, SSDL, and MSL content together with some data for diagramming support. This file is processed by EntityModelCodeGenerator tool which extracts corresponding .csdl, .ssdl and .msl files. Note that hand crafting these files is a perfectly valid way.

You then write path to the metadata files (.csdl, .ssdl and .msl) as part of your Entity Framework client connection string:

<connectionStrings> 
  <add name="NorthwindEntities" 
  connectionString="metadata=.\Northwind.csdl|.\Northwind.ssdl|.\Northwind.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SERVER\SQL2000;Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=False&quot;" providerName="System.Data.EntityClient" /> 
</connectionStrings>

You have a couple of choices here (most of them have not been fully documented yet). An obvious choice is a physical file system path. The problem with this approach is that if you write a relative path, it is resolved against the current directory which is a very fragile thing. Take for example an ASP.NET web site/project -- the current directory is set up by the process hosting the site and it's usually anything what you don't expect. When you host your site in the IIS, most likely it's the system directory, while when you are debugging the site inside Visual Studio ASP.NET Development Server, it's C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE or something similar.

Better alternative in case of an ASP.NET web site is to use a well known tilde (~) prefix. This option is undocumented as far as I know. Internally the Entity Framework resolves the path using the HostingEnvironment.MapPath method so you may use any virtual path. For example let's suppose that we have a directory called models in the root virtual directory. Then the connection string could look like:

<connectionStrings> 
  <add name="NorthwindEntities" 
  connectionString="metadata=~\models\Northwind.csdl|~\models\Northwind.ssdl|~\models\Northwind.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SERVER\SQL2000;Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=False&quot;" providerName="System.Data.EntityClient" /> 
</connectionStrings>

The last option is to embed the metadata as resources. Let's say that our Entity Framework models (.edmx files) are defined within a project called Models. We can automatically embed the generated .csdl, .ssdl and .msl files as resources by setting the Metadata Artifact Processing property of the conceptual entity model (this is what shows up when you open an .esmx file in the Visual Studio) to Embed in Output Assembly:

However, do not set Build Action in the properties of the .edmx file itself to Embeded Resource nor to Resource, just leave it as Entity Deploy:

Of course, if you craft the files by hand, just embed them into the assembly as usual (Build Action to Embeded Resource for all of them).

Entity Framework client connection string recognizes the res:// prefix for locating metadata in resource manifests. Common syntax is res://assembly/resourcepath. In our case it could be:

<connectionStrings> 
  <add name="NorthwindEntities" 
  connectionString="metadata=res://Model/Northwind.csdl|res://Model/Northwind.ssdl|res://Model/Northwind.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SERVER\SQL2000;Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=False&quot;" providerName="System.Data.EntityClient" /> 
</connectionStrings>

One special note: instead of a specific assembly name you may use an asterisk (*) to force the Entity Framework to search in all referenced assemblies which is quite useful when you have a single ASP.NET web site (not an ASP.NET web project) with .edmx files placed directly in the site -- then the ASP.NET compiler generates many assemblies with generated names so you don't know the name of the assembly your metadata will be part of in advance:

<connectionStrings>
  <add name="NorthwindEntities" 
  connectionString="metadata=res://*/Northwind.csdl|res://*/Northwind.ssdl|res://*/Northwind.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SERVER\SQL2000;Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=False&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

Unfortunately, the tilde (~) and res:// prefix for locating files given as either virtual paths or resources are not any kind of widely universal standard in the .NET framework. Entity Framework parses input and locates files on its own as many other frameworks/libraries and even parts of the .NET framework itself. I think that a standard describing common schemas (prefixes) and a standard component in the .NET framework handling it would be very useful.