Walkthrough: Table-Valued Functions (June CTP)

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 https://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.

Pre-requisites

•            Microsoft Entity Framework June 2011 CTP. Click here to download.</![IF>
• Microsoft Entity Framework Tools June 2011 CTP. Click here to download.
• Northwind Database. Click here to download.

The Database

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]

 (@Oid INT)

 RETURNS TABLE 

 AS

 RETURN 

     SELECT [OrderID],

            [ProductID],

            [UnitPrice],

            [Quantity],

            [Discount]

     FROM   [dbo].[Order Details]

     WHERE  OrderID = @Oid  

Database First Approach

Setting up Project

-
Launch Visual Studio and create a new C# Console application.

• Make sure you are targeting the Entity Framework June 2011 CTP. Please see the EF June 2011 CTP Intro Post to learn how to change the target framework.

Creating a Model

1

-
Add a new ADO.NET Entity Data Model to your project by right clicking on the project and navigating to Add > New Item.

-
In the Add New Item window, click Data, and then click ADO.NET Entity Data Model. Name your model ‘NorthwindModel’ and click Add.

-
Select New Connection… on the Wizard. Under Server name, enter the name of your SQL Server instance (if you installed SQL Express, the server name should be .\SqlExpress)

-
Under Connect to a database, select Northwind from the first dropdown menu.

-
The Connection Properties window should look as follows. Click OK to proceed.

-
Click Next on the Entity Data Model Wizard, then expand the Tables node, then the dbo node. Select the checkboxes next to the Orders, Order Details, and Products tables. Now scroll down to find the Stored Procedures and Functions node, expand dbo, and select the checkbox next to GetDetailsForOrder from the list of functions. Click Finish.

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.

</![IF>

Creating a FunctionImport for the TVF

• • Open the Model Browser by right clicking on the designer and selecting Model Browser:

     

• Find the GetDetailsForOrder function under NorthwindModel.Store:

-
Double-click on the function to bring up the Add Function Import dialog.

-
Click on the Function Import is Composable? checkbox

-
Select GetDetailsForOrderfrom the Stored Procedure/Function Name checkbox.

-
Click the Entities radio button then select Order_Detail from the dropdown. The Add Function Import dialog should now look as follows:

-
Click OK to close the dialog and create the Function Import. You will see it on the Model Browser under the Function Imports node:

Writing the App

1</![IF>

• • Open Program.cs and enter the following code:

 using System;

 using System.Collections.Generic;

 using System.Linq;

 using System.Text;

 namespace NorthwindApp

 {

     class Program

     {

         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

                               select od.Product.ProductName;

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

                 Console.ReadLine();

             }

         }

     }

 }

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:

        Queso Cabrales

        Mozzarella di Giovanni

Done! Press ENTER to exit.

How It Works

EDMX
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" />

   <ReturnType>

     <CollectionType>

       <RowType>

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

       </RowType>

     </CollectionType>

   </ReturnType>

 </Function>

 <!-- 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" />

 </FunctionImport>

.NET Layer
The Entity Framework tools generate a function stub so that the function can be used in LINQ queries:

 [EdmFunction("NorthwindEntities", "GetDetailsForOrder")]

 public IQueryable<Order_Detail> GetDetailsForOrder(Nullable<global::System.Int32> oid)

 {

     ObjectParameter oidParameter;

     if (oid.HasValue)

     {

         oidParameter = new ObjectParameter("Oid", oid);

     }

     else

     {

         oidParameter = new ObjectParameter("Oid", typeof(global::System.Int32));

     }

     return base.CreateQuery<Order_Detail>("[NorthwindEntities].[GetDetailsForOrder](@Oid)", oidParameter);

 }

Code First Approach

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:

-
Open NorthwindModel.edmx and right click on the canvas

-
Click on Add Code Generation Item…

-
Select ADO.NET DbContext Generator V4.2, enter a name for your template, and click Add

Conclusion

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.

Pedro Ardila

Program Manager – Entity Framework

Comments

• Anonymous
  August 31, 2011
  Hi, Thanks for this post, I have just discovered TVFs and it looks great! Is there any plan to include this feature into Code First approach? If yes, when? For the moment, I can not understand the workaround you have proposed: with CodeFirst, there is no edmx file, so how can I do the "Add Code Generation item" step? Should I create a edmx model aside from my POCOs?

• Anonymous
  October 31, 2011
  When will TVFs be supported for Code First?