We next will create a simple data project, and add our ADO.NET Entity Data Model to the project. The resulting EF model is shown here:
We’ll add a Student class to our project, and encapsulate our student business logic there. Let’s suppose we need a method to return a simple list of names of our students. We might create a simple LINQ to Entities query in the following manner:1) Create an ObjectContext for the query, which points to our EducationEntities class.
2) Use the LINQ syntax to retrieve our student records
3) Enumerate the student records, and add each name to a list of strings.
The resulting code looks like this:
If we decide that we need some overly complex method to retrieve student records, and we would like to use SQL syntax to manage the query, we can create a custom stored procedure, and still leverage Entity Framework to manage the objects. The first step is to create our stored procedure in the database. We’ll name our procedure GetStudentList, and add it to our Entity model using the Update Model from Database command:
If we look at our model in Visual Studio, we will see that we now have a new procedure and a new Function, both named GetStudentList. The function is the method we will use to call our stored procedure.
By examining the properties of the new function, we see that it has created a Return Type of GetStudentList_Result. This gets us close, but what we really want is a collection of Student objects.
If we click on the Return Type property, we can bring up the following property dialog for the function:
By changing the value for the Returns a Collection of selection, we can map the return collection to our Student Entity. Choose OK to save this mapping.
Now with the return collection of our procedure mapped, we can modify our previous code to call the new function:
This allows us to write complex SQL code on the database side, and still leverage Entity Framework for modeling our data.
Modifying Data with Custom Stored Procedures
Using the same data model as before, we will now tackle the problem of modifying data with custom stored procedures. First, let’s look at some simple code for modifying a student:
Here we are retrieving the Student object by ID, and then modifying the name fields before calling SaveChanges() on our ObjectContext. Entity Framework has taken care of the plumbing for us, so out of the box we get this functionality without writing any SQL code.
Now, let’s suppose that we want to add some custom logging to our database, but we don’t want to handle this with our business logic. We can create a logging table, and insert a row every time a student is updated. Because database triggers were created by the devil, we would rather handle this with a stored procedure. Here is the simple code for updating the Student table, and logging the action:
OF COURSE, if this were production code, we would add try blocks and ROWCOUNT value testing, etc., but let’s keep it simple. Notice that the ROWCOUNT value is returned as an OUTPUT parameter. You’ll see that in a second.
Update your data model as before, and you’ll now see a new procedure (MyStudentUpdate) listed:
Right-click the Student Entity Type, and select Stored Procedure Mapping from the menu:
In your Mapping Details window, you will see the following for the Student object:
Select the Update function, and change the value to our new stored procedure:
This will bring up the parameter list for the update function. Verify that each parameter for the stored procedure is mapped to the correct property. Also, we will now indicate the our Rows output parameter is marked as our Rows Affected Paramter. If this return value is 0, Entity Framework will throw an OptimisticConcurrencyException, which we can catch and handle accordingly.
Once we have mapped this procedure, we can now use the same code as before, but instead have our new procedure handle the update, including the logging.
There is one big caveat to this: if you map one of the modifying functions, you will have to map all three. Entity Framework does not allow you to mix methods, so you can’t use a custom procedure for one function, and an automatically generated function for another. It’s fine if you don’t call an unmapped function, but it’s best not to leave yourself open to debugging problems down the road.
