CODEWITHSUNDEEP
c#entity-frameworkstored-procedurescode-first
frennky
frennky

Reputation: 14004

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?

Upvotes: 113

Views: 93063

Answers (5)

divega
divega

Reputation: 6476

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.

Upvotes: 51

JJJCoder
JJJCoder

Reputation: 16986

For .NET Core (EntityFrameworkCore), I have been able to get them working.

Might not be the neatest, but this definitely works.

The migration for adding the stored procedure looks like this:

using Microsoft.EntityFrameworkCore.Migrations;
using System.Text;

namespace EFGetStarted.AspNetCore.NewDb.Migrations
{
    public partial class StoredProcedureTest : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("CREATE PROCEDURE GetBlogForAuthorName");
            sb.AppendLine("@authorSearch varchar(100)");
            sb.AppendLine("AS");
            sb.AppendLine("BEGIN");
            sb.AppendLine("-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.");
            sb.AppendLine("SET NOCOUNT ON;");
            sb.AppendLine("SELECT  Distinct Blogs.BlogId, Blogs.Url");
            sb.AppendLine("FROM Blogs INNER JOIN");
            sb.AppendLine("Posts ON Blogs.BlogId = Posts.BlogId INNER JOIN");
            sb.AppendLine("PostsAuthors ON Posts.PostId = PostsAuthors.PostId Inner JOIN");
            sb.AppendLine("Authors on PostsAuthors.AuthorId = Authors.AuthorId");
            sb.AppendLine("Where Authors.[Name] like '%' + @authorSearch + '%'");
            sb.AppendLine("END");

            migrationBuilder.Sql(sb.ToString());
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql("DROP PROCEDURE GetBlogForAuthorName");
        }
    }
}

I could then call it with the following code:

var blogs = _context.Blogs.FromSql("exec GetBlogForAuthorName @p0", "rod").Distinct();

Later tried getting some of the related data (one to many relationship data e.g. Post content) and the blog came back with the filled Post content as exptected.

Upvotes: 2

Luc Bos
Luc Bos

Reputation: 1732

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);

Upvotes: 8

anon
anon

Reputation: 4618

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")

Upvotes: 65

Mark
Mark

Reputation: 531

    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;
    }

Upvotes: 31

Related Questions