c# - 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所以想使用 View 来填充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

标签： c#entity-frameworkef-core-2.2

Answer 1

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

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