与实体框架核心删除dbData.Database。我找不到一个解决方案来构建一个原始的SQL查询为我的全文搜索查询,将返回表数据和排名。
我所见过的在实体框架核心中构建原始SQL查询的唯一方法是通过dbData.Product。FromSql(“SQL脚本”);这是没有用的,因为我没有DbSet,将映射我在查询中返回的排名。
有什么想法?
与实体框架核心删除dbData.Database。我找不到一个解决方案来构建一个原始的SQL查询为我的全文搜索查询,将返回表数据和排名。
我所见过的在实体框架核心中构建原始SQL查询的唯一方法是通过dbData.Product。FromSql(“SQL脚本”);这是没有用的,因为我没有DbSet,将映射我在查询中返回的排名。
有什么想法?
当前回答
我之所以提出这个问题,是因为我们在实体框架6中有超过100个SqlQuery的无实体使用实例,所以按照微软建议的方式在我们的例子中并不容易工作。
此外,在从EF迁移到EFC的过程中,我们不得不维持一个EF(实体框架6)/ EFC(实体框架核心5)代码库好几个月。代码库相当大,根本不可能“一夜之间”迁移。
下面的答案是基于上面的答案,这只是一个小的扩展,使它们适用于更多的边缘情况。
首先,对于每个基于EF的项目,我们创建了一个基于EFC的项目(例如MyProject)。csproj ==> MyProject_EFC.csproj),在所有这样的EFC项目中我们定义了常量EFCORE。如果你正在做一个快速的一次性迁移从EF到EFC,那么你不需要,你可以保持什么里面# If EFCORE…删除#else里面的内容# endif下面。
下面是主要的互操作扩展类。
using System;
using System.Collections.Generic;
using System.Threading;
#if EFCORE
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Storage;
using Database = Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade;
using MoreLinq.Extensions;
#else
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
#endif
namespace YourNameSpace.EntityFrameworkCore
{
/// <summary>
/// Collection of extension methods to simplify migration from EF to EFC.
/// </summary>
public static class EntityFrameworkCoreInterop
{
/// <summary>
/// https://stackoverflow.com/questions/6637679/reflection-get-attribute-name-and-value-on-property
/// </summary>
public static TAttribute? TryGetAttribute<TAttribute>(this PropertyInfo prop) where TAttribute : Attribute =>
prop.GetCustomAttributes(true).TryGetAttribute<TAttribute>();
public static TAttribute? TryGetAttribute<TAttribute>(this Type t) where TAttribute : Attribute =>
t.GetCustomAttributes(true).TryGetAttribute<TAttribute>();
public static TAttribute? TryGetAttribute<TAttribute>(this IEnumerable<object> attrs) where TAttribute : Attribute
{
foreach (object attr in attrs)
{
switch (attr)
{
case TAttribute t:
{
return t;
}
}
}
return null;
}
/// <summary>
/// Returns true if the source string matches *any* of the passed-in strings (case insensitive)
/// </summary>
public static bool EqualsNoCase(this string? s, params string?[]? targets)
{
if (s == null && (targets == null || targets.Length == 0))
{
return true;
}
if (targets == null)
{
return false;
}
return targets.Any(t => string.Equals(s, t, StringComparison.OrdinalIgnoreCase));
}
#if EFCORE
public class EntityException : Exception
{
public EntityException(string message) : base(message)
{
}
}
public static TEntity GetEntity<TEntity>(this EntityEntry<TEntity> entityEntry)
where TEntity : class => entityEntry.Entity;
#region SqlQuery Interop
/// <summary>
/// kk:20210727 - This is a little bit ugly but given that this interop method is used just once,
/// it is not worth spending more time on it.
/// </summary>
public static List<T> ToList<T>(this IOrderedAsyncEnumerable<T> e) =>
Task.Run(() => e.ToListAsync().AsTask()).GetAwaiter().GetResult();
private static string GetColumnName(this MemberInfo info) =>
info.GetCustomAttributes().TryGetAttribute<ColumnAttribute>()?.Name ?? info.Name;
/// <summary>
/// See: https://stackoverflow.com/questions/35631903/raw-sql-query-without-dbset-entity-framework-core
/// 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 the model. If not present then they will be set to nulls.
/// </summary>
private static async IAsyncEnumerable<T> ExecuteQuery<T>(this Database database, string query, params object[] parameters)
{
await using DbCommand command = database.GetDbConnection().CreateCommand();
command.CommandText = query;
command.CommandType = CommandType.Text;
if (database.CurrentTransaction != null)
{
command.Transaction = database.CurrentTransaction.GetDbTransaction();
}
foreach (var parameter in parameters)
{
// They are supposed to be of SqlParameter type but are passed as objects.
command.Parameters.Add(parameter);
}
await database.OpenConnectionAsync();
await using DbDataReader reader = await command.ExecuteReaderAsync();
var t = typeof(T);
// TODO kk:20210825 - I do know that the code below works as we use it in some other place where it does work.
// However, I am not 100% sure that R# proposed version does. Check and refactor when time permits.
//
// ReSharper disable once CheckForReferenceEqualityInstead.1
if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
{
t = Nullable.GetUnderlyingType(t)!;
}
var lstColumns = t
.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic)
.ToList();
while (await reader.ReadAsync())
{
if (t.IsPrimitive || t == typeof(string) || t == typeof(DateTime) || t == typeof(Guid) || t == typeof(decimal))
{
var val = await reader.IsDBNullAsync(0) ? null : reader[0];
yield return (T) val!;
}
else
{
var newObject = Activator.CreateInstance<T>();
for (var i = 0; i < reader.FieldCount; i++)
{
var name = reader.GetName(i);
var val = await reader.IsDBNullAsync(i) ? null : reader[i];
var prop = lstColumns.FirstOrDefault(a => a.GetColumnName().EqualsNoCase(name));
if (prop == null)
{
continue;
}
prop.SetValue(newObject, val, null);
}
yield return newObject;
}
}
}
#endregion
public static DbRawSqlQuery<TElement> SqlQuery<TElement>(this Database database, string sql, params object[] parameters) =>
new(database, sql, parameters);
public class DbRawSqlQuery<TElement> : IAsyncEnumerable<TElement>
{
private readonly IAsyncEnumerable<TElement> _elements;
internal DbRawSqlQuery(Database database, string sql, params object[] parameters) =>
_elements = ExecuteQuery<TElement>(database, sql, parameters);
public IAsyncEnumerator<TElement> GetAsyncEnumerator(CancellationToken cancellationToken = new ()) =>
_elements.GetAsyncEnumerator(cancellationToken);
public async Task<TElement> SingleAsync() => await _elements.SingleAsync();
public TElement Single() => Task.Run(SingleAsync).GetAwaiter().GetResult();
public async Task<TElement> FirstAsync() => await _elements.FirstAsync();
public TElement First() => Task.Run(FirstAsync).GetAwaiter().GetResult();
public async Task<TElement?> SingleOrDefaultAsync() => await _elements.SingleOrDefaultAsync();
public async Task<int> CountAsync() => await _elements.CountAsync();
public async Task<List<TElement>> ToListAsync() => await _elements.ToListAsync();
public List<TElement> ToList() => Task.Run(ToListAsync).GetAwaiter().GetResult();
}
#endif
}
}
用法与以前的EF用法没有区别:
public async Task<List<int>> GetMyResults()
{
using var ctx = GetMyDbContext();
const string sql = "select 1 as Result";
return await ctx.GetDatabase().SqlQuery<int>(sql).ToListAsync();
}
其中GetMyDbContext是一个获取数据库上下文的方法,GetDatabase是一个返回((DbContext)上下文的一行互操作。给定IMyDbContext的数据库:DbContext。这是为了简化同时进行的EF / EFC操作。
这适用于基本类型(上面的例子)、实体、本地类(但不包括匿名类)。通过GetColumnName支持列重命名,但是,…上面已经做过了。
其他回答
这样做的实体框架核心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();
在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过程。
在实体框架6中,你可以执行如下内容
创建模态类为
Public class User
{
public int Id { get; set; }
public string fname { get; set; }
public string lname { get; set; }
public string username { get; set; }
}
执行Raw DQL SQl命令如下所示:
var userList = datacontext.Database.SqlQuery<User>(@"SELECT u.Id ,fname , lname ,username FROM dbo.Users").ToList<User>();
在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()
更多信息可以在这里找到
查询数据:不存在实体
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"
}
]