c# - 是否可以使用带有内部连接的 ef core 5 运行原始 sql 并将数据具体化为一个类？

Question

public class UserDto
{
    public int Id {get; set;}
    public string Name {get; set;}
    public string Email {get; set;}
    public string Username {get; set;}
}

//代码优先实体类

public class User
{
    public int Id {get; set;}
    public string Username {get; set;}
}

//代码优先实体类

public class Profile
{
    public string Name {get; set;}
    public string Email {get; set;}
}

//我想做这样的事情

List<UserDto> userDto = context.Database
.FromSqlRaw<List<UserDto>>("SELECT u.Id, u.Username, p.Name, p.Email FROM dbo.User u
    INNER JOIN dbo.Profile p on p.UserId = u.id 
    ").ToList();

Answer 1

我通常会使用 View ，以便能够通过迁移处理所有 RawSql 查询。

为此，您可以在创建主要实体和迁移后执行以下操作。

为结果模型创建一个新类:

public class JoinedResult
{
    public int UserId { get; set; }
    public string Username { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

通过运行 add-migration 命令来创建空迁移，例如:

add-migration JoinedResultsView

然后像这样更新迁移:

public partial class JoinedResultsView : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"CREATE VIEW JoinedResults
        AS
            SELECT        u.UserId, u.Username, p.Name, p.Email
            FROM            dbo.Users AS u INNER JOIN
        dbo.Profiles AS p ON p.UserId = u.UserId");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP VIEW JoinedResults");
    }
}

最后一步是使用配置类将 View 映射到实体:

public class JoinedResultConfiguration :IEntityTypeConfiguration<JoinedResult>
{
    public void Configure(EntityTypeBuilder<JoinedResult> builder)
    {
        builder.ToView("JoinedResults");
        builder.HasNoKey();
        builder.Property(p => p.Name).HasColumnName("Name");
        builder.Property(p => p.Email).HasColumnName("Email");
        builder.Property(p => p.UserId).HasColumnName("UserId");
        builder.Property(p => p.Username).HasColumnName("Username");
    }
}

然后将配置添加到 DbContext:

public class MyDbContext : DbContext
{
    public DbSet<Profile> Profiles { get; set; }
    public DbSet<User> Users { get; set; }
    public DbSet<JoinedResult> JoinedResults { get; set; }


    protected override void OnConfiguring(DbContextOptionsBuilder options) => options.UseSqlServer($"data source=.;initial catalog=TheDb;Integrated Security=True");
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new JoinedResultConfiguration());
    }
}

就是这样。

不仅你可以在你的代码中得到这样的查询结果:

var queryResult = cntx.JoinedResults.ToList();

但您也可以在此基础上添加 linq 查询:

var x = cntx.JoinedResults.OrderBy(x => x.Name).FirstOrDefault(x => x.Name.Contains("MA"));

这也是一种非常有用的方法，可以提取出大查询和慢查询的公共(public)部分并将它们重写为 Sql View ，然后在它们之上为非公共(public)部分添加 linq 查询。

玩得开心;)