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