查询数据:不存在实体

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

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

现在，直到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();
    }
  }
}

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