Entity Framework投影中子查询复用的性能优化问题

问题描述 #

使用Entity Framework做投影查询时，因重复调用子查询导致性能问题。当前代码在获取用户最后一笔订单的ID和价格时，触发了两次独立的子查询，希望复用子查询结果来提升性能。

当前查询代码：

public List<UserProjection> FindUserAndLastOrder(List<Guid> idsUser)
{
    return _entity.Where(x => idsUser.Contains(x.Id)).Select(x => new UserProjection()
    {
        Email = x.Email,
        LastOrderId = x.Orders.OrderByDescending(order => order.CreatedAt).First().Id,
        Price = x.Orders.OrderByDescending(order=> order.CreatedAt).First().Price
    }).ToList();
}

实体类定义：

public class User : BaseEntity
{
    public string Email { get; }
    public string Password { get; private set; }
    public byte[] Key { get; private set; }
    public List<Order> Orders { get; set;}
}

public class Order : BaseEntity
{
    public double Price { get; set; }
    public Guid IdUser { get; set; }
}

public class UserProjection
{
    public string Email { get; set; }
    public Guid LastOrderId { get; set; }
    public double Price { get; set; }
}

解决方案 #

方法1：通过中间投影复用子查询结果 #

先将用户和其最后一笔订单投影到匿名对象，再从该对象映射到UserProjection，EF会自动优化生成单次子查询：

public List<UserProjection> FindUserAndLastOrder(List<Guid> idsUser)
{
    return _entity.Where(x => idsUser.Contains(x.Id))
                  .Select(x => new 
                  {
                      User = x,
                      LastOrder = x.Orders.OrderByDescending(order => order.CreatedAt).FirstOrDefault()
                  })
                  .Select(temp => new UserProjection()
                  {
                      Email = temp.User.Email,
                      LastOrderId = temp.LastOrder.Id,
                      Price = temp.LastOrder.Price
                  })
                  .ToList();
}

方法2：使用查询表达式的let子句 #

用LINQ查询表达式的let关键字定义临时变量，直接复用子查询结果，写法更直观：

public List<UserProjection> FindUserAndLastOrder(List<Guid> idsUser)
{
    var query = from user in _entity
                where idsUser.Contains(user.Id)
                let lastOrder = user.Orders.OrderByDescending(o => o.CreatedAt).FirstOrDefault()
                select new UserProjection
                {
                    Email = user.Email,
                    LastOrderId = lastOrder.Id,
                    Price = lastOrder.Price
                };

    return query.ToList();
}

额外优化建议 #

• 若用户可能无订单，建议用FirstOrDefault()替代First()，并处理LastOrder为null的场景（比如给默认值），避免空引用异常。
• 给Order表的CreatedAt字段添加索引，能进一步提升子查询排序的性能。

内容的提问来源于stack exchange，提问作者Ivo Paulo Puehler