Dan Rigsby – Coding Up Style

One feature that was sorely missing in the first version of Entity Framework was the automated ability to work with stored procedures that didn’t return back entities.  This could have been sprocs that returned void, a scalar value, or some other custom construct. 

Entity Framework v1

In Entity Framework v1 you could create a “Function Import” from these stored procedures types, but there would be no generated code that would give you access to them directly in the ObjectContext object.  You could only access them via pure Entity SQL.

To add a “Function Import” you would just add the sproc to your entity model as your normally would, then right-click on the “Function Imports” in the “Model Browser”, and select “Add Function Import…”.  This would bring up the following screen which would allow you to add your function.

If you selected that the function returned an Entity, then you would be able to access that entity in the ObjectContext. However, if you selected scalar or void, then you would have to manually write some kind of Entity SQL.  It would be nice if these “Function Imports” appeared as operations in the ObjectContext as well.

Entity Framework 4.0 Solution

Microsoft finally completed the story for “Function Imports” in Entity Framework 4.0. As you can see in the image below, the “Add Function Import” dialog is virtually the same except for the new option to return a complex type.

You can select the None and Scalar return types as you could before.  However, when the “Function Import” is created, some new code is injected into the Model code behind file that materializes the stored procedure into an operation on the ObjectContext itself:

public ObjectResult<Nullable<global::System.Int32>> GetProductCountByCategoryId(

    Nullable<global::System.Int32> categoryID)

{

    ObjectParameter categoryIDParameter;

    if (categoryID.HasValue)

    {

        categoryIDParameter = new ObjectParameter("CategoryID", categoryID);

    }

    else

    {

        categoryIDParameter = new ObjectParameter(

            "CategoryID", 

            typeof(global::System.Int32));

    }

    return base.ExecuteFunction<Nullable<global::System.Int32>>(

        "GetProductCountByCategoryId", 

        categoryIDParameter);

}

A couple of nice things about this are:

1. No matter what we select as the return type, it wraps it as a Nullable type since a database value can always be null
2. The results are wrapped in an ObjectResult which makes it consistent with all other queries.

And as we expect, we can directly access these operations in our code:

using (var context = new NorthwindEntities())

{

    ObjectResult<int?> result =

        context.GetProductCountByCategoryId(1);

    MessageBox.Show(

        result.FirstOrDefault().Value.ToString());

}

New “Execute” operations

If you look at the last line of the code generated by “Add Function Import”, you might notice is making a call to a new method called ExecuteFunction<T>.  This method isn’t marked public, but there are two new methods on ObjectContext that we can use to execute functions:

1. int ExecuteStoreCommand

   This method seems to work like “ExecuteCommand” and returns in int representing the result

2. T ExecuteStoreQuery<T>
   
   This appears to be more of a combination of “ExecuteScalar”, “ExecuteNonQuery”, and “ExecuteReader”.

These methods give us the ability to run any imported stored procedure in a variety of ways.  Most users, will want to just stick with the generated operations, but its always good to have options!