logo标签列表

如何在Entity Framework Core中运行存储过程？

asp.net-coreasp.net-core-mvcentity-framework-core
107

我在一个ASP.NET Core应用中使用EF Core 1.0。请问您能否指导我执行存储过程的正确方法？旧的方法使用ObjectParameters和((IObjectContextAdapter)this).ObjectContext.ExecuteFunction已经不起作用了。

1
你需要运行存储过程并获取一些 DbSet 结果吗？还是只需运行它？ - haim770
我需要获取结果！ - eadam
对于最简单和最完整的答案，请查看此链接：https://dev59.com/Fl4b5IYBdhLWcg3w3k-Y#75465142 - Ash K
20个回答
95

现在，EF Core 1.0已经支持存储过程，并且支持多个结果集的映射。

点击此处查看修复详情

您可以在C#中如下调用它：

var userType = dbContext.Set().FromSql("dbo.SomeSproc @Id = {0}, @Name = {1}", 45, "Ada");
2
在这里找到另一个很好的例子 - http://www.dotnetjalps.com/2015/11/stored-procedure-entity-framework-codefirst.html。这是不言自明的。 - Arvin
1
目前还不支持多个数据集。请参见 https://github.com/aspnet/EntityFramework/issues/1864#issuecomment-170709883。 - AperioOculus
5
我的个人意见：var UserId =1; _dbCtx.Set<YourDTOViewModelWhatever>().FromSql($"dbo.StoredProcedureName @UserId= {UserId}"); //仅仅是语法糖... - hidden
12
如果您像我一样找不到FromSql，请看这个：http://dotnetthoughts.net/how-to-execute-storedprocedure-in-ef-core/。"要获取FromSql命令，您需要在project.json文件中添加“Microsoft.EntityFrameworkCore.Relational”包的引用。"还需要添加using Microsoft.EntityFrameworkCore;。 - Ernesto
1
很遗憾，在EF Core 1.1中似乎无法使用Set()。只会得到The arguments for DbContext.Set<TEntity>() cannot be inferred from the usage。 - Douglas Gaskell
显示剩余5条评论
34

要执行存储过程，请使用FromSql方法，该方法可以执行原始的SQL查询。

例如：

    var products= context.Products
        .FromSql("EXECUTE dbo.GetProducts")
        .ToList();

使用带参数

    var productCategory= "Electronics";

    var product = context.Products
        .FromSql("EXECUTE dbo.GetProductByCategory {0}", productCategory)
        .ToList();
或者
    var productCategory= new SqlParameter("productCategory", "Electronics");

    var product = context.Product
        .FromSql("EXECUTE dbo.GetProductByName  @productCategory", productCategory)
        .ToList();

执行RAW SQL查询或存储过程存在一定的限制。您不能将其用于INSERT/UPDATE/DELETE。如果要执行INSERT、UPDATE、DELETE查询，请使用ExecuteSqlCommand。

    var categoryName = "Electronics";
    dataContext.Database
               .ExecuteSqlCommand("dbo.InsertCategory @p0", categoryName);
7
ж–№жі•ExecuteSqlCommandзҺ°е·ІиҝҮж—¶гҖӮиҜ·еҸӮйҳ…ExecuteSqlCommandж–№жі•гҖӮ еҸҜиғҪзҡ„жӣҝд»Јж–№жі•жҳҜExecuteSqlRawе’ҢExecuteSqlInterpolatedгҖӮ - Pablo Alexis Domínguez Grau
33

截至EF7的7.0.0-beta3版本，还没有实现存储过程支持。您可以使用问题#245跟踪此功能的进度。

目前，您可以使用传统的ADO.NET方法来完成此操作。

var connection = (SqlConnection)context.Database.AsSqlServer().Connection.DbConnection;

var command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "MySproc";
command.Parameters.AddWithValue("@MyParameter", 42);

