I have the following entities:
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; }
}
and
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; }
}
I'm only ever interested in the most recent AnimalStatus so would like to use a View to populate just the CurrentAnimalStatus property and remove the need for ICollection<AnimalStatus>. The rest of the Animal entity should be populated by EF core.
Currently the AnimalStatus table holds 300,000 records so is very slow using EF Core with
_dbContext.Animal.Include(x => x.AnimalStatus);
Is it possible in EF Core to have a navigation property on an entity be populated from a SQL view ?
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