Table-Valued Function Support

Article
01/21/2011

One of the key features coming in the next release of Entity Framework is Table-Valued Function support. It is a very popular customer request and we have been working diligently to design a solution that we hope you find simple, yet useful. This design intends to make TVFs first-class citizens on Entity Framework, by allowing a user to map TVFs to both Entities and Complex types.

This article will explore the design of the feature in the framework, so we will focus on the metadata within the EDMX as opposed to the designer experience in Visual Studio. We will cover that experience in a later post. Other topics we will cover in the future include QueryViews and other complex Function Mapping scenarios.

With that said, here are the specific areas we will cover now:

· A basic overview of TVFs; pros and cons vs. Stored Procedures and Views

· How to call a TVF Directly

· Mapping a TVF to a Collection of Complex type

· Mapping a TVF to a Collection of Entity type and performing CRUD on the Entity

We’d love to hear your thoughts, so please feel free to comment and make suggestions about any part of this design.

What Are TVFs?

TVFs are User Defined Functions (UDFs) which live in the target database and whose return type is a table. UDFs are constructs made up of one or more T-SQL statements. In the context of a T-SQL query, TVFs can be used anywhere a View or a Table would be used. Although there is some overlap between TVFs, Views, and Stored Procedures, there are key advantages which make TVFs appealing in certain scenarios. Let’s look at how TVFs stack up against Views and Stored Procedures:

TVFs vs. Views
TVFs and Views are similar in that they are both composable. This means that you can use the function—or the view—anywhere within the body of a T-SQL query. Their major differences are, first, that SQL Views can only have one SELECT statement, whereas TVFs can have multiple statements as well as procedural code. This makes it possible to design more complex logic with TVFs than with Views. Additionally, TVFs can take parameters, whereas Views cannot.

TVFs vs. Stored Procedures
TVFs and stored procedures may both contain procedural code, but unlike stored procedures, TVFs can be used anywhere in a query. This allows us to both compose queries using TVFs and filter the result set of a TVF. One advantage of stored procedures over TVFs is that stored procedures are capable of returning multiple result sets.

Background: TVF declaration and usage in SQL

TVFs allow you to write complex statements and queries, all in the same place. In this post we will use relatively simple TVFs in order to keep complexity low. Please read this article to learn more about TVFs in SQL.

Throughout the post we will use these two TVFs to showcase functionality:

· GetCategories() returns all of the entries in the Categories table.

CREATE FUNCTION [dbo].[GetCategories]()

RETURNS TABLE

RETURN

SELECT [CategoryID]

,[CategoryName]

,[Description]

,[Picture]

FROM [dbo].[Categories]

· GetDetailsForOrder(int) returns the Order Details for a given order for Id equal to Oid.

10.

11.

CREATE FUNCTION [dbo].[GetDetailsForOrder]

(@Oid INT)

RETURNS TABLE

RETURN

SELECT [OrderID]

,[ProductID]

,[UnitPrice]

,[Quantity]

,[Discount]

FROM [dbo].[OrderDetails]

WHERE OrderID = @Oid

Typical usage of a TVF in T-SQL as follows:

SELECT d.ProductID, d.Quantity

FROM dbo.GetDetailsForOrder(10248) AS d

This simple statement returns the category ID and Category Name from our TVF.

ProductID Quantity

----------- --------

11 12

42 10

72 5

Note that we were able to call the TVF in the FROM clause of our T-SQL query, as we would a table. Also note that we were able to pass in parameters, and that the result of the query was a table. This combination of factors, as well as the possibility to have procedural code within your function, is what makes TVFs appealing.

Why Should You Use TVFs in EF?

If you already use TVFs, the advantages of having EF support are obvious: You will now be able to use your TVFs as the source for materializing entity types and complex types via queries in EF – giving you the ability to use LINQ and Entity SQL against them. If you don’t already use TVFs, think about the potential we get from creating Entity or Complex type that is backed by a function instead of an entity. TVF support enables interesting scenarios such as Full-Text search since in SQL, it is implemented using TVFs.

