与实体框架核心删除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
其他回答
现在,直到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查询
你可以在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]);
}
}
添加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
实际上,您可以创建一个通用的存储库并做类似的事情
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);
}
你可以用这个:
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()))
{
// share the current database transaction, if one exists
var transaction = db.Database.CurrentTransaction;
if (transaction != null)
db2.Database.UseTransaction(transaction.GetDbTransaction());
return db2.Set<T>().FromSqlRaw(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)
{
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");
}