command.ExecuteNonQuery();
2
这是#624。;) 您始终可以在ADO.NET连接上执行此操作：context.Database.AsRelational().Connection.DbConnection。 - bricelam
3
@JimWooley，这似乎有点不道德...但是，现在请使用FromSql。 - bricelam
7
@JimWooley 哈哈，不确定你是否意识到了，但你给 EF 团队的一位主要开发者的回答投了反对票... :) - gdoron
4
StackOverflow的答案很大程度上是一时的东西。你真的希望每个人都为了永远而保持他们的答案最新吗？要么提问者应该选择一个不同的答案，要么社区应该赞同出现的更好的答案。对于打负分的行为是不好的。 - bricelam
1
@bricelam - 我不同意答案应该被视为时间点。我偶尔会回到旧的高票答案，看看是否有什么变化，以便新的搜索仍然相关，但我意识到并非每个人都这样做。很多时候，我从搜索中来到SO，并开始使用被接受的答案，只是后来发现还有更好的答案。我同意如果提问者更新答案会很好，但他们可能只是得到他们想要的并离开了。无论如何，我认为让从Google来的人知道有更好的方法会很有帮助。 :) - Richard Marskell - Drackir
显示剩余6条评论
19

EF Core对存储过程的支持与之前版本的EF Code First类似。

您需要通过继承来自EF的DbContext类来创建您的DbContext类。使用DbContext执行存储过程。

第一步是编写一个方法，从DbContext创建DbCommand。

public static DbCommand LoadStoredProc(
  this DbContext context, string storedProcName)
{
  var cmd = context.Database.GetDbConnection().CreateCommand();
  cmd.CommandText = storedProcName;
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  return cmd;
}

要向存储过程传递参数，请使用以下方法。

public static DbCommand WithSqlParam(
  this DbCommand cmd, string paramName, object paramValue)
{
  if (string.IsNullOrEmpty(cmd.CommandText))
    throw new InvalidOperationException(
      "Call LoadStoredProc before using this method");
  var param = cmd.CreateParameter();
  param.ParameterName = paramName;
  param.Value = paramValue;
  cmd.Parameters.Add(param);
  return cmd;
}

最后，使用MapToList方法将结果映射为自定义对象列表。
private static List<T> MapToList<T>(this DbDataReader dr)
{
  var objList = new List<T>();
  var props = typeof(T).GetRuntimeProperties();

  var colMapping = dr.GetColumnSchema()
    .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
    .ToDictionary(key => key.ColumnName.ToLower());

  if (dr.HasRows)
  {
    while (dr.Read())
    {
      T obj = Activator.CreateInstance<T>();
      foreach (var prop in props)
      {
        var val = 
          dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
          prop.SetValue(obj, val == DBNull.Value ? null : val);
      }
      objList.Add(obj);
    }
  }
  return objList;
}

现在我们准备使用ExecuteStoredProc方法执行存储过程，并将其映射到作为T传递的List类型。

public static async Task<List<T>> ExecuteStoredProc<T>(this DbCommand command)
{
  using (command)
  {
    if (command.Connection.State == System.Data.ConnectionState.Closed)
    command.Connection.Open();
    try
    {
      using (var reader = await command.ExecuteReaderAsync())
      {
        return reader.MapToList<T>();
      }
    }
    catch(Exception e)
    {
      throw (e);
    }
    finally
    {
      command.Connection.Close();
    }
  }
}

例如，要执行名为“StoredProcedureName”的存储过程，并带有名为“firstparamname”和“secondparamname”的两个参数，可以使用以下实现方式。
List<MyType> myTypeList = new List<MyType>();
using(var context = new MyDbContext())
{
  myTypeList = context.LoadStoredProc("StoredProcedureName")
  .WithSqlParam("firstparamname", firstParamValue)
  .WithSqlParam("secondparamname", secondParamValue).
  .ExecureStoredProc<MyType>();
}
1
这是一个非常好的/完整的答案，包括映射器等。谢谢伙计。 - Yogurtu
嗨，如果我需要在一个链中运行多个存储过程，我如何知道存储过程的运行状态，比如等待前一个完成后再运行下一个？ - undefined
13