Using TVFs in Entity Framework

Now that we’ve established what a TVF is, let’s see what artifacts we need in order to use one in an EF app:

· Metadata describing the store function and its return type.

· A function stub is required to call the function from a LINQ query. The stub is not required for Entity SQL queries.

These two items are all we need if we want to call the TVF directly in a LINQ or Entity SQL query. However, to truly take advantage TVFs in EF (reusability, change tracking, CUD), we also need to describe the mapping of the function to a Collection of Entity Type or Collection of Complex Type.

TVFs as Functions in SSDL returning RowType

Storage Layer
In order to make the TVF visible to Entity Framework, we must declare the store function in the storage layer (SSDL). We must specify the function name, whether or not it is composable, and the schema it belongs to. Up until version 4 of EF, the Function node was only capable of returning a scalar. Our new design required us to make two changes to this node: First, we can now set IsComposable to true, and therefore we are able to compose functions. Second, the return type is no longer a property of Function, but instead a child node in which we can describe the rich return types produced by TVFs.

Here is how we declare the store function called GetCategories in our SSDL:

10.

11.

12.

13.

</RowType>

</CollectionType>

</ReturnType>

</Function>

Here we specify the ReturnType of the Function. In this case it will be a collection of RowTypes.

Object Layer
We need a function stub to refer to the store function from a LINQ query. The result of calling the function stub is a set of unstructured rows. See this design blog to learn more about function stubs. Here is what the stub will look like:

[EdmFunction("NorthwindModel.Store", "GetCategories")]

public IQueryable<DbDataRecord> GetCategoriesDirect()

{

return this.CreateQuery<DbDataRecord>("[NorthwindModel.Store].[GetCategories]()");

}

This stub will usually reside within your strongly typed ObjectContext class. Here are a few things to take note of:

· The stub returns a collection of DbDataRecord. These records aren’t richly typed and therefore only provide us with limited functionality.

· The EdmFunction attribute is the store’s namespace since we are calling a store function.

· Our function stub contains a meaningful body, as opposed to an exception (see here for an example). This ‘bootstrapping’ allows us to call the function in the FROM clause of our LINQ query. Here is exactly how we would call our TVF:

using (var context = new NorthwindEntities())

{

//Call GetCategories directly using Entity LINQ

var query = from c in context.GetCategoriesDirect()

select c;

foreach (var r in query)

Console.WriteLine("[{0}] {1}", r.GetInt32(0), r.GetString(1));

}

The stub is not required when using Entity SQL for our queries. Here is how to call the same TVF in Entity SQL:

10.

using (var context = new NorthwindEntities())

{

//Call GetCategories directly using Entity SQL

string query = @"Select Category.CategoryId, Category.CategoryName

From NorthwindModel.Store.getCategories() as Category";

var categories = context.CreateQuery<DbDataRecord>(query);

foreach (DbDataRecord category in categories)

Console.WriteLine("[{0}]: {1}", category.GetInt32(0), category.GetString(1));

}

The functionality above is useful, but only to a limited extent since unstructured data records are read only.

Mapping TVF to Complex Types and Entity Types

Function Mapping is a new feature which allows us to map the TVF’s results to a collection of Complex Type as well as a collection of Entity Type. By mapping to an Entity Type, we gain full CRUD functionality against our entities. The first step is to specify the function mapping in the mapping layer.

Result Type Mapping

Below we map a function in the conceptual layer to a function in the storage layer with an explicit return type mapping. Explicit mapping is supported for conceptual functions returning collection of Complex Type or a collection of Entity Type. Using explicit mapping, we describe how each property in the conceptual function will map to a property in the store function. We could use implicit mapping, but only for conceptual functions returning a scalar or a collection of Rowtype with primitive properties.

Collection of Complex Type

