is it possible to run a raw sql using ef core 5 with inner join and materialize the data to a class?

Question

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

// code first entity class

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

// code first entity class

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

// i want to do something like this

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

I would normally use views, to be able to handle all RawSql queries through migrations.

To do this you can do the following after creating the main entities and the migrations.

Create a new Class for the result model:

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

Create an empty migration by running the add-migration command like:

add-migration JoinedResultsView

Then update the migration like this:

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

The final step would be to map the view to the entity using a Configuration class:

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

and then adding the configuration to the 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());
    }
}

That's it.

Not only you can get the query result in your code like this:

var queryResult = cntx.JoinedResults.ToList();

But you can also add linq queries on top of that:

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

This is also a very useful way to factor out the common part of your big and slow queries and rewrite them as Sql Views and then add linq queries on top of them for the non-common parts.

Have fun ;)