Stored Procedures and EF Code First

Question

I would like to use a stored procedure to retrieve entities from a DB, I don't care about tracking changes. I just need all entities be resolved including related ones.

Do I have to use SqlCommand?
What about complex properties, will they be resolved too?
Any other limitations you could tell me about?

Thanks!

score 6 · Accepted Answer · edited May 23 '17 at 12:16

6

General answer about using stored procedures in EF is here so stored procedure in pure EF will not handle navigation properties. The answer also mentioned EFExtensions but that is not available in DbContext API.

To execute stored procedure in DbContext API use:

var entities = context.Database.SqlQuery<MyEntity>(...);

DbContext API doesn't offer any function import like EDMX and ObjectContext API.

edited May 23 '17 at 12:16

Community

1
1

answered Apr 04 '11 at 20:49

Ladislav Mrnka

360,892
59
660
670

I see. But is there a SQL Reader that I can use? – Apr 04 '11 at 21:35
No. It will handle materialization of object internally. – Ladislav Mrnka Apr 04 '11 at 21:41
The question you mentioned is more than one year old. Are you sure it's still the same restrictions in CTP5? – Apr 04 '11 at 21:48
CTP5 is outdated version. Current version is EF 4.1RC but it is just wrapper around EFv4. Moreover EF 4.1RC is simplyfied API which doesn't contains some features. Mapping stored procedures is on of these features. So yes, it is still the same. – Ladislav Mrnka Apr 04 '11 at 21:50
So what can I do? I would like to use EF CF for many retrieval operations in my project, but I have to use SP separately then. Can I use the same connection that EF uses to work with ADO.NET DataReaders ? – Apr 05 '11 at 00:36
@Nazaf - Why do you want to use CF? If you were to use DB first this would be no problem since you can just hook the procedures up to the model. – Tridus Apr 05 '11 at 10:30
Because I need to use clean POCO and fluent API – Apr 06 '11 at 07:02
@Nazaf - You can generate POCOs using DB First or Model First in EF 4.1 pretty easily. You just change the code generator to the new DBContext one they provided and you're good to go. – Tridus Apr 16 '11 at 10:32

score 5 · Answer 2 · answered Apr 04 '11 at 20:52

5

Stored Procedures are not supported in Code First. So yes, you'll have to use SqlCommand or whatever your DB of choice provides if you intend on using Code First.

Stored Procedures ARE supported in DB First or Model First.

answered Apr 04 '11 at 20:52

Tridus

5,021
1
19
19

score 0 · Answer 3 · answered Oct 17 '14 at 09:21

By Looking at database first approach, in auto generated context class it defines stored procedures as a virtual functions, Here I share a function from my project, this stored procedure returns a complex type of question.

public virtual ObjectResult<Question> GetMyInnerQuestions(Nullable<int> id)
        {
            var idParameter = id.HasValue ?
                new ObjectParameter("Id", id) :
                new ObjectParameter("Id", typeof(int));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Question>("GetMyInnerQuestions", idParameter);
        }

I used this in my code first do I can call stored procedures like functions as:

IQueryable<Question> questions = db.GetMyInnerQuestions(id).AsQueryable();

Hope this help

score 0 · Answer 4 · answered Apr 04 '11 at 20:44

0

Try function import: http://msdn.microsoft.com/en-us/library/dd456824.aspx

answered Apr 04 '11 at 20:44

Thomas Li

3,358
18
14

Stored Procedures and EF Code First

4 Answers4

Linked