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

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

注意:该方案性能较慢

你可以用这个:

public static class SqlQueryExtensions
{
    public static IList<T> SqlQuery<T>(this DbContext db, string sql, params object[] parameters) where T : class
    {
        using (var db2 = new ContextForQueryType<T>(db.Database.GetDbConnection()))
        {
            // share the current database transaction, if one exists
            var transaction = db.Database.CurrentTransaction;
            if (transaction != null)
                db2.Database.UseTransaction(transaction.GetDbTransaction());
            return db2.Set<T>().FromSqlRaw(sql, parameters).ToList();
        }
    }

    private class ContextForQueryType<T> : DbContext where T : class
    {
        private readonly DbConnection connection;

        public ContextForQueryType(DbConnection connection)
        {
            this.connection = connection;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(connection, options => options.EnableRetryOnFailure());

            base.OnConfiguring(optionsBuilder);
        }

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

以及用法:

    using (var db = new Db())
    {
        var results = db.SqlQuery<ArbitraryType>("select 1 id, 'joe' name");
        //or with an anonymous type like this
        var results2 = db.SqlQuery(() => new { id =1, name=""},"select 1 id, 'joe' name");
    }

我使用Dapper绕过了Entity框架Core的这个约束。

IDbConnection.Query

正在处理带有多个参数的SQL查询或存储过程。 顺便说一下，它更快一些(参见基准测试)

Dapper很容易学。编写和运行带参数的存储过程需要15分钟。无论如何，你可以同时使用EF和Dapper。下面是一个例子:

 public class PodborsByParametersService
{
    string _connectionString = null;


    public PodborsByParametersService(string connStr)
    {
        this._connectionString = connStr;

    }

    public IList<TyreSearchResult> GetTyres(TyresPodborView pb,bool isPartner,string partnerId ,int pointId)
    {

        string sqltext  "spGetTyresPartnerToClient";

        var p = new DynamicParameters();
        p.Add("@PartnerID", partnerId);
        p.Add("@PartnerPointID", pointId);

        using (IDbConnection db = new SqlConnection(_connectionString))
        {
            return db.Query<TyreSearchResult>(sqltext, p,null,true,null,CommandType.StoredProcedure).ToList();
        }


        }
}

如果您使用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”定义。

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

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

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后的优先级。这个问题正在被追踪。