A TVF’s result set can map to a collection Complex Type. This is the default behavior when using Model First or EDMGen. Let’s explore this mapping using GetDetailsForOrder:

Storage Layer

10.

11.

12.

13.

14.

15.

16.

</RowType>

</CollectionType>

</ReturnType>

</Function>

Mapping Layer

To tie the function to a collection of Complex Types, we need to create a mapping between our store function and a conceptual function. We do that in the following way:

10.

11.

12.

13.

14.

15.

16.

<FunctionMapping StorageFunction="NorthwindModel.Store.GetDetailsForOrder"

CdmFunction="NorthwindModel.GetDetailsForOrder">

</Parameters>

</ComplexTypeMapping>

</ResultMapping>

</FunctionMapping>

The explicit mapping above describes how to map the columns returned by the TVF to our Complex Type’s properties. It also describes the parameters. In this iteration we will only support parameters of Primitive type.

Conceptual Layer

Finally we need a conceptual function whose return type is a collection of Complex Types. The Complex type and the function look like the following:

10.

11.

12.

13.

</ComplexType>

…

<Function Name="GetDetailsForOrder"

ReturnType="Collection(NorthwindModel.GetDetailsForOrder_Result)">

</Function>

In the code above we declared a complex type then we created a function which returns a collection of such type. EDMGen uses “<FunctionMame>_result” as the Complex Type’s name by default when generating metadata.

Object Layer

Once these are in place we can use call TVF using Entity SQL. However, we need a new function stub to use the TVF in a LINQ query:

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

public IQueryable<GetDetailsForOrder_Result> GetDetailsForOrder(int Oid)

{

return this.CreateQuery<GetDetailsForOrder_Result>("NorthwindModel.GetDetailsForOrder(@Oid)",

new ObjectParameter("Oid", Oid));

}

The noteworthy differences from our first stub are that this one returns a collection of Complex Type, rather than a collection of unstructured records. Also, note that the first parameter in the EdmFunction attribute is the conceptual function, as opposed to the store function.

Usage
Let’s have a look at the basic usage of our function in LINQ and Entity SQL.

10.

using (var context = new NorthwindEntities())

{

int OrderId = 10248;

//GetDetailsForOrder using LINQ

var OrderDetails = from O in context.GetDetailsForOrder(OrderId)

select O;

foreach (var od in OrderDetails)

Console.WriteLine("[{0}] {1}", od.OrderID, od.ProductID);

}

10.

11.

12.

13.

14.

using (var context = new NorthwindEntities())

{

int OrderId = 10248;

//GetDetailsForOrder using Entity SQL

string queryString = @"Select VALUE OrderDetail

From NorthwindModel.GetDetailsForOrder(@Oid) as OrderDetail";

ObjectQuery<GetDetailsForOrder_Result> OrderDetailsQuery =

new ObjectQuery<GetDetailsForOrder_Result>(queryString, context);

OrderDetailsQuery.Parameters.Add(new ObjectParameter("Oid", OrderId));

foreach (var result in OrderDetailsQuery)

Console.WriteLine("[{0}] {1}", result.OrderID, result.ProductID);

}

Both samples do the same. They call GetDetailsForOrder with an OrderId of 10248 and then iterate over the result.

Collection of Entity Type

We can also make our TVF return entities. We get all of the functionality we would expect from entities, such as reading, writing, and navigation provided by using NavigationProperties. EDMGen does not map to Entities by default, but we intend to provide tooling support to create such mapping without having to edit the EDMX.

Storage Layer
The store function definition remains the same as described above.

Mapping Layer
Now we need to describe the mapping between the store function and the conceptual function.

10.

11.

12.

13.

14.

15.

16.

<FunctionMapping StorageFunction="NorthwindModel.Store.GetDetailsForOrder"

CdmFunction="NorthwindModel.GetDetailsForOrderAsEntity" >

</Parameters>

