ADO.Net Entity Framework Tools: Stored Procedures

17 בדצמבר 2007

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.

ADO.Net Entity Framework Tools: Stored Procedures

The new model is now created and we can see that by default, the Post entity is mapped 1:1 to the Posts table.

ADO.Net Entity Framework Tools: Stored ProceduresADO.Net Entity Framework Tools: Stored Procedures 

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.

ADO.Net Entity Framework Tools: Stored Procedures

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.

ADO.Net Entity Framework Tools: Stored Procedures

This will expand the node for the mapping between the Stored Procedures parameters and the entity fields.

ADO.Net Entity Framework Tools: Stored Procedures

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.

ADO.Net Entity Framework Tools: Stored Procedures

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.

 ADO.Net Entity Framework Tools: Create Function Import

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.

ADO.Net Entity Framework Tools: Create Function Import

This will add a new Function Import to the model.

ADO.Net Entity Framework Tools: Create Function Import

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!

Add comment
facebook linkedin twitter email

18 comments

  1. Jeff Ferguson10 במרץ 2008 ב 4:30

    Where does the ObjectContext class name (BlogEntities) come from? I see it in your "Using It In Code" section, but I don't know how you arrived at its name.

  2. zxevil16316 במרץ 2008 ב 11:19

    KDJiG4 Hi from Russia!

  3. Dave Black25 במרץ 2008 ב 23:31

    Hi Jeff,

    The name 'BlogEntities' is a mix of what Guy used for the name of the EDMX file and the name used by the Entity Data Model Generator. The EDMGenerator takes everything before the 'Model' part of the name (in this case it is 'Blog') and then generates your Entity Container and appends the word 'Entities'. So, you end up with 'Blog' + 'Entities' = 'BlogEntities'

    HTH

  4. Jaap9 באפריל 2008 ב 14:05

    Hi Guy,
    Ms is doing good work here. And I like the entity-to-table mapping. I also do like using stored procs as 'the interface to the database'. But I do not like maintaining them. Is there any tool available that can (re)generate the needed CRUD stored procdures. Or is maybe Microsoft thinking about a "generate CRUD stored procedures as a sublayer" functionality in the tools?
    Thanks,
    Jaap.

  5. Florian S.5 באוגוסט 2008 ב 7:28

    Hi!

    Thanks for the blog entry!
    I'm just playing around with calling stored procedures through the Entity Framework and returning Entities worked out of the box.

    But how can I call stored procedures that return e.g. floats, double or integers?

    I can't call them although I created a function import…

    Thanks in Advance!

    Flo

  6. Mathias2 באוקטובר 2008 ב 6:01

    Same on me, if you create function imports with return type other than entities (a scalar value oder nothing), then the function will not be generated and is only shown in the Model Browser.

    Any advice?

    Thanks!

  7. Simon12 באוקטובר 2008 ב 5:18

    Same problem here (can't return scalar or no value). Did anyone manage to find a solution to the problem? Or is this just another Microsoft rush job?

    Thanks

  8. Naresh Jakher3 בדצמבר 2008 ב 4:05

    I have a stored procedure with a parameter which doesn't map to any column in a table. Stored Procedure returns columns from two table . I want to bind the result set of this sp to datagridview how can i achieve this ADO.Net entity Framework.

    Thanks & Regards,
    Naresh Jakher

  9. Adrian Cunningham10 בדצמבר 2008 ב 14:59

    Hi,

    Using a stored procedure to select entities that have associations, how do you get the SP to return the child object. e.g. Sales.Orders

    Regards,
    Adrian.

  10. John Groft15 בדצמבר 2008 ב 9:16

    Thank you for the blog posting.

    I have a stored procedure that returns the results of a query. You could think of it more or less as a parameterized view – except that there's not an actual view that can be used as an entity to map the result set. How would this work?

    I tried manually creating an entity, but it appeared that the entity had to be mapped to a table or view. I'm a little confused on this point.

  11. lalit potdar17 בפברואר 2009 ב 18:41

    plz help me.

  12. Sajid18 בפברואר 2009 ב 7:28

    Hi Guy Burstein & all the bloggers,

    I too have similar problems as its mentioned earlier by rest of the bloggers. Additionally, I am also getting this error message:

    "Procedure or function '' expects parameter '', which was not supplied."

    Even though I am supplying all the necessary parameter(data) required!

    Has anyone have any solution or guide me whats wrong in my SP call.

  13. shimmy19 בפברואר 2009 ב 19:34

    תודה רבה

    תזכה למצוות

    !חולים עליך כחול לבן

    שימי ויצהנדלר
    ביתר עילית ישראל

  14. tomas2 באפריל 2009 ב 0:16

    Hi All,
    I have a question for you. I use stored procedure for insert, update and delete operation and I'am not sure whether EF supporting output parameter in SP. Can you tell me how can I get SCOPE_IDENTITY from SP and map her to my Entity?

    Regards,

    Tom

  15. da28 באפריל 2009 ב 9:01

    in short: do not use entity framework yet…

  16. Paul Anthony19 ביולי 2009 ב 19:43

    For all the guys asking about scalar return types:
    see here.

    http://blogs.msdn.com/meek/archive/2008/03/26/ado-entity-framework-stored-procedure-customization.aspx

    Seems to be a partial answer to performing this with EF.

  17. Jimmy20 ביולי 2009 ב 19:58

    Hi,
    Can anybody tell me, how i can access the BlogEntitites object in the code bhind class of a XAML page? do i need to import some namespace or any other utility work need to be done before i can avail of it.

    I've followed all the above steps and project is compiling sccessfully as well but i can't access "BlogEntities" class or even it is not visible in the intellisense list.

    Thanks

  18. Tim Acheson23 ביולי 2009 ב 13:40

    No problem! It's easy to execute a stored procedure and get the output parameter with Entity Framework:

    http://www.timacheson.com/Blog/2009/jul/entity_framework_stored_procedure_with_output_parameter

    Doing so isn't really the purest way to work with ORM/EF principles, but it's easy enough to do if you want to support a legacy database.

Comments are closed.