How to call Stored Procedure from Entity Framework?

Question

Hi:

I'm trying to use stored procedure in my n-tier Entity Framaework 4. I have created entity model and can see the stored proc from the Model Browser in the Stored Procedure folder. I added the stored proc through functional import. My stored proc has no return type. Now when I'm trying to call the stored proc in the object context (Business Layer ) I can't see the stored proc in the intelisence. Couldn't find a way to call the stored proc. Is ther e any mistake I'm doing?

Thanks in advance for any help.

h_maksuda...

Answer 1

If you are using the base type object context you will not be able to see/use the function import. You have to use the <Something>Entities type that it generates for you to get this.

---

Devlin Liles http://twitter.com/devlinliles http://www.devlinliles.com/ If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 2

Thanks Capscdev for your reply. I'm very new in EF developing environment.

May be it's a stupid answer,  I don't know if mine is base type or not.

You said I have to use <somting> entities type that generates to get this, can you please help me with a sample code.

Thanks in advance.

 

Maksuda...

---

h_maksuda

Answer 3

//You will not get the function import of the stored proc like this.
ObjectContext context = new AdventureWorksEntities();

//You will get it like this
AdventureWorksEntities context = new AdventureWorksEntities();

---

Devlin Liles http://twitter.com/devlinliles http://www.devlinliles.com/ If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 4

Hello Maksuda,

Thanks for your post.

I found a similar thread with yours, and I quoted the reply posted by Ladislav, which is a good expert here:

MVC is in this case absolutely not related. The way how you call stored procedure from EF will be still the same. I guess you want to use stored procedures without actually using entities and linq-to-entities (main EF features), don't you? Generally you need:

• EDMX file (ado.net entity data model) where you run update from database and add all stored procedures you want to use. EDMX file also generates derived ObjectContext and all entities by default.
• Next you must go to Model Browser and create Function import for each procedure. Function import will create method on the derived ObjectContext which will allow you call the stored procedure as any other .net method.
• During function import you will have to create complex type (it can happen automatically) for result set returned from stored procedure.

You also don't have to use function imports at all and you can execute procedures directly by calling either:

• objectContext.ExecuteSqlCommand("storedProcedureName", SqlParameters) for SPs not returning record set
• objectContext.ExecuteStoreQuery<ResultType>("storedProcedureName", SqlParameters) for SPs returning record set. ResultType must have properties with same names as columns in result set. It can work only with flat types (no nested objects).

There are some limitations when using stored procedures:

• Entity framework doesn't like stored procedures which returns dynamic result sets (based on some condition result set has different columns)
• Entity framework doesn't support stored procedures returning multiple result sets - there are EFExtensions which does but it is more like doing ADO.NET directly.

If you want to know more information, please read this thread.

Hope this can help.

 

Good day,

---

Jackie Sun [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 5

Can u give some link of video tutorial