Does Entity Framework Code First support stored procedures?

I've watched several presentations of EF Code First and haven't seen how EFCF works with stored procedures.

How can I declare a method that will use some sp? Can I pass an entity to a method that calls sp without manually mapping entity properties to sp parameters?

Also, what happens if I change my model? Would it drop my sp while recreating table from model? And what about triggers?

If these things are not supported, are there any plans to support them in future?

Solution 1:

EDIT: My original answer for EF4.1 (below) is now out of date. Please see the answer below from Diego Vega (who works on the EF team at Microsoft)!

@gsharp and Shawn Mclean: Where are you getting this information? Don't you still have access to the underlying ObjectContext?

IEnumerable<Customer> customers = 
    ((IObjectContextAdapter)this)
    .ObjectContext.ExecuteStoreQuery<Customer>("select * from customers");

Replace the "select" statement with a stored proc, and there you go.

As for your other question: Yes, unfortunately your s.p.'s will get clobbered. You may need to add the "CREATE PROCEDURE" statements in your code.

For EF 4.2:

var customers = context.Database.SqlQuery<Customer>("select * from customers")

Solution 2:

Update: From EF6 on, EF Code First does support stored procedure mapping for inserts, updates and deletes. You can specify stored procedure mapping during model creation using the MapToStoredProcedures method. We also support automatic scaffolding of basic stored procedures for those operations. See the feature specification here.

Original answer: We won't have support for mapping stored procedures in the model in Code-First in the first release, nor we will have a way to automatically generate stored procedures for CRUD operations from your types. These are features that we would like to add in the future.

As it was mentioned in this thread, it is possible to fall back to ObjectContext but DbContext also provides nice APIs to execute native SQL queries and commands (e.g. DbSet.SqlQuery, DbContext.Database.SqlQuery and DbContext.Database.ExecuteSqlCommand). The different SqlQuery versions have the same basic materialization functionality that exists in EF4 (like ExecuteStoreQuery: http://msdn.microsoft.com/en-us/library/dd487208.aspx).

Hope this helps.

Solution 3:

    public IList<Product> GetProductsByCategoryId(int categoryId)
    {
        IList<Product> products;

        using (var context = new NorthwindData())
        {
            SqlParameter categoryParam = new SqlParameter("@categoryID", categoryId);
            products = context.Database.SqlQuery<Product>("Products_GetByCategoryID @categoryID", categoryParam).ToList();
        }

        return products;
    }

    public Product GetProductById(int productId)
    {
        Product product = null;

        using (var context = new NorthwindData())
        {
            SqlParameter idParameter = new SqlParameter("@productId", productId);
            product = context.Database.SqlQuery<Product>("Product_GetByID @productId", idParameter).FirstOrDefault();
        }

        return product;
    }

Solution 4:

A more type safe solution would be this:

http://strugglesofacoder.blogspot.be/2012/03/calling-stored-procedure-with-entity.html

The usage of this class is:

var testProcedureStoredProcedure = new TestProcedureStoredProcedure() { Iets = 5, NogIets = true };

var result = DbContext.Database.ExecuteStoredProcedure(testProcedureStoredProcedure);