在实体框架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>();

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

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

注意:该方案性能较慢

我使用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();
        }


        }
}

现在，直到EFCore有新的东西，我将使用命令 手动映射

  using (var command = this.DbContext.Database.GetDbConnection().CreateCommand())
  {
      command.CommandText = "SELECT ... WHERE ...> @p1)";
      command.CommandType = CommandType.Text;
      var parameter = new SqlParameter("@p1",...);
      command.Parameters.Add(parameter);

      this.DbContext.Database.OpenConnection();

      using (var result = command.ExecuteReader())
      {
         while (result.Read())
         {
            .... // Map to your entity
         }
      }
  }

尝试使用SqlParameter避免Sql注入。

 dbData.Product.FromSql("SQL SCRIPT");

FromSql不支持全查询。例如，如果你想包含一个WHERE子句，它将被忽略。

一些链接:

使用实体框架核心执行原始SQL查询

原始SQL查询

不是直接针对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();
    }
  }
}

我更新了扩展方法从@AminRostami返回IAsyncEnumerable(这样LINQ过滤可以应用)，它的映射模型列名的记录从DB返回到模型(EF Core 5测试):

扩展本身:

public static class QueryHelper
{
    private static string GetColumnName(this MemberInfo info)
    {
        List<ColumnAttribute> list = info.GetCustomAttributes<ColumnAttribute>().ToList();
        return list.Count > 0 ? list.Single().Name : info.Name;
    }
    /// <summary>
    /// 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 model (if not present - null)
    /// </summary>
    public static async IAsyncEnumerable<T> ExecuteQuery<T>(
        [NotNull] this DbContext db,
        [NotNull] string query,
        [NotNull] params SqlParameter[] parameters)
        where T : class, new()
    {
        await using DbCommand command = db.Database.GetDbConnection().CreateCommand();
        command.CommandText = query;
        command.CommandType = CommandType.Text;
        if (parameters != null)
        {
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
        }
        await db.Database.OpenConnectionAsync();
        await using DbDataReader reader = await command.ExecuteReaderAsync();
        List<PropertyInfo> lstColumns = new T().GetType()
            .GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic).ToList();
        while (await reader.ReadAsync())
        {
            T newObject = new();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                string name = reader.GetName(i);
                PropertyInfo prop = lstColumns.FirstOrDefault(a => a.GetColumnName().Equals(name));
                if (prop == null)
                {
                    continue;
                }
                object val = await reader.IsDBNullAsync(i) ? null : reader[i];
                prop.SetValue(newObject, val, null);
            }
            yield return newObject;
        }
    }
}

使用的模型(注意列名与实际的属性名不同):

public class School
{
    [Key] [Column("SCHOOL_ID")] public int SchoolId { get; set; }

    [Column("CLOSE_DATE", TypeName = "datetime")]
    public DateTime? CloseDate { get; set; }

    [Column("SCHOOL_ACTIVE")] public bool? SchoolActive { get; set; }
}

实际的用法:

public async Task<School> ActivateSchool(int schoolId)
{
    // note that we're intentionally not returning "SCHOOL_ACTIVE" with select statement
    // this might be because of certain IF condition where we return some other data
    return await _context.ExecuteQuery<School>(
        "UPDATE SCHOOL SET SCHOOL_ACTIVE = 1 WHERE SCHOOL_ID = @SchoolId; SELECT SCHOOL_ID, CLOSE_DATE FROM SCHOOL",
        new SqlParameter("@SchoolId", schoolId)
    ).SingleAsync();
}