EF Core - What is MapToStoredProcedures replacement in EF Core 3.1 or 5

Question

There is no MapToStoredProcedures in EF Core which is a pity as it allows the Add method to hide whether a stored procedure is used or not.

I have looked at EF Core 3.1 and 5 and I cannot find the recommended replacement. So if I have the code below, how/where do I setup and call an insert stored procedure or select stored procedure against it?

public class DatabaseModel : DbContext
{
    public virtual DbSet<Office> Offices { get; set; }

    public DatabaseModel(DbContextOptions<DatabaseModel> options) : base(options)    
    { }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {}
}

Thanks for any help.

Answer 1

NOTE: THIS CURRENTLY DOESN'T IMPLEMENT THE BULK INSERTS THAT SQL SERVER DOES, FOR MULTIPLE TO WORK WITH THE SAME TABLE NAME YOU WOULD NEED TO IMPLEMENT AppendBulkInsertOperation

You could create an implementation that overrides Microsoft.EntityFrameworkCore.Update.IUpdateSqlGenerator and do a ReplaceService on it when registering. I've successfully overridden an insert operation in Sql Server.

In my case, calling the stored procedure directly is not an option because I'm attempting to upgrade a project from EF 6 to EF Core with more minimal changes.

Registering DbContext:

            services.AddDbContext<ExampleDbContext>((sp, options) =>
            {
                options
                    .UseSqlServer(connectionString)
                    .ReplaceService<IUpdateSqlGenerator, MapToProcedureUpdateSqlGenerator>();
            });

MapToProcedureUpdateSqlGenerator

using Microsoft.EntityFrameworkCore.SqlServer.Update.Internal;
using Microsoft.EntityFrameworkCore.Update;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace EFCoreMapToStoredProcedures
{
   // careful with this implementation as it gives warning
   // This is an internal API that supports the Entity Framework Core infrastructure
    //     and not subject to the same compatibility standards as public APIs.
    public class MapToProcedureUpdateSqlGenerator : SqlServerUpdateSqlGenerator
    {
        public MapToProcedureUpdateSqlGenerator(UpdateSqlGeneratorDependencies dependencies) : base(dependencies)
        {
        }



        public override ResultSetMapping AppendInsertOperation(StringBuilder commandStringBuilder, ModificationCommand command, int commandPosition)
        {
            if (command == null) throw new ArgumentNullException(nameof(command));
            if (commandStringBuilder == null) throw new ArgumentNullException(nameof(commandStringBuilder));

            if (_tableInsertProcs.TryGetValue(command.TableName, out string procName))
            {
                var name = command.TableName;
                var schema = command.Schema;
                var operations = command.ColumnModifications;

                var writeOperations = operations.Where(o => o.IsWrite).ToList();
                 
                AppendExecCommandHeader(commandStringBuilder, procName, schema, writeOperations);

                if (operations.Any(_ => _.IsRead))
                {
                    return ResultSetMapping.LastInResultSet;
                }

                return ResultSetMapping.NoResultSet;
            }
            else
            {
                return base.AppendInsertOperation(commandStringBuilder, command, commandPosition);
            }
        }

  
        /// <summary>
        ///     Appends a SQL fragment for excuting a stored procedure
        /// </summary>
        /// <param name="commandStringBuilder"> The builder to which the SQL should be appended. </param>
        /// <param name="name"> The name of the procedure. </param>
        /// <param name="schema"> The table schema, or <see langword="null" /> to use the default schema. </param>
        /// <param name="operations"> The operations representing the data to be inserted. </param>
        protected virtual void AppendExecCommandHeader(
            StringBuilder commandStringBuilder,
            string name,
            string schema,
            IReadOnlyList<ColumnModification> operations)
        {
            if (commandStringBuilder == null) throw new ArgumentNullException(nameof(commandStringBuilder));
            if (string.IsNullOrWhiteSpace(name)) throw new ArgumentException("required", nameof(name));
            if (operations == null) throw new ArgumentNullException(nameof(operations));
            
            commandStringBuilder.Append("EXEC ");
            SqlGenerationHelper.DelimitIdentifier(commandStringBuilder, name, schema);

            if (operations.Count > 0)
            {

                commandStringBuilder
                    .AppendJoin(
                        operations,
                        (this, name, schema),
                        (sb, o, p) =>
                        {
                            if (o.IsWrite)
                            {
                                var (g, n, s) = p;
                                if (!o.UseCurrentValueParameter)
                                {
                                    throw new NotSupportedException("literals not supported");
                                }
                                else
                                {
                                    g.SqlGenerationHelper.GenerateParameterNamePlaceholder(sb, o.ColumnName);
                                    commandStringBuilder.Append(" = ");
                                    g.SqlGenerationHelper.GenerateParameterNamePlaceholder(sb, o.ParameterName);
                                }
                            }
                            else
                            {
                                sb.Append("DEFAULT");
                            }
                        });
                commandStringBuilder.AppendLine(SqlGenerationHelper.StatementTerminator);
            }
        }


        // todo make configurable with dependencies
        private readonly Dictionary<string, string> _tableInsertProcs = new Dictionary<string, string>()
        {
            ["OrderItems"] = "InsertOrderItem"
        };
    }
}

Answer 2

MapToStoredProcedures is not supported in EF CORE. You can execute stored procedure with FromSqlRaw Method.

var result = ctx.ParameterDetails.FromSqlRaw("EXEC dbo.get_nextparam @UserId={0}", userId).ToList();