与实体框架核心删除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();
}
其他回答
在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()
更多信息可以在这里找到
这样做的实体框架核心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();
我更新了扩展方法从@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();
}
我使用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();
}
}
}
我的案例使用了存储过程而不是原始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();
推荐文章
- 实体框架核心:在上一个操作完成之前，在此上下文中开始的第二个操作
- 如何为构造函数定制Visual Studio的私有字段生成快捷方式?
- 如何使用JSON确保字符串是有效的JSON。网
- AppSettings从.config文件中获取值
- 通过HttpClient向REST API发布一个空体
- 如何检查IEnumerable是否为空或空?
- 自动化invokerrequired代码模式
- 在c#代码中设置WPF文本框的背景颜色
- 在c#中，什么是单子?
- c#和Java中的泛型有什么不同?和模板在c++ ?
- c#线程安全快速(est)计数器
- 如何将此foreach代码转换为Parallel.ForEach?
- 如何分裂()一个分隔字符串到一个列表<字符串>
- 如何转换列表<字符串>列表<int>?
- c#对象列表，我如何得到一个属性的和
