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.

Expression vs. Func for Data Selection

So it happened like this...

I built a DataContext object and wanted a generic selection function called Get that would allow me to pass in the type-safe query arguments like this:

'// Pseudo-code
Get( Patient Where Patient.ID == ? )

That's cool, right? Because then, any Entity can go in the "Patient" spot, and that Entity's Property could go in the "Patient.ID" spot, and any reasonable value could go in the "?" spot.

So first, because I want to define the Entity that the get is for, I have to use a type argument. So the Get signature becomes:

'// Pseudo-code
Get(Of EntityT)(Where EntityT.Property == ?)

Right. Now, how to be able to select any Property on the EntityT we've designated? Like this:

'// Pseudo-code, but becoming more real
Get(Of EntityT)(Func(Of EntityT,Boolean) == ?)
* Func is a delegate that accepts an argument of EntityT type, and returns a boolean.

So far, so good. So, the nice thing is, Func(Of EntityT,Boolean) accepts the query value automatically, because of the nature of it wanting to return a boolean. So this definition will suffice:

'// Real code, but not complete yet...
Get(Of EntityT)(Func(Of EntityT,Boolean))

because it can be used like this:

Get(Of Patient)(Func(x) x.PatientID.Equals(0))

Get method knows because of its signature that the Func will accept an EntityT (in this case, Patient) and return a boolean. So all we do is use x as a placeholder for the EntityT object. Lambda.

See what happened? Because the Get method accepts a Func that returns a Boolean, the statement x.PatientID.Equals(0) will return the True/False-ness of the comparison.

Alright, great, we're done, right? Well, not exactly...

When using this in the DataContext, I would do something like:

Function Get(Of EntityT)(Criteria as Func(Of EntityT,Boolean)) as IQueryable(Of EntityT)
Return CreateQuery("[" + EntityNam + "]").Where(Criteria).AsQueryable

Something is wrong here already. Why do I have to cast what should be an IQueryable(Of EntityT) to an IQueryable? Because I missed something very important.

When I passed in the Criteria Func to the Where clause, Where selected the best overload based on the Criteria argument type. What was the best overload? IEnumerable. The IEnumerable Where accepts a Func. So what's wrong with that?

Well, instead of getting the delayed execution of LINQ by being able to build up my query string, the call to IEnumerable Where was causing the query to execute *before* the Where was applied, meaning that the *entire table data* was being returned in order to satisfy that one Where clause.

I noticed the side effect in performance of the form (gee, ya think?!? I'm lazy loading data from like 7 tables for this form). That means ALL THE DATA from each of the 7 tables was being brought into memory, so a filter could be applied to each of them. Wow. Bummer.

After some thought, I realized that I wanted to control the selection of the overloaded where by providing the correct argument type to Where. I want the IQueryable Where. Looking at the documentation on this, IQueryable Where signature is:

Where(Of TSource)(Expression(Of Func(Of TSource, Boolean))

Notice that?!? It wants an *Expression* of a Func. Of course! Why did I not realize that already?!?

The whole point of IQueryable is to get that delayed execution, so you can build up your query before delivering it to the store. Because I was not providing an Expression, I was instructing the query to execute, then apply my Func parameter.

So my final Get signature ended up being:

Get(Of EntityT)(Expression(Of Func(Of EntityT,Bool))

It is exactly the same as the IQueryable Where signature, took me long enough to get there!

By the way, the form load time is just a *fraction* of what it was! Beautiful!

State Pattern

A really helpful article on the State Pattern - those Los Techies really have it under control.

DataGridView Adds Extra Columns

The AutoGenerateColumns property is not available on the design surface of VS, only in code. What a pain. So, to prevent columns from displaying:

Public Sub New() '// or whatever
my_data_grid_view.AutogenerateColumns = False

End Sub

RhinoMocks and InternalsVisibleTo

Well, when I first found this tip, I thought the name DynamicProxyGenAssembly2 was, you know, like a placeholder for my assembly name. But no, it actually means exactly what it says. This is the full explanation.

MenuStrip - Items Not Visible

Yes - YES. This is what I was looking for. When I added a MenuStrip to an MDI application, none of the menu items were showing up. That's because I had to set the AllowMerge property on the MenuStrip to False.

Granted, I don't know what that means yet, but I know how to solve the issue now...