与实体框架核心删除dbData.Database。我找不到一个解决方案来构建一个原始的SQL查询为我的全文搜索查询,将返回表数据和排名。
我所见过的在实体框架核心中构建原始SQL查询的唯一方法是通过dbData.Product。FromSql(“SQL脚本”);这是没有用的,因为我没有DbSet,将映射我在查询中返回的排名。
有什么想法?
与实体框架核心删除dbData.Database。我找不到一个解决方案来构建一个原始的SQL查询为我的全文搜索查询,将返回表数据和排名。
我所见过的在实体框架核心中构建原始SQL查询的唯一方法是通过dbData.Product。FromSql(“SQL脚本”);这是没有用的,因为我没有DbSet,将映射我在查询中返回的排名。
有什么想法?
当前回答
添加Nuget包- Microsoft.EntityFrameworkCore.Relational
using Microsoft.EntityFrameworkCore;
...
await YourContext.Database.ExecuteSqlCommandAsync("... @p0, @p1", param1, param2 ..)
这将以int形式返回行号
参见- https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlcommand?view=efcore-3.0
其他回答
在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过程。
我更新了扩展方法从@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();
}
我找到了EntityFrameworkCore包。RawSQLExtensions在github上。要使用它,添加nuget包。
<PackageReference Include="EntityFrameworkCore.RawSQLExtensions" Version="1.2.0" />
这个库没有文档,但下面是我在。net 6 + EF Core 6 + Npgsql 6中使用它
public class DbResult
{
public string Name { get; set; }
public int Age { get; set; }
}
using EntityFrameworkCore.RawSQLExtensions.Extensions;
var results = await context.Database
.SqlQuery<DbResult>(
@"select name, age from ""users"" where age > @Age",
new NpgsqlParameter("@Age", 15))
.ToListAsync();
你也可以使用QueryFirst。和Dapper一样,这完全不在EF的范围内。与Dapper(或EF)不同的是,您不需要维护POCO,只需在真实环境中编辑sql sql,并根据DB不断对其进行重新验证。免责声明:我是QueryFirst的作者。
实际上,您可以创建一个通用的存储库并做类似的事情
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);
}