EF Core - 从 SQL 视图加载的导航属性

Question

我有以下实体：

public class Animal
{
public int Id { get; set; }
public string Name { get; set; }
/// <summary>
/// Current Animal Status
/// </summary>
public virtual AnimalStatus CurrentAnimalStatus
{
get { return AnimalStatuses.OrderByDescending(x => x.VersionNo).First(); }
}
/// <summary>
/// List of all Animal Statuses
/// </summary>
public virtual ICollection<AnimalStatus> AnimalStatuses { get; set; }
/// <summary>
/// List of all Animal Operations
/// </summary>
public virtual ICollection<AnimalOperation> AnimalOperations { get; set; }
}

和

public class AnimalStatus
{
public int Id { get; set; }
public int VersionNo { get; set; }
public int BloodCount { get; set; }
public int AnimalId { get; set; }
public virtual Animal Animal { get; set; }
}

我只对最近的AnimalStatus感兴趣，所以想使用视图来填充CurrentAnimalStatus属性并消除对ICollection<AnimalStatus>的需求。Animal实体的其余部分应由 EF 核心填充。

目前，AnimalStatus表包含 300,000 条记录，因此使用 EF Core 非常慢

_dbContext.Animal.Include(x => x.AnimalStatus);

是否可以在 EF Core 中从 SQL 视图填充实体上的导航属性？

CREATE VIEW [dbo].[CurrentAnimalStatusView]
AS
WITH ANIMAL_EVENTS_CTE AS
(
SELECT
AnimalId,
BloodCount,
ROW_NUMBER() OVER (PARTITION BY AnimalId ORDER BY VersionNo DESC) AS RowNumber --RowNumber = 1 indicates the latest status
FROM
AnimalBloodTests
)
SELECT
AnimalId,
BloodCount
FROM
ANIMAL_EVENTS_CTE cte
WHERE
RowNumber = 1 --Only interested in latest status for each AnimalId

Answer 1

您可以为视图创建模型并将其映射到 Dbset。

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<CurrentAnimalStatus>(eb =>
{
eb.HasNoKey();
eb.ToView("CurrentAnimalStatusView");
});
base.OnModelCreating(modelBuilder);
}