EF Core - EF Core 3.1或5中MapToStoredProcedures的替换方案是什么？

Question

在EF Core中没有MapToStoredProcedures，这很遗憾，因为它允许Add方法隐藏是否使用存储过程。

我查看了EF Core 3.1和5，但找不到推荐的替代方法。那么，如果我有下面的代码，我应该在哪里设置和调用插入存储过程或选择存储过程呢？

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

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

    protected override void OnModelCreating(ModelBuilder modelBuilder) {}
}

感谢任何的帮助。

- user3807918

---

目前不支持此功能。 - ErikEJ

在 Github 上追踪 https://github.com/dotnet/efcore/issues/245 - Jeremy Lakeman

Answer 1

MapToStoredProcedures 不支持 EF CORE。 您可以使用 FromSqlRaw 方法来执行存储过程。

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

- AVTUNEY

---

ctx的对象是什么？ - Karthic G

这是 EF Core DbContext。 - AVTUNEY

Answer 2

注意：当前尚未实现SQL Server的批量插入，如果要使用相同的表名进行多个操作，您需要实现AppendBulkInsertOperation。

您可以创建一个实现，覆盖Microsoft.EntityFrameworkCore.Update.IUpdateSqlGenerator并在注册时进行ReplaceService。我已经成功地重写了Sql Server中的插入操作。

在我的情况下，直接调用存储过程不是一个选项，因为我正在尝试将项目从EF 6升级到具有更少更改的EF Core。

注册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"
        };
    }
}

- Daniel Leach