Необработанный SQL-запрос без DbSet - основа платформы Entity Framework

Ответ 1

Если вы используете EF Core 2.1 Release Candidate 1, доступный с 7 мая 2018 года, вы можете воспользоваться предложенной новой функцией, которая представляет собой тип запроса.

Что такое тип запроса?

В дополнение к типам сущностей модель EF Core может содержать типы запросов, которые можно использовать для выполнения запросов к базе данных с данными, которые не сопоставлены с типами сущностей.

Когда использовать тип запроса?

Служит типом возврата для специальных запросов FromSql().

Сопоставление с представлениями базы данных.

Сопоставление с таблицами, для которых не определен первичный ключ.

Отображение на запросы, определенные в модели.

Таким образом, вам больше не нужно делать все хаки или обходные пути, предложенные в качестве ответов на ваш вопрос. Просто следуйте этим шагам:

Сначала вы определили новое свойство типа DbQuery<T> где T - это тип класса, который будет содержать значения столбцов вашего SQL-запроса. Итак, в вашем DbContext вы будете иметь это:

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

Во-вторых, используйте метод FromSql как вы делаете с DbSet<T>:

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

Также обратите внимание, что DBContexts являются частичными классами, поэтому вы можете создать один или несколько отдельных файлов, чтобы организовать ваши определения "необработанного SQL DbQuery" так, как вам больше подходит.

Ответ 2

В EF Core вы больше не можете выполнять "бесплатный" raw sql. Вы должны определить класс POCO и DbSet для этого класса. В вашем случае вам нужно определить Rank:

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

Так как, конечно же, будет полезно включить вызов .AsNoTracking().

Ответ 3

Основываясь на других ответах, я написал этого помощника, который выполняет задачу, включая пример использования:

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

Использование:

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

Я планирую избавиться от него, как только добавится встроенная поддержка. Согласно заявлению Артура Викерса из команды EF Core, это высокий приоритет для post 2.0. Проблема здесь отслеживается.

Ответ 4

Вы можете выполнить raw sql в EF Core - добавьте этот класс в свой проект. Это позволит вам выполнить необработанный SQL и получить исходные результаты без необходимости определять POCO и DBSet. См. https://github.com/aspnet/EntityFramework/issues/1862#issuecomment-220787464 для исходного примера.

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

Вот пример того, как его использовать:

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

Ответ 5

Пока, пока не появилось что-то новое от EFCore, я бы использовал команду и отобразил ее вручную

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

Попробуйте SqlParameter, чтобы избежать Sql Injection.

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

FromSql не работает с полным запросом. Пример, если вы хотите включить предложение WHERE, оно будет проигнорировано.

Некоторые ссылки:

Выполнение необработанных SQL-запросов с использованием Entity Framework Core

Необработанные SQL-запросы

Ответ 6

В Core 2.1 вы можете сделать что-то вроде этого:

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

и затем определите 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;
}

Таким образом, модель Ranks не будет создана в вашей БД.

Теперь в вашем контроллере/действии вы можете позвонить:

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

Таким образом вы можете вызвать Raw SQL-процедуры.

Ответ 7

Вы можете использовать это (из 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);
        }
    }
}

Ответ 8

Если вы только хотите выполнить запрос и хотите вернуть int. EFCore 2 имеет DbContext.Database.ExecuteSqlCommand("Yourqueryhere").

Редактировать:

ExecuteSqlCommand и ExecuteSqlCommandAsync определены в пространстве имен Microsoft.EntityFrameworkCore.Relational. Убедитесь, что на него ссылаются.

Ответ 9

Не нацеливаясь непосредственно на сценарий OP, но так как я боролся с этим, я хотел бы отбросить эти бывшие. методы, облегчающие выполнение необработанного SQL с помощью 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();
    }
  }
}

Ответ 10

Я использовал Dapper, чтобы обойти это ограничение в ядре инфраструктуры Entity.

IDbConnection.Query

работает либо с SQL-запросом, либо с хранимой процедурой с несколькими параметрами. Кстати, это немного быстрее (см. Тестовые тесты)

Dapper легко учиться. Потребовалось 15 минут, чтобы написать и запустить хранимую процедуру с параметрами. В любом случае вы можете использовать как EF, так и Dapper. Ниже приведен пример:

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


        }
}

Ответ 11

Вы также можете использовать QueryFirst. Как и Dapper, это полностью вне EF. В отличие от Dapper (или EF), вам не нужно поддерживать POCO, вы редактируете SQL-код в реальной среде и постоянно пересматриваете его. Отказ от ответственности: я автор QueryFirst.

Ответ 12

С Entity Framework 6 вы можете выполнить что-то вроде ниже

Создать модальный класс как

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

Выполните команду Raw DQL SQl, как показано ниже:

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