我尝试了所有其他解决方案，但对我都没有用。但我找到了一个正确的解决方案，它可能对这里的某个人有所帮助。

要调用存储过程并将结果获取到EF Core模型列表中，我们需要遵循3个步骤。

第一步。 您需要添加一个新类，就像实体类一样。该类应具有与SP中所有列相对应的属性。例如，如果您的SP返回两列名为Id和Name，则您的新类应该是：

public class MySPModel
{
    public int Id {get; set;}
    public string Name {get; set;}
}

步骤2。

然后，您需要将一个DbQuery属性添加到您的DBContext类中，用于您的SP。

public partial class Sonar_Health_AppointmentsContext : DbContext
{
        public virtual DbSet<Booking> Booking { get; set; } // your existing DbSets
        ...

        public virtual DbQuery<MySPModel> MySP { get; set; } // your new DbQuery
        ...
}

第三步。

现在，您将能够从您的DBContext中调用并获取来自SP的结果。

var result = await _context.Query<MySPModel>().AsNoTracking().FromSql(string.Format("EXEC {0} {1}", functionName, parameter)).ToListAsync();

我正在使用通用的UnitOfWork和Repository。因此，我的执行SP的函数如下：

/// <summary>
/// Execute function. Be extra care when using this function as there is a risk for SQL injection
/// </summary>
public async Task<IEnumerable<T>> ExecuteFuntion<T>(string functionName, string parameter) where T : class
{
    return await _context.Query<T>().AsNoTracking().FromSql(string.Format("EXEC {0} {1}", functionName, parameter)).ToListAsync();
}

希望这对某些人有所帮助！！！
我本来希望避免这样做的。我曾经编写了自己的库（EF之前）来处理动态数据集（例如连接等），然后一切都运行得很完美。但是每个人都抱怨，所以我转向了EF，现在你不能这样做了。猜猜怎么着，他们要求我重新编写我的库。我的回应并不愉快。 - djack109
5

"(SqlConnection)context" -- 这种强制类型转换不再有效，你可以这样写："SqlConnection context;

".AsSqlServer()" -- 不存在。

"command.ExecuteNonQuery();" -- 不返回结果。要返回结果，你需要使用reader=command.ExecuteReader()。

如果你用dt.load(reader)，那么你需要将框架从5.0切换回4.51，因为5.0还不支持数据表/数据集。注意：这是VS2015 RC版本。

5
这段话的意思是：“文档 上缺乏相关信息，这让人感到尴尬。我花费了很多时间尝试才弄清楚了其中的方法，所以我在这里记录下来，希望其他人不必花费时间去研究。我使用的是 Entity Framework Core Version 6.0.10。

总结

有两种返回结果的存储过程。调用它们并获取结果的方法各不相同：

  1. 返回基于表格的输出的存储过程。
  2. 返回输出参数中的输出的存储过程。
”

返回基于表的输出的存储过程

如果您的存储过程看起来像这样：

CREATE PROCEDURE dbo.MyTestSPToGetShopType @ShopId AS VARCHAR(25)
AS
SELECT ShopId, ShopType
FROM Shop
WHERE SHOPID = @ShopId

步骤1：基于您的输出创建一个类。
public class MyTestSpResult
{
    public string ShopId { get; set; }
    public string ShopType { get; set; }
}

步骤2： 在您的DbContext的protected override void OnModelCreating(ModelBuilder builder)方法中注册此内容：
builder.Entity<MyTestSpResult>(e => e.HasNoKey());

步骤三：从代码中调用SP并获取结果：
var myTestSpResult = await _dbContext.Set<MyTestSpResult>()
                                     .FromSqlInterpolated($"EXEC dbo.MyTestSPToGetShopType @ShopId = \"A03\"")
                                     .ToArrayAsync();
var myShopType = myTestSpResult[0].ShopType;

