asp.net-core - net - stored procedure mapping entity framework core

¿Cómo ejecutar procedimientos almacenados en Entity Framework Core?

Question

Estoy utilizando EF7 (entidad marco de la estructura) en una aplicación asp.net core. ¿Puede indicarme la forma correcta de ejecutar los procedimientos almacenados? El método anterior con ObjectParameters y ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction no funciona.

Answer 1

Actualmente, EF 7 o EF Core no son compatibles con el antiguo método de importar procedimientos almacenados en el diseñador y llamarlos directamente. Puede echar un vistazo a la hoja de ruta para ver qué se va a admitir en el futuro: hoja de ruta central de EF .

Así que, por ahora, es mejor usar SqlConnection para llamar a procedimientos almacenados o cualquier consulta sin formato, ya que no necesita la EF completa para este trabajo. Aquí hay dos ejemplos:

Llame al procedimiento almacenado que devuelve un valor único. Cadena en este caso.

CREATE PROCEDURE [dbo].[Test]
    @UserName nvarchar(50)
AS
BEGIN
    SELECT ''Name is: ''+@UserName;
END

Llamar al procedimiento almacenado que devuelve una lista.

CREATE PROCEDURE [dbo].[TestList]
AS
BEGIN
    SELECT [UserName], [Id] FROM [dbo].[AspNetUsers]
END

Para llamar a estos procedimientos almacenados, es mejor crear una clase estática que contenga todas estas funciones, por ejemplo, la llamé clase DataAccess, de la siguiente manera:

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;
                }
            }

        }
    }

Y la clase de los usuarios es así:

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

Por cierto, no tiene que preocuparse por el rendimiento de abrir y cerrar una conexión para cada solicitud de sql, ya que asp.net se ocupa de gestionar esto por usted. Y espero que esto haya sido útil.

Answer 2

El soporte de procedimientos almacenados todavía no está implementado (a partir de 7.0.0-beta3) en EF7. Puede seguir el progreso de esta función usando el número #245 .

Por ahora, puedes hacerlo a la antigua usanza con 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();

Answer 3

El soporte para el procedimiento almacenado en EF Core es similar a las versiones anteriores del Código EF primero.

Necesita crear su clase DbContext insertando la clase DbContext de EF. Los procedimientos almacenados se están ejecutando con DbContext.

El primer paso es escribir un método que cree un DbCommand desde el DbContext.

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;
}

Para pasar parámetros al procedimiento almacenado, use el siguiente método.

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;
}

Finalmente, para mapear el resultado en una lista de objetos personalizados, use el método 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;
}

Ahora estamos listos para ejecutar el procedimiento almacenado con el método ExecuteStoredProc y lo mapeamos a la Lista cuyo tipo ha pasado como T.

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

Por ejemplo, para ejecutar un procedimiento almacenado llamado "StoredProcedureName" con dos parámetros llamados "firstparamname" y "secondparamname", esta es la implementación.

List<MyType> myTypeList = new List<MyType>();
using(var context = new MyDbContext())
{
  myTypeList = context.LoadStoredProc("StoredProcedureName")
  .WithSqlParam("firstparamname", firstParamValue)
  .WithSqlParam("secondparamname", secondParamValue).
  .ExecureStoredProc<MyType>();
}

Answer 4

El soporte para el procedimiento almacenado en EF7 se resuelve ahora, esto también admite el mapeo de múltiples conjuntos de resultados.

Verifique aquí los detalles del arreglo

Y puede llamarlo así: var userType = dbContext.Set().FromSql("dbo.SomeSproc @Id = {0}, @Name = {1}", 45, "Ada");

Answer 5

Para ejecutar los procedimientos almacenados, use el método FromSql que ejecuta consultas RAW SQL

p.ej

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

Para usar con parámetros

var productCategory= "Electronics";

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

o

var productCategory= new SqlParameter("productCategory", "Electronics");

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

Existen ciertas limitaciones para ejecutar consultas RAW SQL o procedimientos almacenados. No puede usarlo para INSERT / UPDATE / DELETE. si desea ejecutar INSERTAR, ACTUALIZAR, ELIMINAR consultas, utilice ExecuteSqlCommand

var categoryName = "Electronics";
dataContext.Database
           .ExecuteSqlCommand("dbo.InsertCategory @p0", categoryName);

Answer 6

Si está ejecutando un procedimiento almacenado desde Informix utilizando EntityFrameworkCore, debe incluir el comando EXECUTE PROCEDURE

var spresult = _informixContext.procdata.FromSql("EXECUTE PROCEDURE dummyproc ({0},{1},{2})", parameters: new[] { p0, p1,p2 }).ToList();

Answer 7

Tuve muchos problemas con ExecuteSqlCommand y ExecuteSqlCommandAsync , los parámetros IN fueron fáciles, pero los parámetros OUT fueron muy difíciles.

Tuve que volver a usar DbCommand como tal -

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

Escribí más sobre esto en esta publicación .

Answer 8

Usando el conector MySQL y Entity Framework Core 2.0

Mi problema era que recibía una excepción como fx. Ex.Message = "La columna requerida ''cuerpo'' no estaba presente en los resultados de una operación ''FromSql''". Por lo tanto, para obtener filas a través de un procedimiento almacenado de esta manera, debe devolver todas las columnas para ese tipo de entidad con el que está asociado el DBSet, incluso si no necesita acceder a todo para su solicitud actual.

var result = _context.DBSetName.FromSql($"call storedProcedureName()").ToList(); 

O con parámetros

var result = _context.DBSetName.FromSql($"call storedProcedureName({optionalParam1})").ToList(); 

Answer 9

"(SqlConnection)context" - Este tipo de conversión ya no funciona. Puede hacer: "SqlConnection context;

".AsSqlServer()" - No existe.

"command.ExecuteNonQuery();" - No devuelve resultados. reader=command.ExecuteReader() funciona.

Con dt.load (lector) ... entonces tiene que cambiar el marco de 5,0 y volver a 4.51, ya que 5.0 no admite datatables / datasets, aún. Nota: Este es VS2015 RC.