Entity Framework and Custom Stored Procedures

*WARNING*
I didn't want to put this at the end of the post in case you don't get that far, but just be aware...
*If you udpate from the database "for any reason", EF will overwrite your custom CommandText.*
Let that be a lesson to you... Now...

So our database has plenty of valuable business logic in the stored procedures - things that have to happen when, say, we want to save Customer information (yes, I know it shouldn't be there, but it is pre-existing, so what can I do?!?). Entity Framework provided functionality to facilitate this, in the form of Modification Functions. This post isn't about describing what they are, but how I got them to work with our current stored procedures...

OK - so the first thing of note was that many of the operations on business objects for the role we are coding to don't have associated CRUD. For example, if we want to Update (the U in CRUD), it doesn't mean that we should be able to Create (the C), Read (the R), or Delete (the D). But, because of the stored procedure support in EF, if you define one Modification Function for C_UD, you have to provide them all (notice this doesn't apply to Read).

So Modification Function UpdateFunction is set to a real stored procedure:
<UpdateFunction FunctionName="Customer_Update">

while the others are not:
<DeleteFunction FunctionName="usp_NoAction"/>
<InsertFunction FunctionName="usp_NoAction"/>

Alright, so that was pretty easy, even though that had to be hacked together by hand. Now when role calls Customer Insert, it will call usp_NoAction, no harm done.

The next instance was tricky. Many of the stored procedures use ModifiedOn as a sort of concurrency check. Pass in the ModifiedOn that you received, and if no one has updated the record since you grabbed it, the update will continue. In that instance, the ModifiedOn parameter will act as an output parameter, returning the new ModifiedOn date. In addition, the stored procedure may return a RowsAffected integer, telling how many rows were affected. For Insert operations, the stored procedure may return the ID of the newly created row.

So, EF doesn't have the capacity to work with InOut parameter types, even though that is what will be automatically generated in your function import statement. So you have two options: In or Out in the SSDL section of the edmx file.

In is obvious, and works as you would expect. It specifies that the parameter is inbound into the stored procedure. It looks like this (we're leaving out the rest of the definition for the time being):
<Parameter Name="ModifiedOn" Type="datetime" Mode="In"/>

Even though ModifiedOn is an InOut in the procedure, it is declared as an In in the SSDL. We can now pass the value in to it.

OK, we're good so far...

Next is the RowsAffected parameter. It will be declared as an Out, because that is exactly what it is. EF doesn't expect you to pass a value into that mapping, it is an output only.
So now we move on to the CSDL section of the edmx file. Our stored procedure is ready for use as a Modification Function, so we give it a try. Maybe it works, but it doesn't pass the ModifiedOn back to us, because ModifiedOn only works as an In parameter. So our object is now out of date. What do we do?

Here's where CommandText comes in...

CommandText is an element that can be used in the Function element of the SSDL section to pass custom SQL to the database. So we can declare something like this:
<CommandText>
DECLARE @rows_affected int;
DECLARE @modified_on DATETIME;
SET @modified_on = @Modified;
EXEC pfc_Customer_Update
@CustomerID = @CustomerID
, @Modified = @modified_on OUTPUT
, @Adjustment = @Adjustment
, @RowsAffected = @rows_affected OUTPUT;
SELECT @modified_on AS Modified;
</CommandText>

So what does this do for us?

It declares two variables that we are going to use as InOut (or Out) in the stored procedure. Then we set the @modified_on variable to the value of the passed in ModifiedOn property. We use that to execute the stored procedure, then we select the @modified_on variable out after the fact because it will reflect the new ModifiedOn variable. We SELECT that out into a column called Modified.
In the CSDL section, we change the Modification Function to look like this:
<UpdateFunction FunctionName="Customer_Update" >
<ScalarProperty Name="ID" ParameterName="ReferralRx" Version="Current" />
...
<ResultBinding Name="ModifiedOn" ColumnName="Modified"/>
</UpdateFunction>

So that pretty much gets us where we want to be. What that does is use the Modified column (which was set from the @modified_on variable) from the stored procedure (which is now our custom SQL thanks to the CommandText element) and bind it to the ModifiedOn property of the Customer, so that is now updated when the object is saved.

The ID scenario can be solved in pretty much the same way. One note though, your Modification Function declaration can have a RowsAffectedParameter property - which is why I mentioned that above. That property can be set to the @RowsAffected parameter of the stored procedure. Easy. However, I did have an issue where the @RowsAffected parameter was returning 0, even though the row *was* being updated. I assume this was caused by some of the logic in the stored procedure. When the 0 is returned, EF throws an exception. I fixed it by removing the RowsAffectedParameter property from the Modification Function declaration.