备选方法

步骤1和2与上述相同。

第3步： 在您的DbContext中注册步骤1中的类：

public virtual DbSet<MyTestSpResult> MyTestSpResult { get; set; }

步骤四： 从代码中调用SP并获取结果：
var mySPResult = await _dbContext.MyTestSpResult
                                 .FromSqlInterpolated($"EXEC dbo.MyTestSPToGetShopType @ShopId = \"A03\"")
                                 .ToArrayAsync();
var myShopType = mySPResult[0].ShopType;

返回输出参数的存储过程

如果您的存储过程看起来像这样：

CREATE PROCEDURE dbo.MyOtherTestSPToGetShopType
(
    @ShopId VARCHAR(25)
    ,@ShopType VARCHAR(25) OUTPUT
)
AS
SELECT @ShopType = ShopType
FROM Shop
WHERE SHOPID = @ShopId

步骤1：创建SQL参数：
var shopId = new SqlParameter()
{
    ParameterName = "@ShopId",
    Value = "A03",
    Direction = System.Data.ParameterDirection.Input,
    SqlDbType = System.Data.SqlDbType.VarChar,
    Size = 25
};

var shopType = new SqlParameter()
{
    ParameterName = "@ShopType",
    Direction = System.Data.ParameterDirection.Output,
    SqlDbType = System.Data.SqlDbType.VarChar,
    Size = 25
};

步骤2：使用这些参数从代码中调用SP，并获取结果：
await _dbContext.Database
                .ExecuteSqlInterpolatedAsync($"EXEC dbo.MyOtherTestSPToGetShopType @ShopId = {shopId}, @ShopType = {shopType} OUT");
var myShopType = shopType.Value as string;
SP返回多个结果集怎么办？EF 6似乎支持这种情况。在EF Core上没有找到相关信息。 - tasin95
@tasin95 我自己没有做过这个，但我认为你可以使用这个答案中的第一种方法来实现。在你的 MyTestSpResult 模型内，创建 List 属性来建模你的集合。然后只需调用 SP；myTestSpResult[0] 将有第一个集合，myTestSpResult[1] 将有第二个集合，以此类推。 - Ash K
4
目前 EF 7 或 EF Core 不支持在设计器中导入存储过程并直接调用旧的方法。您可以查看路线图，了解将来会支持什么： EF Core 路线图。
因此，现在最好使用 SqlConnection 调用存储过程或任何原始查询，因为这项工作不需要整个 EF。以下是两个示例：
调用返回单个值的存储过程。本例中为字符串。
CREATE PROCEDURE [dbo].[Test]
    @UserName nvarchar(50)
AS
BEGIN
    SELECT 'Name is: '+@UserName;
END

调用返回列表的存储过程。
CREATE PROCEDURE [dbo].[TestList]
AS
BEGIN
    SELECT [UserName], [Id] FROM [dbo].[AspNetUsers]
END

为了调用这些存储过程，最好创建一个静态类来保存所有这些函数，例如，我称之为DataAccess类，如下所示：
public static class DataAccess

    {
        private static string connectionString = ""; //Your connection string
        public static string Test(String userName)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                // 1.  create a command object identifying the stored procedure
                SqlCommand cmd = new SqlCommand("dbo.Test", conn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@UserName", userName));

                // execute the command
                using (var rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        return rdr[0].ToString();
                    }
                    else
                    {
                        return null;
                    }
                }
            }
        }

        public static IList<Users> TestList()
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                // 1.  create a command object identifying the stored procedure
                SqlCommand cmd = new SqlCommand("dbo.TestList", conn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // execute the command
                using (var rdr = cmd.ExecuteReader())
                {
                    IList<Users> result = new List<Users>();
                    //3. Loop through rows
                    while (rdr.Read())
                    {
                        //Get each column
                        result.Add(new Users() { UserName = (string)rdr.GetString(0), Id = rdr.GetString(1) });
                    }
                    return result;
                }
            }

        }
    }

