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
- manually reapply your changes after you update the model,
- or reapply the changes with a small ad-hoc program or PowerShell utility using some XDocument or XSLT magic,
- 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:
