c# - sqlquery - Consulta SQL sin formato sin DbSet-Entity Framework Core

fromsql c#

Question

Con Entity Framework Core eliminando dbData.Database.SqlQuery<SomeModel> no puedo encontrar una solución para construir una consulta SQL sin procesar para mi consulta de búsqueda de texto completo que devolverá los datos de las tablas y también el rango.

El único método que he visto para construir una consulta SQL sin procesar en Entity Framework Core es a través de dbData.Product.FromSql("SQL SCRIPT"); lo cual no es útil ya que no tengo un DbSet que asigne el rango que devuelvo en la consulta.

¿¿¿Algunas ideas???

Answer 1

En Core 2.1 puedes hacer algo como esto:

List<Ranks> gettingRanks = _DbContext.GetRanks(value1,value2).Result.ToListAsync();

y luego defina su Procedimiento SQL, como:

  using (var command = this.DbContext.Database.GetDbConnection().CreateCommand())
  {
      command.CommandText = "SELECT ... WHERE ...> @p1)";
      command.CommandType = CommandType.Text;
      var parameter = new SqlParameter("@p1",...);
      command.Parameters.Add(parameter);

      this.DbContext.Database.OpenConnection();

      using (var result = command.ExecuteReader())
      {
         while (result.Read())
         {
            .... // Map to your entity
         }
      }
  }

De esta manera, el modelo Ranks no se creará en su base de datos.

Ahora en su controlador / acción puede llamar:

 dbData.Product.FromSql("SQL SCRIPT");

De esta manera puede llamar a Procedimientos SQL sin formato.

Answer 2

En EF Core ya no puedes ejecutar sql sin procesar "gratis". DbSet definir una clase POCO y un DbSet para esa clase. En su caso, deberá definir el Rango :

var ranks = DbContext.Ranks
   .FromSql("SQL_SCRIPT OR STORED_PROCEDURE @p0,@p1,...etc", parameters)
   .AsNoTracking().ToList();

Como seguramente será de solo lectura, será útil incluir la llamada .AsNoTracking() .

Answer 3

En caso de que solo desee ejecutar la consulta y desee un int back. EFCore 2 tiene el DbContext.Database.ExecuteSqlCommand("Yourqueryhere") .

Editar:

ExecuteSqlCommand y ExecuteSqlCommandAsync se definen en Microsoft.EntityFrameworkCore.Relational espacio de nombres Microsoft.EntityFrameworkCore.Relational . Asegúrese de que esté referenciado.

Answer 4

No apunta directamente al escenario del OP, pero dado que he estado luchando con esto, me gustaría abandonar estos ex. Métodos que facilitan la ejecución de SQL sin DbContext con DbContext :

public static class DbContextCommandExtensions
{
  public static async Task<int> ExecuteNonQueryAsync(this DbContext context, string rawSql,
    params object[] parameters)
  {
    var conn = context.Database.GetDbConnection();
    using (var command = conn.CreateCommand())
    {
      command.CommandText = rawSql;
      if (parameters != null)
        foreach (var p in parameters)
          command.Parameters.Add(p);
      await conn.OpenAsync();
      return await command.ExecuteNonQueryAsync();
    }
  }

  public static async Task<T> ExecuteScalarAsync<T>(this DbContext context, string rawSql,
    params object[] parameters)
  {
    var conn = context.Database.GetDbConnection();
    using (var command = conn.CreateCommand())
    {
      command.CommandText = rawSql;
      if (parameters != null)
        foreach (var p in parameters)
          command.Parameters.Add(p);
      await conn.OpenAsync();
      return (T)await command.ExecuteScalarAsync();
    }
  }
}

Answer 5

Por ahora, hasta que haya algo nuevo de EFCore, usaría un comando y lo mapearía manualmente

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
       modelBuilder.Query<Ranks>();
}

Intente con SqlParameter para evitar la inyección de SQL.

public async Task<List<Ranks>> GetRanks(string value1, Nullable<decimal> value2)
{
    SqlParameter value1Input = new SqlParameter("@Param1", value1?? (object)DBNull.Value);
    SqlParameter value2Input = new SqlParameter("@Param2", value2?? (object)DBNull.Value);

    List<Ranks> getRanks = await this.Query<Ranks>().FromSql("STORED_PROCEDURE @Param1, @Param2", value1Input, value2Input).ToListAsync();

    return getRanks;
}

FromSql no funciona con una consulta completa. Ejemplo si desea incluir una cláusula WHERE, se ignorará.

Algunos enlaces:

Ejecución de consultas SQL sin formato con Entity Framework Core

Consultas SQL sin procesar

Answer 6

Puede ejecutar sql sin procesar en EF Core: agregue esta clase a su proyecto. Esto le permitirá ejecutar SQL sin formato y obtener los resultados sin tener que definir un POCO y un DBSet. Consulte https://github.com/aspnet/EntityFramework/issues/1862#issuecomment-220787464 para ver un ejemplo original.

using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using System.Threading;
using System.Threading.Tasks;

namespace Microsoft.EntityFrameworkCore
{
    public static class RDFacadeExtensions
    {
        public static RelationalDataReader ExecuteSqlQuery(this DatabaseFacade databaseFacade, string sql, params object[] parameters)
        {
            var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();

            using (concurrencyDetector.EnterCriticalSection())
            {
                var rawSqlCommand = databaseFacade
                    .GetService<IRawSqlCommandBuilder>()
                    .Build(sql, parameters);

                return rawSqlCommand
                    .RelationalCommand
                    .ExecuteReader(
                        databaseFacade.GetService<IRelationalConnection>(),
                        parameterValues: rawSqlCommand.ParameterValues);
            }
        }

        public static async Task<RelationalDataReader> ExecuteSqlQueryAsync(this DatabaseFacade databaseFacade, 
                                                             string sql, 
                                                             CancellationToken cancellationToken = default(CancellationToken),
                                                             params object[] parameters)
        {

            var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();

            using (concurrencyDetector.EnterCriticalSection())
            {
                var rawSqlCommand = databaseFacade
                    .GetService<IRawSqlCommandBuilder>()
                    .Build(sql, parameters);

                return await rawSqlCommand
                    .RelationalCommand
                    .ExecuteReaderAsync(
                        databaseFacade.GetService<IRelationalConnection>(),
                        parameterValues: rawSqlCommand.ParameterValues,
                        cancellationToken: cancellationToken);
            }
        }
    }
}

Aquí hay un ejemplo de cómo usarlo:

// Execute a query.
using(var dr = await db.Database.ExecuteSqlQueryAsync("SELECT ID, Credits, LoginDate FROM SamplePlayer WHERE " +
                                                          "Name IN (''Electro'', ''Nitro'')"))
{
    // Output rows.
    var reader = dr.DbDataReader;
    while (reader.Read())
    {
        Console.Write("{0}/t{1}/t{2} /n", reader[0], reader[1], reader[2]);
    }
}

Answer 7

