与实体框架核心删除dbData.Database。我找不到一个解决方案来构建一个原始的SQL查询为我的全文搜索查询,将返回表数据和排名。

我所见过的在实体框架核心中构建原始SQL查询的唯一方法是通过dbData.Product。FromSql(“SQL脚本”);这是没有用的,因为我没有DbSet,将映射我在查询中返回的排名。

有什么想法?


当前回答

我找到了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();

其他回答

我的案例使用了存储过程而不是原始SQL

创建一个类

Public class School
{
    [Key]
    public Guid SchoolId { get; set; }
    public string Name { get; set; }
    public string Branch { get; set; }
    public int NumberOfStudents  { get; set; }
}

下面在我的DbContext类上添加

public DbSet<School> SP_Schools { get; set; }

执行存储过程。

var MySchools = _db.SP_Schools.FromSqlRaw("GetSchools @schoolId, @page, @size ",
              new SqlParameter("schoolId", schoolId),
              new SqlParameter("page", page),
              new SqlParameter("size", size)))
.IgnoreQueryFilters();

添加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

查询数据:不存在实体

               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"
  }
]

这样做的实体框架核心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();

不是直接针对OP的场景,但因为我一直在努力解决这个问题,我想放弃这些让DbContext更容易执行原始SQL的ex.方法:

public static class DbContextCommandExtensions
{
  public static async Task<int> ExecuteNonQueryAsync(this DbContext context, string rawSql,
    params object[] parameters)
  {
    var conn = context.Database.GetDbConnection();
    using (var command = conn.CreateCommand())
    {
      command.CommandText = rawSql;
      if (parameters != null)
        foreach (var p in parameters)
          command.Parameters.Add(p);
      await conn.OpenAsync();
      return await command.ExecuteNonQueryAsync();
    }
  }

  public static async Task<T> ExecuteScalarAsync<T>(this DbContext context, string rawSql,
    params object[] parameters)
  {
    var conn = context.Database.GetDbConnection();
    using (var command = conn.CreateCommand())
    {
      command.CommandText = rawSql;
      if (parameters != null)
        foreach (var p in parameters)
          command.Parameters.Add(p);
      await conn.OpenAsync();
      return (T)await command.ExecuteScalarAsync();
    }
  }
}