The official source of information on Managed Providers, DataSet & Entity Framework from Microsoft
The information in this post is out of date.
Visit msdn.com/data/ef for the latest information on current and past releases of EF.
For Table-Valued Functions see http://msdn.com/data/hh859577
Microsoft Entity Framework June 2011 CTP is bringing support for Table-Valued Functions. TVFs are store functions capable of returning table-shaped values. TVFs are similar to Stored Procedures in that they can have procedural code in their body. Unlike stored procedures however, TVFs are composable which means I can use them inside a classic query. In this walkthrough we will learn how to map entities to TVFs using the VS designer. Then we will see how to use TVFs in LINQ queries.
1. After installing the Northwind msi, execute "C:\SQL Server 2000 Sample Databases\instnwnd.sql" against your SQL Server instance.</![IF>
2. Now we will add a TVF which returns the details for a given order. Execute the following SQL Statement against the Northwind database.</![IF>
CREATE FUNCTION [dbo].[GetDetailsForOrder]
FROM [dbo].[Order Details]
WHERE OrderID = @Oid
Setting up Project
Creating a Model
The resulting Model includes three entities named Order, Order_Detail, and Product. There are one-to-many relationships between Order to Order_Detail, and between Product and Order_Detail.
Creating a FunctionImport for the TVF
Writing the App
static void Main(string args)
using (var context = new NorthwindEntities())
var OrderID = 10248;
var MinUnitPrice = 10;
//Retrieve the names of the products in order 1048 with a unit price over $10
var Products = from od in context.GetDetailsForOrder(OrderID)
where od.UnitPrice > MinUnitPrice
Console.WriteLine("Products in order " + OrderID + " with unit price over $" + MinUnitPrice + ":");
foreach (var p in Products)
Console.WriteLine("\t" + p);
Console.WriteLine("Done! Press ENTER to exit.");
In the code above we create a context, then we create variables for the OrderID and the Minimum unit price we are looking for. Note how we can follow the TVF call with a where clause. This demonstrates the composable nature of TVFs. The output of our program is the following:
Products in order 10248 with unit price over $10:
Mozzarella di Giovanni
Done! Press ENTER to exit.
A TVF is represented as a function in the storage layer. The function is marked as IsComposable=”True”, and a ReturnType is specified to be a collection of RowTypes. A FunctionImport is specified in the conceptual layer. Here we specify the parameters’ EDM Types, EntitySet and ReturnType that the function will map to. The mapping layer glues the Function and the FunctionImport together.
<!-- SSDL content -->
<Function Name="GetDetailsForOrder" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
<Parameter Name="Oid" Type="int" Mode="In" />
<Property Name="OrderID" Type="int" Nullable="false" />
<Property Name="ProductID" Type="int" Nullable="false" />
<Property Name="UnitPrice" Type="money" Nullable="false" />
<Property Name="Quantity" Type="smallint" Nullable="false" />
<Property Name="Discount" Type="real" Nullable="false" />
<!-- MSL content -->
<FunctionImportMapping FunctionImportName="GetDetailsForOrder" FunctionName="NorthwindModel.Store.GetDetailsForOrder" />
<!-- CSDL content -->
<FunctionImport Name="GetDetailsForOrder" IsComposable="true" EntitySet="Order_Details" ReturnType="Collection(NorthwindModel.Order_Detail)">
<Parameter Name="Oid" Mode="In" Type="Int32" />
The Entity Framework tools generate a function stub so that the function can be used in LINQ queries:
public IQueryable<Order_Detail> GetDetailsForOrder(Nullable<global::System.Int32> oid)
oidParameter = new ObjectParameter("Oid", oid);
oidParameter = new ObjectParameter("Oid", typeof(global::System.Int32));
return base.CreateQuery<Order_Detail>("[NorthwindEntities].[GetDetailsForOrder](@Oid)", oidParameter);
Entity Framework June 2011 CTP does not include Code First support for TVFs. However, you can use DbContext against your TVFs. You can do this by adding the DbContext template to your model. The steps to add the template are the following:
In this walkthrough we created a TVF in Northwind, then we created a FunctionImport and called the TVF within a LINQ query. If you would like to find out more about TVFs, please read the EF Design post about TVF Support. As always, we look forward to hearing from you so please leave your questions and comments below.
Program Manager – Entity Framework