与实体框架核心删除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"
  }
]