</EntityTypeMapping>

</ResultMapping>

</FunctionMapping>

This mapping describes the TVF’s parameters and maps the function’s result to a collection of Entity types named OrderDetails. Each ScalarProperty node has two properties, Name and ColumnName. Name refers to the EntityType and ColumnName refers to the Store function. Function Mapping supports Table-Per-Hierarchy inheritance, much like in FunctionImportMapping. This topic will be covered in a later post.

Conceptual Layer
When you map a function to a collection of Entity types, you get the following in the CSDL:

<Function Name="GetDetailsForOrderAsEntity" ReturnType="Collection(NorthwindModel.OrderDetails)"

EntityContainer="NorthwindEntities" EntitySet="OrderDetails">

</Function>

This metadata is similar to our previous example. However, when mapping to Entity types, we must list two additional properties in our Function node: an EntityContainer and an EntitySet. These two values must refer to existing artifacts in the conceptual layer.

Object Layer
Here is our function stub for a TVF’s result set mapped to a collection of Entity Types:

[EdmFunction("NorthwindModel", "GetDetailsForOrderAsEntity")]

public IQueryable<OrderDetails> GetDetailsForOrderAsEntity(int Oid)

{

return this.CreateQuery<OrderDetails>("[NorthwindModel].[GetDetailsForOrderAsEntity](@Oid)",

new ObjectParameter("Oid", Oid));

}

Usage
With our mapping in place, we can begin using our Entities in the usual fashion. In the following example we will call our TVF and do some simple update to the resulting Entities:

10.

11.

12.

13.

14.

15.

16.

17.

18.

19.

20.

21.

22.

23.

24.

using (var context = new NorthwindEntities())

{

// Use GetDetailsForOrderAsEntity with LINQ and update entities using ObjectContext

var OrderDetails = from OrderDetail in context.GetDetailsForOrderAsEntity(10248)

where OrderDetail.Quantity > 1

select OrderDetail;

foreach (var item in OrderDetails)

{

Console.WriteLine("[{0}] {1} – Original Quantity={2}",

item.OrderID, item.ProductID, item.Quantity);

item.Quantity = 1;

}

context.SaveChanges();

OrderDetails = from OrderDetail in context.GetDetailsForOrderAsEntity(10248)

select OrderDetail;

Console.WriteLine("New Values");

foreach (var item in OrderDetails)

Console.WriteLine("[{0}] {1} – New Quantity={2}",

item.OrderID, item.ProductID, item.Quantity);

}

In this example we get the products in Order #10248 with a quantity greater than 1. Then we change each quantity to 1. The important thing to note about this scenario is that we were able to query for a specific set of order details –those with a quantity greater than 1—using a TVF. Then we edited the resulting entities to which the TVFs result set was mapped.

Wrap-Up

In this article we have covered:

· Background on TVFs in SQL

· Calling a TVF directly from a LINQ or Entity SQL query

· Metadata for mapping a collection of Complex Type to a TVF, and subsequent usage

· Metadata for mapping a collection of Entity Type to a TVF, and subsequent usage including CUD

TVF support will be a key component of Entity Framework’s next release. Our goal is to provider first-class support for TVFs by allowing users to map the function’s result set to Entity types and Complex types. We look forward to getting your feedback, so feel free to make any comments or ask questions about this design.

Pedro Ardila
Program Manager
ADO.Net Entity Framework Team

Comments

