Projection with subquery for Property in EntityFramework

Ivo Paulo Puehler 0
Viewed 29

I have a performance problem in projections of Entity Framework.

My find has a projection with a list of sub-property:

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();
}

Then run the query and it executes two subqueries: one for getting order id and the other for getting price

Has a solution for saving subquery in variable and reusing value in two points?

entities examples:

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; }
}
performance entity-framework c#
1 Answers

One simple solution is to use a double projection, one to get the relevant data, and the second in memory to compose your desired model:

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

You might need to materialize the first step /w a ToList() after the first Select if EF complains, but that wouldn't really have much impact on performance, just a bit of memory use for the double-projection. (depending on how many users you are retrieving)

Note: This assumes that every user has at least one order which may be a problem if there are users without orders. In which case you'd likely want to use a FirstOrDefault then handle that the "LastOrder" detail might be #null and ensure LastOrderId and Price are nullable.

Steve Py0
Related