EF Core query where sum of relationship is less than property

Question

I'm having to classes, one product class and one which tracks the orders. Both are stored in a database and I'm using ef core as the orm.

public class Product
{
    public int Id { get; set; }
    public int AvailableQuantity { get; set; }
    public ICollection<Order> Orders { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public bool Confirmed { get; set; }
    public int Quantity { get; set; }
    public int ProductId { get; set; }
    public Product Product { get; set; }
}

Now I need to get all Products where the sum of the Order.Quantity is less than the Product.AvailableQuantity and the Order.Confirmed property is true.

I already tried

_context.Products.Where(product => product.Orders.Where(order => order.Confirmed).Sum(order => order.Quantity) < product.AvailableQuantity)

but that obviously didn't work. I'm guessing I need something with GroupBy, but I can't get my head around how to get this query to work. Also I don't want to use AsEnumerable and execute the query in memory, because my database is huge.

Answer 1

You wrote:

Get all Products where the sum of the Order.Quantity is less than the Product.AvailableQuantity and the Order.Confirmed property is true.

I think you meant to say the following:

Requirement: Get all Products, with their confirmed orders, where the sum of the Quantity of these confirmed orders is less than the AvailableQuantity of the Product.

If might be that you also want the non-confirmed orders in your end result. This won't change a lot.

var result = dbContext.Products.Where(product =>

    // keep only the Products where the sum of the confirmed orders is less than ...
    product.Orders.Where(order => order.Confirmed)
                  .Select(order => order.Quantity)
                  .Sum() < product.AvailableQuantity);

This will give you the products with all their Orders: the confirmed ones and the non-confirmed ones.

If you only want the confirmed ones, consider using a Select first:

var productsThatCanBeDelivered = dbContext.Products.Select(product => new
{
    // Select only the product properties that you plan to use:
    Id = product.Id,
    AvailableQuantity = product.AvailableQuantity,

    ConfirmedOrders = product.Orders
        .Where(order => order.Confirmed)
        .Select(order => new
        {
            // select only the order properties that you plan to use
            Id = order.Id,
            Quantity = order.Quantity,
            ...

            // no need for this: you already know the value
            // ProductId = order.ProductId,
            // Confirmed = order.Confirmed,
        })
        .ToList(),
    })

    // keep only those Products where the quantities of the confirmed orders is less than ...
    .Where(product => product.ConfirmedOrders
                             .Select(confiredOrder => confirmedOrder.Quantity)
                             .Sum() < product.AvailableQuantity);

One final remark: are you sure you mean less than AvailableQuantity, not less or equal: if there is 1 Available product, and you only need to deliver one, why not deliver it?