EF Core中从历史数据关联当前车辆所有者的实现方案问询

在我的应用中，存在Car（车辆）、User（用户）以及记录车辆所有权变更的关联表CarOwner。90%以上的常见场景是获取车辆的当前所有者，我希望实现方式尽可能简洁。现有实体类定义如下：

public class Car
{
    public int Id {get; set;}
    
    public virtual ICollection<CarOwner> CarOwnerHistory {get; set;}
    public virtual User CurrentOwner {get; set;}
}

public class User
{
    public int Id {get; set;}
    
    public virtual ICollection<CarOwner> CarOwnerHistory {get; set;}
    public virtual ICollection<Car> CurrentCars {get; set;}
}

public class CarOwner
{
    public int Id {get; set;}
    public int CarId {get; set;}
    public int UserId {get; set;}
    public DateTime OwnershipStartedAt {get; set;}

    public virtual Car Car {get; set;}
    public virtual User User {get; set;}
}

我计划将CurrentOwner和CurrentCars设为未映射（Not-Mapped）属性，通过扩展方法实现关联，期望调用方式如下：

var user = dbContext.Set<User>().IncludeCurrentCars().First(x => x.Id == [my user id]);

关联逻辑为：选取CarOwner表中OwnershipStartedAt（所有权变更时间）最大的记录，以此确定当前所有者/车辆。

另外，我也尝试过通过扩展方法过滤CarOwnerHistory，示例如下：

var user = dbContext.Set<User>()
    .Include(x => x.CarOwnerHistory
        .Where(y => y.OwnershipStartedAt == x.CarOwnerHistory.Select(z => z.OwnershipStartedAt).Max())
    )
    .ThenInclude(x => x.Car)
    .First(x => x.Id == [my user id]);

现提出两个问题：

1. 如何创建扩展方法以关联CurrentOwner和CurrentCars？
2. 是否有其他实现方式，例如在DbContext中声明自定义规则来关联未映射属性？

1. 创建扩展方法关联未映射属性 #

首先，给CurrentOwner和CurrentCars添加[NotMapped]特性，告知EF Core这些属性不需要映射到数据库字段：

using System.ComponentModel.DataAnnotations.Schema;

public class Car
{
    public int Id { get; set; }
    
    public virtual ICollection<CarOwner> CarOwnerHistory { get; set; }
    
    [NotMapped]
    public virtual User CurrentOwner { get; set; }
}

public class User
{
    public int Id { get; set; }
    
    public virtual ICollection<CarOwner> CarOwnerHistory { get; set; }
    
    [NotMapped]
    public virtual ICollection<Car> CurrentCars { get; set; }
}

接下来编写针对IQueryable<User>和IQueryable<Car>的扩展方法，通过EF Core的查询投影实现关联逻辑：

using Microsoft.EntityFrameworkCore;

public static class EntityQueryExtensions
{
    // 扩展User查询，加载当前拥有的车辆
    public static IQueryable<User> IncludeCurrentCars(this IQueryable<User> query)
    {
        return query.Select(user => new User
        {
            Id = user.Id,
            // 复制需要保留的其他实体属性
            CarOwnerHistory = user.CarOwnerHistory,
            CurrentCars = user.CarOwnerHistory
                // 按车辆分组，取每组中最新的所有权记录
                .GroupBy(co => co.CarId)
                .Select(group => group.OrderByDescending(co => co.OwnershipStartedAt).FirstOrDefault())
                .Select(co => co.Car)
                .ToList()
        });
    }

    // 扩展Car查询，加载当前所有者
    public static IQueryable<Car> IncludeCurrentOwner(this IQueryable<Car> query)
    {
        return query.Select(car => new Car
        {
            Id = car.Id,
            // 复制需要保留的其他实体属性
            CarOwnerHistory = car.CarOwnerHistory,
            CurrentOwner = car.CarOwnerHistory
                .OrderByDescending(co => co.OwnershipStartedAt)
                .FirstOrDefault()?
                .User
        });
    }
}

调用方式 #

