无dbset的原始sql查询-实体框架核心

4xrmg8kj 于 2021-07-24 发布在 Java

关注(0)|答案(18)|浏览(482)

移除实体框架核心 dbData.Database.SqlQuery<SomeModel> 我找不到为全文搜索查询构建原始sql查询的解决方案，该查询将返回表数据和秩。
我见过的在实体框架核心中构建原始sql查询的唯一方法是通过 dbData.Product.FromSql("SQL SCRIPT"); 这是没有用的，因为我没有dbsetMap我在查询中返回的秩。
有什么想法吗？？？

c#entity-framework-core

来源：https://stackoverflow.com/questions/62531314/how-to-get-sql-query-to-dto-in-asp-net-core

18条答案

按热度按时间

不是直接针对op的场景，但是因为我一直在努力解决这个问题，所以我想放弃这些ex.方法，这些方法使得使用 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();
    }
  }
}

赞(0）回复(0）举报 2021-07-24

使用EntityFramework6，您可以执行如下操作
将模态类创建为

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

执行原始dql sql命令，如下所示：

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

赞(0）回复(0）举报 2021-07-24

我更新了@aminorstami的扩展方法，以返回iasyncenumerable（因此可以应用linq过滤），并将db返回的记录的model列名Map到models（用ef core 5测试）：
扩展本身：

public static class QueryHelper
{
    private static string GetColumnName(this MemberInfo info)
    {
        List<ColumnAttribute> list = info.GetCustomAttributes<ColumnAttribute>().ToList();
        return list.Count > 0 ? list.Single().Name : info.Name;
    }
    /// <summary>
    /// Executes raw query with parameters and maps returned values to column property names of Model provided.
    /// Not all properties are required to be present in model (if not present - null)
    /// </summary>
    public static async IAsyncEnumerable<T> ExecuteQuery<T>(
        [NotNull] this DbContext db,
        [NotNull] string query,
        [NotNull] params SqlParameter[] parameters)
        where T : class, new()
    {
        await using DbCommand command = db.Database.GetDbConnection().CreateCommand();
        command.CommandText = query;
        command.CommandType = CommandType.Text;
        if (parameters != null)
        {
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
        }
        await db.Database.OpenConnectionAsync();
        await using DbDataReader reader = await command.ExecuteReaderAsync();
        List<PropertyInfo> lstColumns = new T().GetType()
            .GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic).ToList();
        while (await reader.ReadAsync())
        {
            T newObject = new();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                string name = reader.GetName(i);
                PropertyInfo prop = lstColumns.FirstOrDefault(a => a.GetColumnName().Equals(name));
                if (prop == null)
                {
                    continue;
                }
                object val = await reader.IsDBNullAsync(i) ? null : reader[i];
                prop.SetValue(newObject, val, null);
            }
            yield return newObject;
        }
    }
}

使用的模型（请注意，列名与实际属性名不同）：

public class School
{
    [Key] [Column("SCHOOL_ID")] public int SchoolId { get; set; }

    [Column("CLOSE_DATE", TypeName = "datetime")]
    public DateTime? CloseDate { get; set; }

    [Column("SCHOOL_ACTIVE")] public bool? SchoolActive { get; set; }
}

实际使用：

public async Task<School> ActivateSchool(int schoolId)
{
    // note that we're intentionally not returning "SCHOOL_ACTIVE" with select statement
    // this might be because of certain IF condition where we return some other data
    return await _context.ExecuteQuery<School>(
        "UPDATE SCHOOL SET SCHOOL_ACTIVE = 1 WHERE SCHOOL_ID = @SchoolId; SELECT SCHOOL_ID, CLOSE_DATE FROM SCHOOL",
        new SqlParameter("@SchoolId", schoolId)
    ).SingleAsync();
}

赞(0）回复(0）举报 2021-07-24

我知道这是一个老问题，但它可能有助于人们调用存储过程而不将dto添加为dbset。
https://stackoverflow.com/a/62058345/3300944

赞(0）回复(0）举报 2021-07-24

也可以使用queryfirst。像潇洒，这是完全以外的环境足迹。与dapper（或ef）不同的是，您不需要维护poco，您可以在真实的环境中编辑您的sql，并且它会不断地根据db进行重新验证。免责声明：我是queryfirst的作者。

赞(0）回复(0）举报 2021-07-24

这个解决方案严重依赖于@pius的解决方案。我想添加支持查询参数的选项来帮助减轻sql注入，还想将其作为dbcontext databasefacade for entity framework core的扩展，使其更加集成。
首先用扩展名创建一个新类：

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;

namespace EF.Extend
{

    public static class ExecuteSqlExt
    {
        /// <summary>
        /// Execute raw SQL query with query parameters
        /// </summary>
        /// <typeparam name="T">the return type</typeparam>
        /// <param name="db">the database context database, usually _context.Database</param>
        /// <param name="query">the query string</param>
        /// <param name="map">the map to map the result to the object of type T</param>
        /// <param name="queryParameters">the collection of query parameters, if any</param>
        /// <returns></returns>
        public static List<T> ExecuteSqlRawExt<T, P>(this DatabaseFacade db, string query, Func<DbDataReader, T> map, IEnumerable<P> queryParameters = null)
        {
            using (var command = db.GetDbConnection().CreateCommand())
            {
                if((queryParameters?.Any() ?? false))
                    command.Parameters.AddRange(queryParameters.ToArray());

                command.CommandText = query;
                command.CommandType = CommandType.Text;

                db.OpenConnection();

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

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

                    return entities;
                }
            }

        }
    }

}

请注意，在上面，“t”是返回的类型，“p”是查询参数的类型，如果您使用的是mysql、sql等，则查询参数的类型会有所不同。
接下来我们将展示一个示例。我使用的是mysql ef核心功能，因此我们将看到如何将上面的通用扩展与更具体的mysql实现结合使用：

//add your using statement for the extension at the top of your Controller
//with all your other using statements
using EF.Extend;

//then your your Controller looks something like this
namespace Car.Api.Controllers
{

    //Define a quick Car class for the custom return type
    //you would want to put this in it's own class file probably
    public class Car
    {
        public string Make { get; set; }
        public string Model { get; set; }
        public string DisplayTitle { get; set; }
    }

    [ApiController]
    public class CarController : ControllerBase
    {
        private readonly ILogger<CarController> _logger;
        //this would be your Entity Framework Core context
        private readonly CarContext _context;

        public CarController(ILogger<CarController> logger, CarContext context)
        {
            _logger = logger;
            _context = context;
        }

        //... more stuff here ...

       /// <summary>
       /// Get car example
       /// </summary>
       [HttpGet]
       public IEnumerable<Car> Get()
       {
           //instantiate three query parameters to pass with the query
           //note the MySqlParameter type is because I'm using MySql
           MySqlParameter p1 = new MySqlParameter
           {
               ParameterName = "id1",
               Value = "25"
           };

           MySqlParameter p2 = new MySqlParameter
           {
               ParameterName = "id2",
               Value = "26"
           };

           MySqlParameter p3 = new MySqlParameter
           {
               ParameterName = "id3",
               Value = "27"
           };

           //add the 3 query parameters to an IEnumerable compatible list object
           List<MySqlParameter> queryParameters = new List<MySqlParameter>() { p1, p2, p3 };

           //note the extension is now easily accessed off the _context.Database object
           //also note for ExecuteSqlRawExt<Car, MySqlParameter>
           //Car is my return type "T"
           //MySqlParameter is the specific DbParameter type MySqlParameter type "P"
           List<Car> result = _context.Database.ExecuteSqlRawExt<Car, MySqlParameter>(
        "SELECT Car.Make, Car.Model, CONCAT_WS('', Car.Make, ' ', Car.Model) As DisplayTitle FROM Car WHERE Car.Id IN(@id1, @id2, @id3)",
        x => new Car { Make = (string)x[0], Model = (string)x[1], DisplayTitle = (string)x[2] }, 
        queryParameters);

           return result;
       }
    }
}

查询将返回如下行：
“福特”，“探险家”，“福特探险家”
“特斯拉”，“x型”，“特斯拉x型”
显示标题没有定义为数据库列，因此默认情况下它不会是ef car模型的一部分。我喜欢这种方法作为许多可能的解决方案之一。本页上的其他答案引用了[notmapped]decorator解决这个问题的其他方法，根据您的用例，这可能是更合适的方法。
请注意，这个示例中的代码显然比它需要的更详细，但我认为它使示例更清晰。

赞(0）回复(0）举报 2021-07-24

我的案例使用存储过程而不是原始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();

赞(0）回复(0）举报 2021-07-24

我使用dapper绕过实体框架核心的约束。

IDbConnection.Query

正在使用sql查询或具有多个参数的存储过程。顺便说一下，它要快一点（参见基准测试）
潇洒易学。用参数编写和运行存储过程花了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();
        }

        }
}

赞(0）回复(0）举报 2021-07-24

实际上，您可以创建一个通用存储库并执行以下操作

public class GenericRepository<TEntity> : IGenericRepository<TEntity> where TEntity : BaseEntity
{
    private readonly DataContext context;
    private readonly DbSet<TEntity> dbSet;

    public GenericRepository(DataContext context)
    {
        this.context = context;
        this.dbSet = context.Set<TEntity>();
    }

    public IEnumerable<TEntity> ExecuteCommandQuery(string command)
        => dbSet.FromSqlRaw(command);

}

赞(0）回复(0）举报 2021-07-24

这取决于您使用的是ef core 2.1还是ef core 3及更高版本。

如果您使用的是ef core 2.1

如果您使用的是自2018年5月7日起推出的ef core 2.1 release candidate 1，那么您可以利用提议的新功能，即查询类型。
什么是查询类型？
除了实体类型之外，ef核心模型还可以包含查询类型，这些类型可用于对未Map到实体类型的数据执行数据库查询。
何时使用查询类型？
用作临时fromsql（）查询的返回类型。
Map到数据库视图。
Map到未定义主键的表。
Map到模型中定义的查询。
因此，你不再需要做所有的黑客或解决方案提出的答案你的问题。只需遵循以下步骤：
首先定义了类型为的新属性 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();

还要注意的是 DdContext s是分部类，因此您可以创建一个或多个单独的文件来组织最适合您的“原始sqldbquery”定义。

如果您使用的是ef core 3.0及更高版本

查询类型现在称为无键实体类型。如上所述，查询类型是在efcore2.1中引入的。如果您使用的是efcore3.0或更高版本，那么现在应该考虑使用无键实体类型，因为查询类型现在被标记为过时。
这个特性是在efcore2.1中以查询类型的名称添加的。在efcore3.0中，这个概念被重命名为无键实体类型。[keyless]数据注解在efcore 5.0中可用。
对于何时使用无键实体类型，我们仍然有与查询类型相同的场景。
所以要使用它，你需要首先标记你的班级 SomeModel 与 [Keyless] 数据注解或通过fluent配置 .HasNoKey() 方法调用如下：

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

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

配置之后，您可以使用这里介绍的方法之一执行sql查询。例如，您可以使用以下方法：

var result = context.SomeModels.FromSqlRaw("SQL SCRIPT").ToList();

赞(0）回复(0）举报 2021-07-24

添加nuget包-microsoft.entityframeworkcore.relational

using Microsoft.EntityFrameworkCore;
...
await YourContext.Database.ExecuteSqlCommandAsync("... @p0, @p1", param1, param2 ..)

这将以整数形式返回行号
见-https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlcommand?view=efcore-3.0

赞(0）回复(0）举报 2021-07-24

试试这个：（创建扩展方法）

public static List<T> ExecuteQuery<T>(this dbContext db, string query) where T : class, new()
        {
            using (var command = db.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;

                db.Database.OpenConnection();

                using (var reader = command.ExecuteReader())
                {
                    var lst = new List<T>();
                    var lstColumns = new T().GetType().GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic).ToList();
                    while (reader.Read())
                    {
                        var newObject = new T();
                        for (var i = 0; i < reader.FieldCount; i++)
                        {
                            var name = reader.GetName(i);
                            PropertyInfo prop = lstColumns.FirstOrDefault(a => a.Name.ToLower().Equals(name.ToLower()));
                            if (prop == null)
                            {
                                continue;
                            }
                            var val = reader.IsDBNull(i) ? null : reader[i];
                            prop.SetValue(newObject, val, null);
                        }
                        lst.Add(newObject);
                    }

                    return lst;
                }
            }
        }

用法：

var db = new dbContext();
string query = @"select ID , Name from People where ... ";
var lst = db.ExecuteQuery<PeopleView>(query);

我的模特：（不在） DbSet ):

public class PeopleView
{
    public int ID { get; set; }
    public string Name { get; set; }
}

测试时间 .netCore 2.2 and 3.0 .
注意：此解决方案性能较慢

赞(0）回复(0）举报 2021-07-24

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

这样就不会在数据库中创建等级模型。
现在在控制器/操作中，您可以调用：

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

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

赞(0）回复(0）举报 2021-07-24

你可以用这个（从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.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");
    }

赞(0）回复(0）举报 2021-07-24

您可以在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]);
    }
}

赞(0）回复(0）举报 2021-07-24

相关问题

热门标签

Java query python Node 开发语言 request Util 数据库 Table 后端算法 Logger Message Element Parser

最新问答

xxl-job 安全组扫描到执行器端口服务存在信息泄露漏洞
回答(1) 发布于 4个月前
xxl-job 不能和nacos兼容？
回答(3) 发布于 4个月前
xxl-job 任务执行完后无法结束，日志一直转圈
回答(3) 发布于 4个月前
xxl-job-admin页面上查看调度日志样式问题
回答(1) 发布于 4个月前
xxl-job 参数512字符限制能否去掉
回答(1) 发布于 4个月前