与实体框架核心删除dbData.Database。我找不到一个解决方案来构建一个原始的SQL查询为我的全文搜索查询，将返回表数据和排名。
我所见过的在实体框架核心中构建原始SQL查询的唯一方法是通过dbData.Product。FromSql(“SQL脚本”);这是没有用的，因为我没有DbSet，将映射我在查询中返回的排名。
有什么想法?
当前回答
这样做的实体框架核心5，需要安装
Microsoft.EntityFrameworkCore.Relational
助手扩展方法
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
public static class EfHelper
{
public static DbTransaction GetDbTransaction(this IDbContextTransaction source)
{
return (source as IInfrastructure<DbTransaction>).Instance;
}
private class PropertyMapp
{
public string Name { get; set; }
public Type Type { get; set; }
public bool IsSame(PropertyMapp mapp)
{
if (mapp == null)
{
return false;
}
bool same = mapp.Name == Name && mapp.Type == Type;
return same;
}
}
public static IEnumerable<T> FromSqlQuery<T>(this DbContext context, string query, params object[] parameters) where T : new()
{
const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.NonPublic;
List<PropertyMapp> entityFields = (from PropertyInfo aProp in typeof(T).GetProperties(flags)
select new PropertyMapp
{
Name = aProp.Name,
Type = Nullable.GetUnderlyingType(aProp.PropertyType) ?? aProp.PropertyType
}).ToList();
List<PropertyMapp> dbDataReaderFields = new List<PropertyMapp>();
List<PropertyMapp> commonFields = null;
using (var command = context.Database.GetDbConnection().CreateCommand())
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
var currentTransaction = context.Database.CurrentTransaction;
if (currentTransaction != null)
{
command.Transaction = currentTransaction.GetDbTransaction();
}
command.CommandText = query;
if (parameters.Any())
{
command.Parameters.AddRange(parameters);
}
using (var result = command.ExecuteReader())
{
while (result.Read())
{
if (commonFields == null)
{
for (int i = 0; i < result.FieldCount; i++)
{
dbDataReaderFields.Add(new PropertyMapp { Name = result.GetName(i), Type = result.GetFieldType(i) });
}
commonFields = entityFields.Where(x => dbDataReaderFields.Any(d => d.IsSame(x))).Select(x => x).ToList();
}
var entity = new T();
foreach (var aField in commonFields)
{
PropertyInfo propertyInfos = entity.GetType().GetProperty(aField.Name);
var value = (result[aField.Name] == DBNull.Value) ? null : result[aField.Name]; //if field is nullable
propertyInfos.SetValue(entity, value, null);
}
yield return entity;
}
}
}
}
/*
* https://entityframeworkcore.com/knowledge-base/35631903/raw-sql-query-without-dbset---entity-framework-core
*/
public static IEnumerable<T> FromSqlQuery<T>(this DbContext context, string query, Func<DbDataReader, T> map, params object[] parameters)
{
using (var command = context.Database.GetDbConnection().CreateCommand())
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
var currentTransaction = context.Database.CurrentTransaction;
if (currentTransaction != null)
{
command.Transaction = currentTransaction.GetDbTransaction();
}
command.CommandText = query;
if (parameters.Any())
{
command.Parameters.AddRange(parameters);
}
using (var result = command.ExecuteReader())
{
while (result.Read())
{
yield return map(result);
}
}
}
}
}
模型
public class UserModel
{
public string Name { get; set; }
public string Email { get; set; }
public bool? IsDeleted { get; set; }
}
手动映射
List<UserModel> usersInDb = Db.FromSqlQuery
(
"SELECT Name, Email FROM Users WHERE Name=@paramName",
x => new UserModel
{
Name = (string)x[0],
Email = (string)x[1]
},
new SqlParameter("@paramName", user.Name)
)
.ToList();
usersInDb = Db.FromSqlQuery
(
"SELECT Name, Email FROM Users WHERE Name=@paramName",
x => new UserModel
{
Name = x["Name"] is DBNull ? "" : (string)x["Name"],
Email = x["Email"] is DBNull ? "" : (string)x["Email"]
},
new SqlParameter("@paramName", user.Name)
)
.ToList();
使用反射自动映射
List<UserModel> usersInDb = Db.FromSqlQuery<UserModel>
(
"SELECT Name, Email, IsDeleted FROM Users WHERE Name=@paramName",
new SqlParameter("@paramName", user.Name)
)
.ToList();
其他回答
现在，直到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注入。
dbData.Product.FromSql("SQL SCRIPT");
FromSql不支持全查询。例如，如果你想包含一个WHERE子句，它将被忽略。
一些链接:
使用实体框架核心执行原始SQL查询
原始SQL查询
不是直接针对OP的场景，但因为我一直在努力解决这个问题，我想放弃这些让DbContext更容易执行原始SQL的ex.方法:
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();
}
}
}
我使用Dapper绕过了Entity框架Core的这个约束。
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();
}
}
}
查询数据:不存在实体
string query = "SELECT r.Name as roleName, ur.roleId, u.Id as userId FROM dbo.AspNetUserRoles AS ur INNER JOIN dbo.AspNetUsers AS u ON ur.UserId = u.Id INNER JOIN dbo.AspNetRoles AS r ON ur.RoleId = r.Id ";
ICollection<object> usersWithRoles = new List<object>();
using (var command = _identityDBContext.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
await _identityDBContext.Database.OpenConnectionAsync();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
usersWithRoles.Add(new {
roleName = reader.GetFieldValueAsync<string>(0).Result,
roleId = reader.GetFieldValueAsync<string>(1).Result,
userId = reader.GetFieldValueAsync<string>(2).Result
});
}
}
}
详细:
[HttpGet]
[Route("GetAllUsersWithRoles")]
public async Task<IActionResult> GetAllUsersWithRoles()
{
string query = "SELECT r.Name as roleName, ur.roleId, u.Id as userId FROM dbo.AspNetUserRoles AS ur INNER JOIN dbo.AspNetUsers AS u ON ur.UserId = u.Id INNER JOIN dbo.AspNetRoles AS r ON ur.RoleId = r.Id ";
try
{
ICollection<object> usersWithRoles = new List<object>();
using (var command = _identityDBContext.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
await _identityDBContext.Database.OpenConnectionAsync();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
usersWithRoles.Add(new {
roleName = reader.GetFieldValueAsync<string>(0).Result,
roleId = reader.GetFieldValueAsync<string>(1).Result,
userId = reader.GetFieldValueAsync<string>(2).Result
});
}
}
}
return StatusCode(200, usersWithRoles); // Get all users
}
catch (Exception e)
{
return StatusCode(500, e);
}
}
结果如下所示:
[
{
"roleName": "admin",
"roleId": "7c9cb1be-e987-4ec1-ae4d-e4c9790f57d8",
"userId": "12eadc86-6311-4d5e-8be8-df30799df265"
},
{
"roleName": "user",
"roleId": "a0d5ef46-b1e6-4a53-91ce-9ff5959f1ed8",
"userId": "12eadc86-6311-4d5e-8be8-df30799df265"
},
{
"roleName": "user",
"roleId": "a0d5ef46-b1e6-4a53-91ce-9ff5959f1ed8",
"userId": "3e7cd970-8c52-4dd1-847c-f824671ea15d"
}
]
如果您使用EF Core 3.0或更新版本
你需要使用无键实体类型，以前称为查询类型:
该特性是在EF Core 2.1中以查询类型的名称添加的。 在EF Core 3.0中，这个概念被重命名为无键实体类型。的 [无键]数据注释在EFCore 5.0中可用。
要使用它们，你需要首先用[Keyless]数据注释标记你的类SomeModel，或者通过.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();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();
如果您正在使用EF Core 2.1
如果您正在使用2018年5月7日发布的EF Core 2.1候选版本1，您可以利用提议的新功能，即查询类型:
除了实体类型，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();
还要注意dbcontext是部分类，所以你可以创建一个或多个单独的文件来组织最适合你的“原始SQL DbQuery”定义。
推荐文章
- 我如何设置在一个组合框中选择的项目，以匹配我的字符串使用c# ?
- String与StringBuilder
- 如何在ASP中使用ILogger进行单元测试。网络核心
- Visual Studio 2017:显示方法参考
- Swagger UI Web Api文档呈现枚举字符串?
- 没有DbSet实体框架核心的原始SQL查询
- 将null分配给SqlParameter
- 任务构造函数中的取消令牌:为什么?
- 是否有一种方法来检查是否int是合法的enum在c# ?
- 远程主机强制关闭现有连接
- 为什么ReSharper想要使用'var'的一切?
- 如何检查一个对象是否为空?
- 为什么要删除不必要的c#使用指令?
- 为什么使用HttpClient进行同步连接
- 什么是MvcHtmlString，什么时候我应该使用它?
