在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过程。

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

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 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]);
    }
}

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

试试这个(创建扩展方法)

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中测试。

注意:该方案性能较慢

这个解决方案很大程度上依赖于@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]装饰器解决此问题的其他方法，这取决于您的用例，可能是更合适的方法。

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