Adding / Modifying Database objects to EDMX

Entity Framework has the ability to automatically build native commands for the database based on your LINQ to Entities or Entity SQL queries, as well as build the commands for inserting, updating, or deleting data. You may want to override these steps and use your own predefined database objects instead.

Entity Framework lets you override it's built in functionality and use your own predefined database objects. Here in this illustration I am trying to walk you through the way of working with Stored Procedures and Table Valued Functions.

Stored Procedures in Entity Framework:

Database objects like Stored procedures and user-defined functions (UDFs) are represented as functions in Entity Framework. So these will not have any defined entity representation in the EDM designer.

For this example walk through of how to work with Stored Procedures in EF, we will consider overriding EF's Find All Courses for a Student functionality with an existing Stored Procedure called "GetCoursesByStudentId" as below:

• Create new ADO.NET Entity Model (.edmx) by picking "EF Designer from Database" option from Choose Model Contents section, and click "Next"

• On Choose your Database Objects and Settings section, select "GetCoursesByStudentId" stored procedure by navigating to the object as below. Don't forget to check option "Import selected stored procedures and functions into the entity model", and clicck "Finish"

• You will see GetCoursesByStudentId added in Function Imports with new complex type GetCoursesByStudentId_Result in the Model Browser. Whenever you import a stored procedure into a model, it creates new complex type with the name {sp name}_Result by default.

• No need to add a new compex type for the returned data from GetCoursesByStudentId as it returns the same fields defined in Course entity. You can change it by right clicking on GetCoursesByStudentId in function imports and selecting Edit. Check Entities and select Course from dropdown in popup window as shown below:

• You will see the function in the context class for GetCoursesByStudentId as shown below:

• Now, GetCoursesByStudentId function can be called as below to get the result, which is equivalent to
  exec [dbo].[GetCoursesByStudentId] @StudentId=1

using (var context = new SchoolDBEntities())
{


var courses = context.GetCoursesByStudentId(1);




foreach (Course cs in courses)

Console.WriteLine(cs.CourseName);
}

Table-Valued Functions in Entity Framework

Table-valued functions are similar to stored procedure with one key difference: the result of TVF is composable which means results of TVF can be used in a LINQ query.

• Here is how we can include a Table-Valued function to our EDM. Right click on the designer → select Update Model from the Database.

• Expand Stored Procedures and Functions node → expand schema node (dbo schema in our case) → select 'GetCourseListByStudentID' and click Finish. Make sure that checkbox for 'Import selected procedures and functions into the entity model' is checked (this will import the function automatically).

• After you imported the function, you can verify it: Open Model Browser → expand Function Imports → right click on imported function 'GetCourseListByStudentID' → click Edit:

• You can see that EDM has automatically created the complex type GetCourseListByStudentID_Result as a return collection type.

• You can also select an existing entity as a return type if TVF returns the same columns as entity:

• Now, the created Table-Valued function can be used in code as beow:

using (var ctx = new SchoolDBEntities())
{


//Execute TVF and filter result

var courseList = ctx.GetCourseListByStudentID(1).Where(c => c.Location == "City1")

.ToList();



foreach (GetCourseListByStudentID_Result cs in courseList)

Console.WriteLine("Course Name: {0}, Course Location: {1}", cs.CourseName, cs.Location);
}

Modifying / Refreshing Database Objects

Following below steps, we can apply the changes made to the existing database objects:

• Make sure that the connection string used in .edmx is activate by saving config file (even though no changes made to it)
• Open .edmx file and select "Model Browser" option by right clicking on .edmx file, and pick "Update Model from Database" option by right clicking on "Stored Procedures / Functions" option from Model Browser window

• Pick "Refresh" tab from "Update Wizard" window. Select database object by expanding "Stored Procedure and Functions" node to refresh and click "Finish", as below:

With this I am concluding the illustration. Feel free to share your feedback.

Happy Programming !!!