没有DbSet实体框架核心的原始SQL查询

与实体框架核心删除dbData.Database。我找不到一个解决方案来构建一个原始的SQL查询为我的全文搜索查询，将返回表数据和排名。

我所见过的在实体框架核心中构建原始SQL查询的唯一方法是通过dbData.Product。FromSql(“SQL脚本”);这是没有用的，因为我没有DbSet，将映射我在查询中返回的排名。

有什么想法?

当前回答

你可以在EF Core中执行原始sql -将这个类添加到你的项目中。这将允许您执行原始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]);
    }
}

2017-03-21 16:22:33

其他回答

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

2017-03-21 16:22:33

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

这样就不会在数据库中创建rank模型。

现在在你的控制器/动作中你可以调用:

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

这样就可以调用原始SQL过程。

2018-06-19 10:22:30

我的案例使用了存储过程而不是原始SQL

创建一个类

Public class School
{
    [Key]
    public Guid SchoolId { get; set; }
    public string Name { get; set; }
    public string Branch { get; set; }
    public int NumberOfStudents  { get; set; }
}

下面在我的DbContext类上添加

public DbSet<School> SP_Schools { get; set; }

执行存储过程。

var MySchools = _db.SP_Schools.FromSqlRaw("GetSchools @schoolId, @page, @size ",
              new SqlParameter("schoolId", schoolId),
              new SqlParameter("page", page),
              new SqlParameter("size", size)))
.IgnoreQueryFilters();

2020-02-22 15:54:07

你可以用这个:

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()))
        {
            // share the current database transaction, if one exists
            var transaction = db.Database.CurrentTransaction;
            if (transaction != null)
                db2.Database.UseTransaction(transaction.GetDbTransaction());
            return db2.Set<T>().FromSqlRaw(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)
        {
            optionsBuilder.UseSqlServer(connection, options => options.EnableRetryOnFailure());

            base.OnConfiguring(optionsBuilder);
        }

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

以及用法:

    using (var db = new Db())
    {
        var results = db.SqlQuery<ArbitraryType>("select 1 id, 'joe' name");
        //or with an anonymous type like this
        var results2 = db.SqlQuery(() => new { id =1, name=""},"select 1 id, 'joe' name");
    }

2019-01-10 09:22:06

在EF核心你不再可以执行“自由”原始sql。您需要为该类定义一个POCO类和一个DbSet。在你的情况下，你需要定义Rank:

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

因为它肯定是只读的，所以包含. asnotracking()调用会很有用。

EF Core 3.0的突破性变化:

DbQuery()现在已经过时了，取而代之的应该是DbSet()。如果你有一个无键实体，即它不需要主键，你可以使用HasNoKey()方法:

ModelBuilder.Entity<SomeModel>().HasNoKey()

更多信息可以在这里找到

2016-02-25 16:58:40

没有DbSet实体框架核心的原始SQL查询

推荐文章

最新文章

标签