// 获取带当前车辆的用户
var user = dbContext.Set<User>().IncludeCurrentCars().First(x => x.Id == userId);

// 获取带当前所有者的车辆
var car = dbContext.Set<Car>().IncludeCurrentOwner().First(x => x.Id == carId);

如果实体有较多属性，可改用Join结合子查询的方式避免逐个复制属性：

public static IQueryable<Car> IncludeCurrentOwner(this IQueryable<Car> query)
{
    // 先获取每辆车的最新所有权记录
    var latestOwners = query
        .SelectMany(car => car.CarOwnerHistory)
        .GroupBy(co => co.CarId)
        .Select(g => new { CarId = g.Key, LatestDate = g.Max(co => co.OwnershipStartedAt) })
        .Join(query.SelectMany(car => car.CarOwnerHistory),
            lo => new { lo.CarId, lo.LatestDate },
            co => new { co.CarId, co.OwnershipStartedAt },
            (lo, co) => co);

    return query
        .Include(car => car.CarOwnerHistory)
        .Select(car => new Car
        {
            Id = car.Id,
            CarOwnerHistory = car.CarOwnerHistory,
            CurrentOwner = latestOwners.FirstOrDefault(co => co.CarId == car.Id)?.User
        });
}

2. 其他实现方式 #

除了扩展方法，还有几种更高效的方案：

方案一：添加冗余字段优化查询 #

既然90%的场景是获取当前所有者，可以在Car表中添加CurrentOwnerId外键字段，每次新增CarOwner记录时同步更新对应Car的CurrentOwnerId为新用户ID。这样CurrentOwner可直接作为EF Core的导航属性，无需查询历史表：

public class Car
{
    public int Id { get; set; }
    
    public int? CurrentOwnerId { get; set; }
    public virtual User CurrentOwner { get; set; }
    
    public virtual ICollection<CarOwner> CarOwnerHistory { get; set; }
}

优点是查询性能极高；缺点是需要维护冗余数据，可通过数据库触发器、EF Core的SaveChanges拦截器实现自动同步。

方案二：延迟加载未映射属性 #

给未映射属性添加延迟加载逻辑，第一次访问时从已加载的CarOwnerHistory中筛选最新记录：

public class Car
{
    public int Id { get; set; }
    
    public virtual ICollection<CarOwner> CarOwnerHistory { get; set; }
    
    [NotMapped]
    private User _currentOwner;
    [NotMapped]
    public User CurrentOwner
    {
        get
        {
            if (_currentOwner == null && CarOwnerHistory != null)
            {
                _currentOwner = CarOwnerHistory
                    .OrderByDescending(co => co.OwnershipStartedAt)
                    .FirstOrDefault()?.User;
            }
            return _currentOwner;
        }
        set => _currentOwner = value;
    }
}

注意：需确保CarOwnerHistory已通过Include加载，否则会返回null；数据量大时性能较差。

方案三：DbContext中封装自定义查询 #

在DbContext中编写封装方法，直接返回带当前所有者/车辆的实体：

public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Car> Cars { get; set; }
    public DbSet<CarOwner> CarOwners { get; set; }

    public User GetUserWithCurrentCars(int userId)
    {
        var user = Users.Find(userId);
        if (user == null) return null;

        user.CurrentCars = CarOwners
            .Where(co => co.UserId == userId)
            .GroupBy(co => co.CarId)
            .Select(g => g.OrderByDescending(co => co.OwnershipStartedAt).First().Car)
            .ToList();

        return user;
    }

    public Car GetCarWithCurrentOwner(int carId)
    {
        var car = Cars.Find(carId);
        if (car == null) return null;

        car.CurrentOwner = CarOwners
            .Where(co => co.CarId == carId)
            .OrderByDescending(co => co.OwnershipStartedAt)
            .FirstOrDefault()?.User;

        return car;
    }
}

调用时直接使用封装方法：

var user = dbContext.GetUserWithCurrentCars(userId);

内容的提问来源于stack exchange，提问作者wekso