Aug 222012
 

I started working for a new company a few weeks ago, and I have been spending a lot of time just getting used to the new portfolio and all of the new techniques and conventions in use there.

One of the things I wasn’t used to is the heavy use of stored procedures that act as custom getter methods within the DAL layer of the .NET applications. The DAL is code generated and the codegen templates automatically know to transform those sprocs into getters on the generated entity manager. So if I have a Customer table, and a sproc in the database called sp_Customer_SearchByName, the codegen process will automatically create a Customer entity, along with a CustomerManager that has a SearchByName method on it, which takes the same inputs as the sproc and returns a collection of Customers.

One of the things I’m looking at is whether Entity Framework makes sense for this portfolio, and I wasn’t sure how EF would handle this type of situation. After pleading ignorance, I was told the magic keyword that I needed: Function Imports!

Let’s assume we have the following objects in a database:

CREATE TABLE Customer
(
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(200) NOT NULL
)
 
CREATE PROCEDURE sp_Customer_SearchByName
(
    @Name VARCHAR(200),
    @LIMIT INT = 20,
    @TotalRows INT = 0 OUTPUT
)
AS
BEGIN
    DECLARE @Predicate AS VARCHAR(1000)
    SET @Predicate = '%' + @Name + '%'
 
    SELECT @TotalRows = COUNT(*) FROM Customer WHERE Name LIKE @Predicate
 
    SET ROWCOUNT @LIMIT
    SELECT * FROM Customer WHERE Name LIKE @Predicate
    SET ROWCOUNT 0
END

It is trivial to generate an Entity Framework model for the Customer table, so I won’t bore you with the details. But you do have to include any stored procs in the EF model that you ultimately want to use as an imported function.

Note that this technique works with both the “old” ObjectContext style entities as well as the POCO DbContext style entities. I use POCOs and DbContext in my demo because I consider that to be the superior option.

Once you have the EF model, you can go to the Model Browser window in Visual Studio. Under the EntityContainer, there is a folder for Function Imports. Right-click on that and you can choose to add a new function import.

The function import dialog gives you several options. It lets you name the resulting function that will appear on the DbContext. It also lets you choose what the stored procedure should return. You can map the return value to a scalar, an existing entity in the model, or to a new or existing complex type. In my demo I chose to map the stored procedure to the Customer entity.

At this point, I have a function called CustomerSearchByName on my CustomerEntities DbContext that I can call to return a collection of Customer entities. The code is as simple as this:

var context = new CustomerEntities();
var rowCountParam = new ObjectParameter("TotalRows", typeof(int));
var result = context.CustomerSearchByName("Justin", 2, rowCountParam);

There is one caveat that I will mention. It is a common technique in creating a stored procedure for searching that the result set is limited to an amount dictated by the application (such as in a search screen with paging). But the application calling the stored procedure also needs to know the total rows in addition to returning the limited set. A common solution to this is to include an out parameter in the stored procedure that will give the total row count, which is what I have in my demo. Entity Framework can handle this, and automatically creates an ObjectParameter parameter on the imported function. However, when you get the result from executing the function, the parameter will not be set until the results are enumerated! This confused me for a bit, but a simple ToList() resolved the issue.

I was happy to see that Entity Framework is able to handle the case of mapping stored procedures that return entities directly into the DbContext as a getter method. This is a common pattern for existing database implementations, and it will come in quite handy for shops that use this pattern when adopting EF. Feel free to download my demo code, and get started using function imports right away.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>