与实体框架核心删除dbData.Database。我找不到一个解决方案来构建一个原始的SQL查询为我的全文搜索查询，将返回表数据和排名。
我所见过的在实体框架核心中构建原始SQL查询的唯一方法是通过dbData.Product。FromSql(“SQL脚本”);这是没有用的，因为我没有DbSet，将映射我在查询中返回的排名。
有什么想法?
当前回答
我更新了扩展方法从@AminRostami返回IAsyncEnumerable(这样LINQ过滤可以应用)，它的映射模型列名的记录从DB返回到模型(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();
}
其他回答
在其他答案的基础上，我写了这个助手来完成这个任务，包括示例用法:
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核心团队的Arthur Vickers的声明，这是2.0后的优先级。这个问题正在被追踪。
我更新了扩展方法从@AminRostami返回IAsyncEnumerable(这样LINQ过滤可以应用)，它的映射模型列名的记录从DB返回到模型(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();
}
查询数据:不存在实体
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"
}
]
实际上，您可以创建一个通用的存储库并做类似的事情
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);
}
你也可以使用QueryFirst。和Dapper一样，这完全不在EF的范围内。与Dapper(或EF)不同的是，您不需要维护POCO，只需在真实环境中编辑sql sql，并根据DB不断对其进行重新验证。免责声明:我是QueryFirst的作者。
推荐文章
- 实体框架核心:在上一个操作完成之前，在此上下文中开始的第二个操作
- 如何为构造函数定制Visual Studio的私有字段生成快捷方式?
- 如何使用JSON确保字符串是有效的JSON。网
- AppSettings从.config文件中获取值
- 通过HttpClient向REST API发布一个空体
- 如何检查IEnumerable是否为空或空?
- 自动化invokerrequired代码模式
- 在c#代码中设置WPF文本框的背景颜色
- 在c#中，什么是单子?
- c#和Java中的泛型有什么不同?和模板在c++ ?
- c#线程安全快速(est)计数器
- 如何将此foreach代码转换为Parallel.ForEach?
- 如何分裂()一个分隔字符串到一个列表<字符串>
- 如何转换列表<字符串>列表<int>?
- c#对象列表，我如何得到一个属性的和
