Create one entity for multiple tables?

Question

I have a situation where I have several tables in my SQL Server database that I would like to query using Entity Framework Core. Rather than have an individual entity for each table, I would like to have one entity that contains properties for all of the columns across all of the tables.

When I try to do this, I end up with a

System.InvalidOperationException: 'The required column was not present in the results of a 'FromSql' operation.'

Here's essentially what my tables look like:

Table #1 Columns: Column1, Column2, Column3  
Table #2 Columns: Column3, Column4, Column5  
Table #3 Columns: Column3, Column4, Column6

This is essentially what my Entity looks like:

public class UniversalEntity
{
    public string? Column1 { get; set; }
    public string? Column2 { get; set; }
    public string? Column3 { get; set; }
    public string? Column4 { get; set; }
    public string? Column5 { get; set; }
    public string? Column6 { get; set; }
}

This is what my DbContext looks like:

public class MyContext : DbContext
{
    public DbSet<UniversalEntity>? Universals {get; set;}

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer([My Database Connection String]);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var entityTypeBuilder = modelBuilder.Entity<UniversalEntity>().HasNoKey();

        entityTypeBuilder.Property(p => p.Column1).IsRequired(false);
        entityTypeBuilder.Property(p => p.Column2).IsRequired(false);
        entityTypeBuilder.Property(p => p.Column3).IsRequired(false);
        entityTypeBuilder.Property(p => p.Column4).IsRequired(false);
        entityTypeBuilder.Property(p => p.Column5).IsRequired(false);
        entityTypeBuilder.Property(p => p.Column6).IsRequired(false);
    }
}

And this is how I'm querying the tables:

MyContext context = new MyContext();
List<UniversalEntity>? results = context.Universals.FromSqlRaw("RAW SQL QUERY").ToList();

If I query table #2 for example, then the error I get looks like this:

System.InvalidOperationException: 'The required column 'Column1' was not present in the results of a 'FromSql' operation.'

I thought that using

entityTypeBuilder.Property(p => p.ColumnX).IsRequired(false);

in the OnModelCreating method would have stopped the columns from being required, but that does not appear to work.

Is it possible to create an entity that has properties that aren't required to be filled by a call to FromSqlRaw? If so, how?

I'm currently using EF Core 7, but I'm open to switching versions if necessary.

All of my queries are strictly read-only as well. No queries will need to write to the tables.

Answer 1

You can create a database view that consolidates the columns from multiple tables and then query this view using Entity Framework.

Create a Database View:

CREATE VIEW YourEntity AS
SELECT Column1, Column2, Column3, Column4, Column5, Column6
 FROM Table1
   UNION ALL
SELECT NULL AS Column1, NULL AS Column2, Column3, Column4, Column5, NULL AS Column6
 FROM Table2
   UNION ALL
SELECT NULL AS Column1, NULL AS Column2, Column3, Column4, NULL AS Column5, Column6
 FROM Table3;

Map View to Entity:

public class YourEntity
{
    public string? Column1 { get; set; }
    public string? Column2 { get; set; }
    public string? Column3 { get; set; }
    public string? Column4 { get; set; }
    public string? Column5 { get; set; }
    public string? Column6 { get; set; }
}

Query the View:

MyContext context = new MyContext();
List<YourEntity>? results = context.Your.FromSqlRaw("SELECT * FROM YourEntity").ToList();