Map inserts, updates, and deletes (CUD operations) to stored procedures

[rowanmiller]

Note: Some of the features tracked in this issue could help with using EF Core with database views. However, the feature is not limited to database views and its completion would not mean that every aspect of database view support has been implemented. See #827 for an overview of the areas where EF Core interacts with database views.

---

Feature description

(added by @divega on Sep 4, 2014)

This is a general meta-issue for enabling better support for working with stored procedures in EF Core, with an emphasis on mapping stored procedures to parts of the EF Core model as opposed to using them in an ad hoc manner, which is covered in other issues. There are different aspects of stored procedures support than should be consider separately, e.g.:

• Invoking stored procedures in an ad hoc manner to return entity types (already possible with FromSql)
• Invoking stored procedures in an ad hoc manner to return arbitrary types (covered by Support for querying objects without keys #1862)
• Wrap Migrations calls to create stored procedures on SQL Server with EXEC (see Script-Migration -idempotent creates script with errors #19740)
• Mapping of CUD operations to stored procedures
  • Add optimistic concurrency support to CUD sproc mapping #28435

Backlog

• Flexible navigation mapping #20339
• Reverse engineer/scaffold stored procedures  #15105
• Allow mapping inserts and deletes to stored procedures with TVPs #27975
• Add entity splitting support to CUD sproc mapping #28433
• Add "table" splitting and complex type support to CUD sproc mapping #28434
• Allow to disable auto-transactions for stored procedures mapped to CUD operations #28511
• Ability to specify more facets for sproc parameters and result columns #28436
• Remove old property overrides when a sproc mapping is removed/renamed #28437
• Support configuring parameters with constant values for sproc mapping #28438
• Allow configuring sproc mapping to use parameter names for invocation  #28439
• Allow to configure any property as the return in CUD sproc mapping #28571

Note that for data retrieval stored procedures aren't generally considered composable on the store i.e. it wouldn't normally be possible to reference them in LINQ queries without causing all the further processing to switch to the client (#621 and #622 cover composable functions) but with our compensating LINQ provider it could be possible some day to enable mapping specific LINQ patterns to stored procedures calls, e.g.:

var customerOrders = db.Orders.Where(o => o.Customer == customerId);

Could cause a stored procedure OrdersByCustomerId(int @customerId) to be invoked.

---

API proposal

(added by @AndriySvyryd on May 6, 2022)

modelBuilder.Entity<Customer>()
    .UpdateUsingStoredProcedure(u => u.HasName("modify_cust")
        .HasParameter(b => b.Id, pb => pb.HasName("customer_id"))
        .HasParameter(b => b.Name, pb => pb.IsOutput())
        .HasParameter(b => b.AlternateKey)
        .HasRowsAffectedParameter(pb => pb.HasName("rows_affected")))
    .DeleteUsingStoredProcedure(d => d.HasName("delete_cust")
        .HasParameter(b => b.Id, pb => pb.HasName("customer_id")))
    .InsertUsingStoredProcedure(i => i.HasName("insert_cust")
        .HasParameter(b => b.Id, pb => pb.HasName("customer_id"))
        .HasParameter(b => b.Name)
        .HasParameter(b => b.AlternateKey)
        .HasResultColumn(b => b.Id, rb => rb.HasName("customer_out_id"))
        .HasResultColumn(b => b.Name)
        .HasRowsAffectedParameter(pb => pb.HasName("rows_affected"))
        .SuppressTransactions());

[IlyaTsilikov]

IMHO one of the most useful features of EF 6 is wrapping stored procedure into DbContext-based class methods. So it would be better to include such feature in EF 7 and if it's possible make two improvemtnts:
1). make available EF to generate methods with out-parameters for OUTPUT SQL keyword (and also ref);
2). make EF available to generate async/await verson of such methods.

[BaranyiAndor]

Also a good idea would be to add User Defined Table Type support for Stored Procedure parameters when generating the DbContext-based class methods using the edmx Database first code generator.

[mikary]

Note: Support for stored procedures with client composition on DbSet<T> was added via #1864

[Aristarh71]

Where I can find any example of using stored procedures with client composition on DbSet in entity framework 7 context?

[mikary]

There are a few examples in the functional tests FromSqlSprocQueryTestBase.cs.

The general pattern is to call the FromSql extension with the stored procedure command text and compose on the result:
myContext.Products.FromSql("[dbo].[Ten Most Expensive Products]").Where(...

or command text format string and parameters:
myContext.CustomerOrders.FromSql("[dbo].[CustOrderHist] @CustomerID = {0}", "ALFKI").Where(...

This feature depends on the generic type of the DbSet being a type that can be populated from the columns returned by the stored procedure. There are still some limitations/issues when the composed methods require server side execution (i.e. Include or additional FromSql calls #2420), and there is no support for saving changes to the results at this time.

[Aristarh71]

Code with FromSql is outside of the context class (myContext), am I right?
Are there any plans to support stored procedure mapping in RC?

[rowanmiller]

@Aristarh71 FromSql uses your context, and you can always hide it away in a method on your context:

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    public IEnumerable<Blog> FindBlogs(string searchTerm)
    {
        return this.Blogs.FromSql("SELECT * FROM dbo.SearchBlogs(@p0)", searchTerm);
    }
}

[poke]

Are there any plans yet to provide a way to consume stored procedures without having to set up a real entity type in the model builder? So that we only get the mapping capabilities, but leave out redundant stuff like the whole change tracker? I already add a AsNoTracking() after everything, but the structures are still there even if it doesn’t make sense for the types.

So ideally, I would just like to be able to define “mapping entities” (I have no idea how these are actually called), that don’t do anything except provide the definitions when EF needs to map a result onto that type. This would then also mean that normal entity restrictions would not be necessary (e.g. having no primary key—which is perfectly reasonable for non-tracked items from a stored procedure).

[rowanmiller]

@poke I think that is an interesting idea... it's probably also something you want when you map views (or at lease non-updateable views). We'll keep that in mind when we come to these features.

[Vasim-DigitalNexus]

RE: mapping capabilities/non-updateable views

This would be a great feature, just map to a type, key or no key

In our case we want to hide the keys sometimes for a read-only view of the result

[dealdiane]

+1 for OUTPUT support.

As for a real world example: When doing paging using stored procedures, we need to run 2 queries. One to get the paged entities and another to get the total row count of unpaged entities to build a pager. By passing an OUTPUT parameter, we could reduce this to a single query. We can use both OFFSET and COUNT() OVER in a single query and return both (entities and total # of rows thru the OUTPUT parameter).

This is just one example. I'm sure many will benefit this if implemented.

[divega]

@dealdiane I would recommend filing a separate bug if your main interest is to have output parameters supported in FromSql() queries. Otherwise I am afraid your request is going to be lost in a comment in this bug that is already in the backlog. It would also be great if you could include the actual SQL (I assume it would be a stored procedure) to illustrate your particular scenario better.

[TimPosey2]

+1 for adding Stored Proc Async/Await Support
-1 for out/ref parameters because it would conflict with adding async support.

[eadbhard]

I reverse-enginered an existing SQL Server database and added the model classes and context class to my MVC 6 project. This is my controller constructor, I assume DI will take care of injecting the context (which is registered as a service in StartUp.cs)

public ReportsController(akzent_reportsContext context)
{
this._context = context;
}

Now on a controller method I'm trying this:

IEnumerable results = _context.FromSql("SELECT * FROM dbo.MyFunctionName(@p0)", "something");

But I get this compilation error:

'akzent_reportsContext' does not contain a definition for 'FromSql' and no extension method 'FromSql' accepting a first argument of type 'akzent_reportsContext' could be found (are you missing a using directive or an assembly reference?) Reportes.DNX 4.5.1, Reportes.DNX Core 5.0

I've already added the EntityFramework.Relational 7.0.0-rc1-final package to the project, and in the controller I am

using Microsoft.Data.Entity;

What am I missing?

Thanks for any insight.

[poke]

FromSql does not exist on the database context but on a context’s DbSet. For example as shown in the code above: context.Blogs.FromSql(…).

The reason for this is that EF does need to know the resulting entity type it should materialize from the SQL statement. This also means that you—currently—need to declare entities for your stored procedure result.

[stevensrf1]

a

[stevensrf1]

Does anyone know where I can find source code project to download the uses the FromSQl to call a stored procedure that actually compiles and works,

[poke]

As explained above, you can just call FromSql on the DbSet to materialize a result of registered entities. If you are having problems with that, feel free to open a question on Stack Overflow to ask for help with your specific problem (but remember to include a MCVE).

Otherwise, this issue is used to track the progress for stored procedure progress, so it’s not really the right place to ask for individual usage assistance.

[stevensrf1]

I am using EF Core in a windows class library and not a web class library.
I do not see the FromSQL extension on the entity class referenced by the derived dbcontext
object.
I guess I have to see what other assembly I need to install..

[stevensrf1]

I am looking to call a StroredProcedure not an sql statement. Is there a working code example available for download that show FromSql calling a stored procedure?

[poke]

See the functional stored procedure tests on how to consume stored procedures. As I already said, you need to run it on a DbSet, not on the context or some entity type. And there is no way to call stored procedures other than to execute SQL right now.

[stevensrf1]

I did not say run it from the DBContext. At least I did think I said that.
I said reference to the entity referenced by the DBContext such as
dbContext.entity.FromSQL

The reason I could not find the FromSQL ion the entity was because an extension in an assembly was not loaded.
I used Net.Reflector to search through the various entity framework DLLs used in a Web Application and found the assembly that needed to be load..