ef-core-3.0 - 在 EF Core 3.0 中 - 如何从 View 中加入相关的只读无 key 实体，以便加载相关实体

Question

使用 .NET EFCore 3.0 - 查询类型已折旧，现在我们移至 "Keyless entity type"在 Entity Framework Core 3.0 中。

我的要求是使用新的 HasNoKey() 将多个只读 View 从 MS SQL 数据库映射到 DbContext。句法。

返回的只读实体必须加载其相关读取
只有实体。

有没有办法将 View 相互连接并自动加载相关
实体？

也许还有另一种使用 View 和只读实体的方法
除了 HasNoKey？

简单的示例架构，Order 有许多 OrderItem。如果这两个实体都来自一个 View ，那么订单如何加载它的订单项？

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

我觉得这应该在 DbContext 实例上实现，我知道我可以稍后手动加载和加入。

对于这个问题，迁移并不重要，因为 DBA 会强制执行他们自己对数据库的更新。

以下是在 MS Documentation 中找到的无 key 实体类型的限制。 .没有示例的实体导航有点困惑。

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

您不能查询订单然后包含订单项目。但是您可以查询所有订单项目，其中父订单符合您的条件，然后包括父记录。

我最近发现了一种设计模式，其中您的“无键实体”仅包含其他数据库实体的主键。为它们中的每一个定义导航属性。

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

这样你就可以使用数据库 View ，或者 FromRawSql定义可重用结果集中哪些记录可用。

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

每个使用此结果集的地方都可以使用 Where 进一步限制结果。健康)状况。然后 Include或 Select只有他们需要的列。

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

EF 将使用您的 View 或原始 sql 作为 from 子句中的子查询。然后围绕该连接构建到其他表。

您无法说服 EF 生成的丑陋 SQL 部分被封装在一个位置。而且您最终不会为每个 View 重复真实表的列模式。