Puede usar esto (desde https://github.com/aspnet/EntityFrameworkCore/issues/1862#issuecomment-451671168 ):

public static class SqlQueryExtensions
{
    public static IList<T> SqlQuery<T>(this DbContext db, string sql, params object[] parameters) where T : class
    {
        using (var db2 = new ContextForQueryType<T>(db.Database.GetDbConnection()))
        {
            return db2.Query<T>().FromSql(sql, parameters).ToList();
        }
    }

    private class ContextForQueryType<T> : DbContext where T : class
    {
        private readonly DbConnection connection;

        public ContextForQueryType(DbConnection connection)
        {
            this.connection = connection;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // switch on the connection type name to enable support multiple providers
            // var name = con.GetType().Name;
            optionsBuilder.UseSqlServer(connection, options => options.EnableRetryOnFailure());

            base.OnConfiguring(optionsBuilder);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Query<T>();
            base.OnModelCreating(modelBuilder);
        }
    }
}

Answer 8

Si utiliza EF Core 2.1 Release Candidate 1 disponible desde el 7 de mayo de 2018, puede aprovechar la nueva característica propuesta, que es el tipo de consulta.

¿Qué es el tipo de consulta ?

Además de los tipos de entidad, un modelo EF Core puede contener tipos de consulta, que se pueden usar para realizar consultas de base de datos contra datos que no están asignados a tipos de entidad.

¿Cuándo usar el tipo de consulta?

Sirviendo como el tipo de retorno para consultas FromSql () ad hoc.

Asignación a vistas de bases de datos.

Asignación a tablas que no tienen una clave primaria definida.

Asignación a consultas definidas en el modelo.

Por lo tanto, ya no necesita hacer todos los hacks o soluciones propuestas como respuestas a su pregunta. Solo sigue estos pasos:

Primero definió una nueva propiedad de tipo DbQuery<T> donde T es el tipo de la clase que llevará los valores de columna de su consulta SQL. Entonces en tu DbContext tendrás esto:

public DbQuery<SomeModel> SomeModels { get; set; }

En segundo lugar, use el método FromSql como lo hace con DbSet<T> :

var result = context.SomeModels.FromSql("SQL_SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();

También tenga en cuenta que los DBContexts son clases parciales , por lo que puede crear uno o más archivos separados para organizar sus definiciones ''sin formato SQL DbQuery'' como mejor le convenga.

Answer 9

Sobre la base de las otras respuestas, he escrito este asistente que realiza la tarea, incluido el uso de ejemplo:

public static class Helper
{
    public static List<T> RawSqlQuery<T>(string query, Func<DbDataReader, T> map)
    {
        using (var context = new DbContext())
        {
            using (var command = context.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;

                context.Database.OpenConnection();

                using (var result = command.ExecuteReader())
                {
                    var entities = new List<T>();

                    while (result.Read())
                    {
                        entities.Add(map(result));
                    }

                    return entities;
                }
            }
        }
    }

Uso:

public class TopUser
{
    public string Name { get; set; }

    public int Count { get; set; }
}

var result = Helper.RawSqlQuery(
    "SELECT TOP 10 Name, COUNT(*) FROM Users U"
    + " INNER JOIN Signups S ON U.UserId = S.UserId"
    + " GROUP BY U.Name ORDER BY COUNT(*) DESC",
    x => new TopUser { Name = (string)x[0], Count = (int)x[1] });

result.ForEach(x => Console.WriteLine($"{x.Name,-25}{x.Count}"));

Planeo deshacerme de él tan pronto como se agregue el soporte incorporado. Según una statement de Arthur Vickers del equipo de EF Core, es una alta prioridad para la publicación 2.0. El problema está siendo rastreado here .

Answer 10

También puede usar QueryFirst . Al igual que Dapper, esto está totalmente fuera de EF. A diferencia de Dapper (o EF), no necesita mantener el POCO, edita su SQL SQL en un entorno real y se revalida continuamente contra la base de datos. Descargo de responsabilidad: soy el autor de QueryFirst.

Answer 11

Dapper para evitar esta restricción de Entity Framework Core.

IDbConnection.Query

está trabajando con consultas SQL o procedimientos almacenados con múltiples parámetros. Por cierto, es un poco más rápido (ver pruebas de referencia )

Dapper es fácil de aprender. Tardó 15 minutos en escribir y ejecutar el procedimiento almacenado con parámetros. De todos modos, puedes usar EF y Dapper. A continuación se muestra un ejemplo:

 public class PodborsByParametersService
{
    string _connectionString = null;


    public PodborsByParametersService(string connStr)
    {
        this._connectionString = connStr;

    }

    public IList<TyreSearchResult> GetTyres(TyresPodborView pb,bool isPartner,string partnerId ,int pointId)
    {

        string sqltext  "spGetTyresPartnerToClient";

        var p = new DynamicParameters();
        p.Add("@PartnerID", partnerId);
        p.Add("@PartnerPointID", pointId);

        using (IDbConnection db = new SqlConnection(_connectionString))
        {
            return db.Query<TyreSearchResult>(sqltext, p,null,true,null,CommandType.StoredProcedure).ToList();
        }


        }
}

Answer 12

Con Entity Framework 6 puede ejecutar algo como a continuación

Crear clase modal como

Public class User
{
        public int Id { get; set; }
        public string fname { get; set; }
        public string lname { get; set; }
        public string username { get; set; }
}

Ejecute el comando Raw DQL SQl de la siguiente manera:

var userList = datacontext.Database.SqlQuery<User>(@"SELECT u.Id ,fname , lname ,username FROM dbo.Users").ToList<User>();