Entity Framework CTP5 - How to Call Stored Procedure?

Question

This may be a simple answer, but i can't see how to execute a stored procedure with EF CTP5.

In Entity Framework 4.0, we did this:

ExecuteFunction("ContainerName.StoredProcName", new ObjectParameter("Id", id)).

Which is a method on the ObjectContext.

But DbContext has no such method.

How do we call a stored proc? Is it not supported in EF CTP5?

EDIT:

I found this thread, which states you need to do this:

  var people = context.People.SqlQuery("EXECUTE [dbo].[GetAllPeople]");

This raises some concerns:

1) You are now calling a stored prodedure on the set, not the context. Stored procedures should be available context-wide, not tied to a particular entity set. Just like how they are under the "Database" in SQL Server, and not under the "Table".

2) What about complex types? I previously had a complex type being returned from a stored procedure. But now, it looks as though you have to map directly to an entity? That doesn't make any sense. I have many stored procs that return a type not directly represented by an ObjectSet/DBSet, which i can't see how i can pull over.

Hope someone can clear this up for me, because from what i understand so far, i won't be able to upgrade to CTP5.

Butcherbird

Answer 1

You can execute database-wide sql statements like this

using(var context = new MyContext())
{
    // custum sql statement
    var c = context.Database.SqlQuery<int>("SELECT COUNT(*) FROM Employees");

    // returned entity type doesn't have to be represented by ObjectSet/DBSet
    var e = context.Database.SqlQuery<Employee>("SELECT * FROM Employees");

    // stored procedure
    var q = context.Database.SqlQuery<Employee>("GetEmployees");
}

Phantasmal

This answers your first concern. The sp's are available context-wide. DbContext.Database is a reference to the built-in DbDatabase object. This is different from context.People.SqlQuery which operates on the DbSet. – Phantasmal

From my comment: "The sp's are available context-wide.". This should be "Database-wide" since you can query data that isn't related to the context. – Phantasmal