用户类（Users class）如下：

public class Users
{
     public string UserName { set; get; }
     public string Id { set; get; }
}

顺便提一句，您不需要担心每个请求到sql的打开和关闭连接的性能，因为asp.net已经为您管理了这些。

希望对您有所帮助。

4

我在使用 ExecuteSqlCommand 和 ExecuteSqlCommandAsync 时遇到了很多麻烦，输入参数是容易处理的，但输出参数非常困难。

我不得不回归使用 DbCommand ，像这样 - 

DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

cmd.CommandText = "dbo.sp_DoSomething";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar) { Value = "Steve" });
cmd.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar) { Value = "Smith" });

cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.BigInt) { Direction = ParameterDirection.Output });

我在这篇文章中更详细地介绍了它。

1
他们甚至在 EF Core 的文档中都没有提到“输出” :-/ - Simon_Weaver
4

由于我们团队已经决定使用通用的UnitOfWork模式，因此在创建我的代码时，我参考了每个人的解决方案。

我也会发布一些UnitOfWork代码，以便您可以了解为什么我需要这样实现它。

public interface IUnitOfWork : IDisposable
{
    DbContext Context { get; }
    Task<List<T>> ExecuteStoredProc<T>(string storedProcName, Dictionary<string, object> procParams) where T : class;
}

接口实现:

public class UnitOfWork : IUnitOfWork
{
    public DbContext Context { get; private set; }

/// <summary>
/// Execute procedure from database using it's name and params that is protected from the SQL injection attacks.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="storedProcName">Name of the procedure that should be executed.</param>
/// <param name="procParams">Dictionary of params that procedure takes. </param>
/// <returns>List of objects that are mapped in T type, returned by procedure.</returns>
    public async Task<List<T>> ExecuteStoredProc<T>(string storedProcName, Dictionary<string, object> procParams) where T : class
    {
        DbConnection conn = Context.Database.GetDbConnection();
        try
        {
            if(conn.State != ConnectionState.Open)
                await conn.OpenAsync();

            await using (DbCommand command = conn.CreateCommand())
            {
                command.CommandText = storedProcName;
                command.CommandType = CommandType.StoredProcedure;

                foreach (KeyValuePair<string, object> procParam in procParams)
                {
                    DbParameter param = command.CreateParameter();
                    param.ParameterName = procParam.Key;
                    param.Value = procParam.Value;
                    command.Parameters.Add(param);
                }

                DbDataReader reader = await command.ExecuteReaderAsync();
                List<T> objList = new List<T>();
                IEnumerable<PropertyInfo> props = typeof(T).GetRuntimeProperties();
                Dictionary<string, DbColumn> colMapping = reader.GetColumnSchema()
                    .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
                    .ToDictionary(key => key.ColumnName.ToLower());

                if (reader.HasRows)
                {
                    while (await reader.ReadAsync())
                    {
                        T obj = Activator.CreateInstance<T>();
                        foreach (PropertyInfo prop in props)
                        {
                            object val =
                                reader.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
                            prop.SetValue(obj, val == DBNull.Value ? null : val);
                        }
                        objList.Add(obj);
                    }
                }
                reader.Dispose();

                return objList;
            }
        }
        catch (Exception e)
        {
            System.Diagnostics.Debug.WriteLine(e.Message, e.InnerException);
        }
        finally
        {
            conn.Close();
        }

        return null; // default state
    }

例子使用方法如下:

public class MyService : IMyService 
{
        private readonly IUnitOfWork _uow;
        public MyService(IUnitOfWork uow)
        {
            _uow = uow;
        }
        
        public async Task<List<TreeViewModel>> GetTreeOptions()
        {
            var procParams = new Dictionary<string, object>()
            {
                {"@Id", 2}
            };
            var result = await _uow.ExecuteStoredProc<TreeViewModel>("FetchTreeProcedure", procParams);
            return result;
        }
}
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接