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

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

In this post I’ll follow the simple steps to setup ‘SQL Server Database’ and will create a Table-valued Function, will test it and then will use this in 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 post [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 following key differences between TVFs and Stored Procedures:

• The results of a TVF is composable within 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 above step we have created 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 image and click on ‘Execute’. Remember, 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 only available for Database First workflow and we have database now. So, to use this database in EF, we need to add 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 ‘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 below 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

Find the complete code of my demo.

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.