Anonymous
January 29, 2011
The comment has been removed
Anonymous
January 29, 2011
What makes you think there isn't designer support? He states in the post that he is focusing on "..will focus on the metadata within the EDMX as opposed to the designer experience in Visual Studio"
Anonymous
January 29, 2011
@Fred Morrison please read everything before start to complain!
Anonymous
January 29, 2011
Cool! Let's hope SQL azure will one day have FTS support.
Anonymous
January 30, 2011
The comment has been removed
Anonymous
February 01, 2011
This is pretty great. I see massive potential using it for paging scenarios when you want the benefits of complex sql queries along with server side (sql) page trimming. I am assuming this is going to be compatible with skip, take and where? Are you guys considering allowing the use of multiple EF definitions in a single assembly. Since a LOT of the time it is pretty great to treat your EF model as a repository for domain specific designs, which fits in great with the Conceptual Layer concept. Sadly having multiple entities of the same name in different contexts fails. (I am assuming due to the XML resource mapping in the assembly)
Anonymous
February 02, 2011
The comment has been removed
Anonymous
February 06, 2011
We had a business requirement on a project for an .AsOfDate(adate) function for retrieving entities, because we need to the store the history of entity values. TVF would provide encapsulation at the database but at the time did not find a solution to put this together with EF I see there is mention of an analogy to the .Skip and Take implementation. Does this mean we could perhaps see, for example Orders.AsOfDate(x).OrderItems.AsOfDate(y) where the function was an extention to the entity?
Anonymous
March 09, 2011
Skip and Take aren't TVFs, they are treated as query operators that get embedded into the tSQL query as TOP(limit) and ROWNUMBER() > skip. Encapsulating versions of orders and order items in such a way that model is unaware is quite tricky. One way is to map Orders and OrderItems entity sets to corresponding sql views that always return latest version and then define GetOrdersAsOfDate(date) and GetOrderItemsAsOfDate(oid, date) to retrieve older versions.
Anonymous
March 09, 2011
I can't believe it took 4 versions and yet it will still be missing from EF Code First. Without TVFs any ORM against SQL Server 2005 and greater is pretty much useless because you cannot compose your queries utilizing the advance features in SQL Server 2005 and 2008. No FullTextSearch as mentioned, no Spatial, etc. How is it possible that LinqToSql supports them since almost the start and EF does not. How is it possible that a couple of simple query generation bugs in LinqToSql remain unfixed for years thus rendering LinqToSql almost unusable (broken inheritance for example). So many years have passed and none of the two ORMs has the basic functionality covered. Please excuse my little rant. I and others have spent the time to report these issues on Connect and in person years ago, but to no avail. Where (or when) can I download EF vNEXT that has EF Code First support for TVFs? I would greatly appreciate it.
Anonymous
March 10, 2011
When can we expect to see a release (CTP or otherwise) that contains the TVF functionality? Definitely looking forward to this. Thanks!
Anonymous
August 22, 2011
What about .net Framework .0 4 / VS 2010? ReturnType is not recognized with _schemas.microsoft.com/.../ssdl...
Anonymous
August 27, 2011
I would love to see support for wcf ria, silverlight, code first !! Please work with the wcf ria team so we can see this soon!! Thank you for the post
Anonymous
November 01, 2011
Table Valued Parameters, Stored Procs and EF? I'm hoping to do set-based CRUD (inserts, deletes, updates) via passing in IDs, and TVPs (table-value params) with Stored Procedures (or even Entity Framework generated SQL) seems great b/c of the set-based process. I'm curious if Code-First MVC3+EF4.2 can have set-based Stored Procedures?
Anonymous
February 27, 2012
Hi, From what I could tell, this implementation did not work since "ReturnType" is not recognised in the namespace. Is there a MSDN article using either EF4 or EF4.1 with TVF?
Anonymous
March 05, 2012
Hi, I have a scenario where my TVF adds 3 computed columns (based on input param). I'd like to map those to properties of entity which i would add utilizing that entities are defined as partial class. Will that be possible?

Share via

Table-Valued Function Support

What Are TVFs?

Background: TVF declaration and usage in SQL

Why Should You Use TVFs in EF?

Using TVFs in Entity Framework

TVFs as Functions in SSDL returning RowType

Mapping TVF to Complex Types and Entity Types

Result Type Mapping

Collection of Complex Type

Collection of Entity Type

Wrap-Up

Comments

Additional resources