与实体框架核心删除dbData.Database。我找不到一个解决方案来构建一个原始的SQL查询为我的全文搜索查询，将返回表数据和排名。
我所见过的在实体框架核心中构建原始SQL查询的唯一方法是通过dbData.Product。FromSql(“SQL脚本”);这是没有用的，因为我没有DbSet，将映射我在查询中返回的排名。
有什么想法?
当前回答
试试这个(创建扩展方法)
public static List<T> ExecuteQuery<T>(this dbContext db, string query) where T : class, new()
{
using (var command = db.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
db.Database.OpenConnection();
using (var reader = command.ExecuteReader())
{
var lst = new List<T>();
var lstColumns = new T().GetType().GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic).ToList();
while (reader.Read())
{
var newObject = new T();
for (var i = 0; i < reader.FieldCount; i++)
{
var name = reader.GetName(i);
PropertyInfo prop = lstColumns.FirstOrDefault(a => a.Name.ToLower().Equals(name.ToLower()));
if (prop == null)
{
continue;
}
var val = reader.IsDBNull(i) ? null : reader[i];
prop.SetValue(newObject, val, null);
}
lst.Add(newObject);
}
return lst;
}
}
}
用法:
var db = new dbContext();
string query = @"select ID , Name from People where ... ";
var lst = db.ExecuteQuery<PeopleView>(query);
我的模型:(不在DbSet中):
public class PeopleView
{
public int ID { get; set; }
public string Name { get; set; }
}
在。net core 2.2和3.0中测试。
注意:该方案性能较慢
其他回答
在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()
更多信息可以在这里找到
你可以在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]);
}
}
现在，直到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查询
添加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
这样做的实体框架核心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();
推荐文章
- 我如何设置在一个组合框中选择的项目，以匹配我的字符串使用c# ?
- String与StringBuilder
- 如何在ASP中使用ILogger进行单元测试。网络核心
- Visual Studio 2017:显示方法参考
- Swagger UI Web Api文档呈现枚举字符串?
- 没有DbSet实体框架核心的原始SQL查询
- 将null分配给SqlParameter
- 任务构造函数中的取消令牌:为什么?
- 是否有一种方法来检查是否int是合法的enum在c# ?
- 远程主机强制关闭现有连接
- 为什么ReSharper想要使用'var'的一切?
- 如何检查一个对象是否为空?
- 为什么要删除不必要的c#使用指令?
- 为什么使用HttpClient进行同步连接
- 什么是MvcHtmlString，什么时候我应该使用它?
