如果您使用EF Core 3.0或更新版本

你需要使用无键实体类型，以前称为查询类型:

该特性是在EF Core 2.1中以查询类型的名称添加的。 在EF Core 3.0中，这个概念被重命名为无键实体类型。的 [无键]数据注释在EFCore 5.0中可用。

要使用它们，你需要首先用[Keyless]数据注释标记你的类SomeModel，或者通过.HasNoKey()方法调用进行流畅配置，如下所示:

public DbSet<SomeModel> SomeModels { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<SomeModel>().HasNoKey();
}

配置完成后，可以使用这里解释的方法之一来执行SQL查询。例如，你可以用这个:

var result = context.SomeModels.FromSqlRaw("SQL SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();

如果您正在使用EF Core 2.1

如果您正在使用2018年5月7日发布的EF Core 2.1候选版本1，您可以利用提议的新功能，即查询类型:

除了实体类型，EF Core模型还可以包含查询类型， 哪些可以用于对数据进行数据库查询 没有映射到实体类型。

什么时候使用查询类型?

作为临时FromSql()查询的返回类型。 映射到数据库视图。 映射到没有定义主键的表。 映射到模型中定义的查询。

所以你不再需要做所有的黑客或变通方法来回答你的问题。只需遵循以下步骤:

首先，您定义了一个DbQuery<T>类型的新属性，其中T是将携带SQL查询列值的类的类型。在你的DbContext中，你会有这个:

public DbQuery<SomeModel> SomeModels { get; set; }

其次，使用FromSql方法，就像使用DbSet<T>一样:

var result = context.SomeModels.FromSql("SQL_SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();

还要注意dbcontext是部分类，所以你可以创建一个或多个单独的文件来组织最适合你的“原始SQL DbQuery”定义。

我的案例使用了存储过程而不是原始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();

这个解决方案很大程度上依赖于@pius的解决方案。我想添加支持查询参数的选项，以帮助减少SQL注入，我还想使它成为实体框架核心的DbContext DatabaseFacade的扩展，使它更加集成。

首先用扩展名创建一个新类:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;

namespace EF.Extend
{

    public static class ExecuteSqlExt
    {
        /// <summary>
        /// Execute raw SQL query with query parameters
        /// </summary>
        /// <typeparam name="T">the return type</typeparam>
        /// <param name="db">the database context database, usually _context.Database</param>
        /// <param name="query">the query string</param>
        /// <param name="map">the map to map the result to the object of type T</param>
        /// <param name="queryParameters">the collection of query parameters, if any</param>
        /// <returns></returns>
        public static List<T> ExecuteSqlRawExt<T, P>(this DatabaseFacade db, string query, Func<DbDataReader, T> map, IEnumerable<P> queryParameters = null)
        {
            using (var command = db.GetDbConnection().CreateCommand())
            {
                if((queryParameters?.Any() ?? false))
                    command.Parameters.AddRange(queryParameters.ToArray());

                command.CommandText = query;
                command.CommandType = CommandType.Text;

                db.OpenConnection();

                using (var result = command.ExecuteReader())
                {
                    var entities = new List<T>();

                    while (result.Read())
                    {
                        entities.Add(map(result));
                    }

                    return entities;
                }
            }
                
        }
    }

}

注意上面的“T”是返回的类型，“P”是查询参数的类型，这取决于你是否使用MySql、Sql等。

接下来我们将展示一个例子。我使用的是MySql EF核心功能，所以我们将看到如何使用上面的通用扩展与这个更具体的MySql实现:

//add your using statement for the extension at the top of your Controller
//with all your other using statements
using EF.Extend;

//then your your Controller looks something like this
namespace Car.Api.Controllers
{

    //Define a quick Car class for the custom return type
    //you would want to put this in it's own class file probably
    public class Car
    {
        public string Make { get; set; }
        public string Model { get; set; }
        public string DisplayTitle { get; set; }
    }

    [ApiController]
    public class CarController : ControllerBase
    {
        private readonly ILogger<CarController> _logger;
        //this would be your Entity Framework Core context
        private readonly CarContext _context;

        public CarController(ILogger<CarController> logger, CarContext context)
        {
            _logger = logger;
            _context = context;
        }

        //... more stuff here ...

       /// <summary>
       /// Get car example
       /// </summary>
       [HttpGet]
       public IEnumerable<Car> Get()
       {
           //instantiate three query parameters to pass with the query
           //note the MySqlParameter type is because I'm using MySql
           MySqlParameter p1 = new MySqlParameter
           {
               ParameterName = "id1",
               Value = "25"
           };

           MySqlParameter p2 = new MySqlParameter
           {
               ParameterName = "id2",
               Value = "26"
           };

           MySqlParameter p3 = new MySqlParameter
           {
               ParameterName = "id3",
               Value = "27"
           };

           //add the 3 query parameters to an IEnumerable compatible list object
           List<MySqlParameter> queryParameters = new List<MySqlParameter>() { p1, p2, p3 };

           //note the extension is now easily accessed off the _context.Database object
           //also note for ExecuteSqlRawExt<Car, MySqlParameter>
           //Car is my return type "T"
           //MySqlParameter is the specific DbParameter type MySqlParameter type "P"
           List<Car> result = _context.Database.ExecuteSqlRawExt<Car, MySqlParameter>(
        "SELECT Car.Make, Car.Model, CONCAT_WS('', Car.Make, ' ', Car.Model) As DisplayTitle FROM Car WHERE Car.Id IN(@id1, @id2, @id3)",
        x => new Car { Make = (string)x[0], Model = (string)x[1], DisplayTitle = (string)x[2] }, 
        queryParameters);

           return result;
       }
    }
}

查询将返回如下行: “福特”，“探险家”，“福特探险家” “特斯拉”，“Model X”，“特斯拉Model X”

显示标题没有定义为数据库列，因此默认情况下它不是EF Car模型的一部分。作为众多可能的解决方案之一，我喜欢这种方法。本页上的其他答案引用了使用[NotMapped]装饰器解决此问题的其他方法，这取决于您的用例，可能是更合适的方法。

注意，本例中的代码显然比实际需要的更冗长，但我认为它使示例更清晰。

在其他答案的基础上，我写了这个助手来完成这个任务，包括示例用法:

public static class Helper
{
    public static List<T> RawSqlQuery<T>(string query, Func<DbDataReader, T> map)
    {
        using (var context = new DbContext())
        {
            using (var command = context.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;

                context.Database.OpenConnection();

                using (var result = command.ExecuteReader())
                {
                    var entities = new List<T>();

                    while (result.Read())
                    {
                        entities.Add(map(result));
                    }

                    return entities;
                }
            }
        }
    }

用法:

public class TopUser
{
    public string Name { get; set; }

    public int Count { get; set; }
}

var result = Helper.RawSqlQuery(
    "SELECT TOP 10 Name, COUNT(*) FROM Users U"
    + " INNER JOIN Signups S ON U.UserId = S.UserId"
    + " GROUP BY U.Name ORDER BY COUNT(*) DESC",
    x => new TopUser { Name = (string)x[0], Count = (int)x[1] });

result.ForEach(x => Console.WriteLine($"{x.Name,-25}{x.Count}"));

我计划在添加内置支持后尽快摆脱它。根据EF核心团队的Arthur Vickers的声明，这是2.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()

更多信息可以在这里找到

我之所以提出这个问题，是因为我们在实体框架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支持列重命名，但是，…上面已经做过了。