ADO.Net Entity Framework Tools: Stored Procedures
ADO.Net Entity Framework Tools: Stored Procedures
The new ADO.Net Entity Framework Tools CTP contains improvements that allows us to map entities to Stored Procedures in the designer, and create Function Imports. In this post, I show how to do that with the designer.
Creating the Initial Entity Data Model
Create a new project, and add a new Entity Data Model (EDM) for your data store. In this sample I am using the BlogSchema that I've used in previous posts, so I named my model BlogModel.edmx.
In the Entity Data Model Wizard, choose Generate From database option, select the database connection to use, and then select the database objects you want to import to the model. This is where you select the stored procedures to import. In this sample, I selected the Posts table and stored procedure for CRUD operations.
The new model is now created and we can see that by default, the Post entity is mapped 1:1 to the Posts table.
Map Insert, Update and Delete Functions to Entities
The new Mapping Details Pane in CTP2 of Entity Framework Tools has a new button, that lets us map entities to functions.
This buttons opens the Functions Mapping display. In order to map a function to an entity operation (Insert, Update or Delete), choose it from the list of available functions.
This will expand the node for the mapping between the Stored Procedures parameters and the entity fields.
By default, the mapper maps parameters to the entities fields by their names automatically, but if there is not match, you can select a fields manually to apply the mapping.
If the stored procedure updates values we want to write back to the entity, like when the PostID is generated by the database and we need to assign the new value back to the entity, we can use the Result Column Binding, and map the new value to an entity column.
Notice the arrows in the mapping details. You can see that values go from the entity to the parameters, and output parameters go back to the entity.
Map Query Functions (Create Function Import)
In order to map a query stored procedure to a Function Import, locate the query stored procedure in the storage model, and use the Create Function Import options from the context menu.
This will open up a dialog that lets you name the Function Import, and choose a return type. In this sample I named the Function Import GetPosts and set its return type to be a collection of Posts.
This will add a new Function Import to the model.
Using it in Code
Now that we have all our functions mapped, we can write some code against them.
BlogEntities ctx = new BlogEntities();
// Use the function import to get a list of posts
foreach ( Posts p in ctx.GetPosts() )
{
if (p.Title.Contains("guy"))
{
ctx.DeleteObject(p);
}
else
{
// Update the properties of the posts.
p.Body += " was recently edited";
}
}
// Create a new Post.
Posts newPost = new Posts { BlogID = "bursteg", Title = "New Post", Body = "This is a new post!" };
ctx.AddToPosts(newPost);
ctx.SaveChanges();
In this code, I create a new instance of the ObjectContext (BlogEntities), and use the GetPosts Function Import to get a list of posts. Then I perform my business logic that deletes or updates posts. I add a new post and save my changes back to the store. Notice that the Function Import is mapped to a new method called GetPosts in the ObjectContext level, and the changes I have made to the object states and the logic I wrote here has nothing to do with the fact I am using stored procedures. I write my logic as I would do anyhow, and the only mapping knows about using stored procedures for applying the changes back the store.
Enjoy!