In EFCore 3.0 - how to join related read only Keyless entities from views so that related entities are loaded

Question

Using .NET EFCore 3.0 - Query Types are depreciated and now we move to "Keyless entity type" in Entity Framework Core 3.0 .

My requirement is to map a number of read only Views from a MS SQL database to the DbContext using the new HasNoKey() syntax.

1. The returned read only Entities must load their related read only Entities.
2. Is there a way to join Views to each other and automatically load related entities?
3. Perhaps there is another way to use Views and read only entities other than with HasNoKey?

Simple example schema, Order has many OrderItems. If both of these Entities come from a view, then how does an Order load it's OrderItems?

public class ReadonlyActionOnDb
{
    OrdersDbContext Db; //need to pass in via constructor etc, just for demo code.
    protected void PrintOrderItems()
    {
        var custItems = Db.vOrders.Where(i=> i.CustomerId == 10).SelectMany(i=> i.OrderItems);
        foreach (OrderItemDto i in custItems ) Console.WriteLine(i.ProductName); 
    }           
}

//part of the config shown...
public partial class OrdersDbContext: DbContext
{
    public DbSet<OrderDto> vOrders { get; set; }
    public DbSet<OrderDto> vOrderItems { get; set; }

    protected void OnModelCreating(ModelBuilder modelBuilder)
    {           
        modelBuilder.Entity<OrderItemDto>().HasNoKey().ToView("vOrderItems ","dbo");

        //how do we automatically load the OrderItems into this?
        modelBuilder.Entity<OrderDto>().HasNoKey().ToView("vOrders","dbo");
    }
}

public class OrderDto
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public ICollection<OrderItemDto> OrderItems { get; set; }
}

public class OrderItemDto
{
    public int Id { get; set; }
    public string ProductName { get; set; }
}

I feel this should be achieved on the DbContext instance, I know I could manually load and join later.

For this issue, migrations are not important because the DBAs enforce their own updates to the Database.

Below are the limitations of Keyless entity types as found in the MS Documentation. A little confusing for entity navigations without examples.

Keyless entity types characteristics

Keyless entity types support many of the same mapping capabilities as regular entity types, like inheritance mapping and navigation properties. On relational stores, they can configure the target database objects and columns via fluent API methods or data annotations.

However, they are different from regular entity types in that they:

Cannot have a key defined.

Are never tracked for changes in the DbContext and therefore are never inserted, updated or deleted on the database.

Are never discovered by convention.

Only support a subset of navigation mapping capabilities, specifically:

They may never act as the principal end of a relationship.

They may not have navigations to owned entities

They can only contain reference navigation properties pointing to regular entities.

Entities cannot contain navigation properties to keyless entity types.

Need to be configured with .HasNoKey() method call.

May be mapped to a defining query. A defining query is a query declared in the model that acts as a data source for a keyless entity type.

Answer 1

You can't query the orders and then include the order items. But you can query all the order items, where the parent order matches your criteria, then include the parent record.

There's a design pattern I've recently discovered, where your "keyless entity" only contains the primary keys of other database entities. With defined navigation properties to each of them.

public class OrderDetailKeys{
    public int OrderId { get; set; }
    public int OrderDetailId { get; set; }
    public virtual Order Order { get; set; }
    public virtual OrderDetail OrderDetail { get; set; }
}

modelBuilder.Entity<OrderDetailKeys>(entity => {
    entity.HasNoKey();
    entity.ToView(null); // or real view name
    entity.HasOne(e => e.Order)
        .WithOne()
        .HasForeignKey<OrderDetailKeys>(e => e.OrderId);
    entity.HasOne(e => e.OrderDetail)
        .WithOne()
        .HasForeignKey<OrderDetailKeys>(e => e.OrderDetailId);
}

This way you can use a database view, or FromRawSql to define which records are available in a reusable result set.

    public static IQuerable<OrderDetailKeys> ComplexQuery(DbContext context) => 
        context.Set<OrderDetailKeys>()
            .FromRawSql("select OrderId, Id as OrderDetailId from OrderDetail where <complex sql condition here>");

Every place where this result set is used can restrict the results further with a Where condition. Then Include or Select only the columns they need.

    var details = ComplexQuery(context)
        .Where(k => k.Order.CustomerId = ...)
        .Include(k => k.OrderDetail)
        .ToListAsync();

EF will use your view or raw sql as a subquery in the from clause. Then build around that join to the other tables.

The ugly bit of SQL that you need, that you can't convince EF to generate, is encapsulated in one location. And you don't end up repeating the column schema of your real tables for every view.