Mapping Table-Valued Functions (TVFs) Using Entity Framework Database First

Entity Framework 5 brings many improvements and one of them is Mapping Table-Valued Functions (TVFs) Support.

In this article I'll follow the simple steps to set up a "SQL Server Database" and create a Table-valued Function then test it, then use it in a Console Application with Entity Framework Database First Workflow. Please note, TVFs are currently only supported in the Database First workflow.

Step 1: Setup SQL Server Database & TVF

I recommend you to read this article [http://tinyurl.com/8hh8bdr] before proceeding. In the image given below, I've create a "College" Database and a "Students" table and then a "Table-valued Function" by name "GetStudentName" that will accept "Id" as a parameter and will return matching records in Tabular form.

There are the following key differences between TVFs and Stored Procedures:

• The results of a TVF is composable within a LINQ Query while the results of a Stored Procedure cannot.
• Table-valued Functions can return only a single result set in Tabular form while Stored Procedures can return multiple result sets.

Step 2: Testing TVF

In the above step we have created a TVF and now in this step we will test it before consuming it in any application. Create a new query and type the code as given in the following image and click on "Execute". Remember that we need to pass a parameter, that's why I have written "GetStudentName(1)" here "1" is my parameter.

Step 3: Create a Console Application

Create a new console application File > New > Project > Visual C# > Console Application.

Step 4: Add ADO.NET Entity Data Model

As we know, TVF in EF5 is only available for a Database First workflow and we now have a database. So, to use this database in EF, we need to add an ADO.NET Entity Data Model.

1. Right-click the project name in Solution Explorer, point to Add, and then click New Item.
2. Select Data from the left menu and then select ADO.NET Entity Data Model in the Templates pane.
3. Enter Model1.edmx for the file name, and then click Add.
4. In the Choose Model Contents dialog box, select Generate from database, and then click Next.
5. Click New Connection.
6. Select Server name & Database Name and click OK.
7. In the Choose Your Database Objects dialog box, under the Tables node, select the "Students" tables and select the "GetStudentName" function located under the Stored Procedures and Functions node. Click Finish.

Note: By default, the result shape of each imported stored procedure or function will automatically become a new complex type in your entity model if you have multiple entity models (tables). In my case, there is only one entity model, so no worries and avoid the following instructions (in light black color).

1. In case if you want to map the results of the "GetStudentName" function to the "Students" entity, do this:
2. Right-click the design surface and select Model Browser.
3. In Model Browser, select Function Imports, and then double-click the "GetStudentName" function.
4. In the Edit Function Import dialog box, select Entities and choose "Students".

You will have following structure:

Step 5: What is Composable?

Composable means, results from a TVF can be used in a LINQ query while the results of a stored procedure cannot. That's cool.

Step 6: Complete Code

The complete code of my demo is:

using System;

using System.Linq;

namespace ConsoleApplication5_TVF

{

    class Program

    {

        static void Main(string[] args)

        {

            using (var context = new CollegeEntities())

            {

                var Id = 1;

                var student = from s in context.GetStudentName(Id)

                               select new { s.Name };

                foreach (var result in student)

                {

                    Console.WriteLine("Student Name is " + result.Name);

                }

                Console.ReadKey();

            }

        }

    }

}

Output:

Student Name is abhimanyu

I hope you like it. Thanks.