DbContext filter based on multiple values

[raysefo]

I have orders and order details. I want to check all the order details in order to find out if the status of all the order details is completed. There are a couple of statuses; canceled, completed, continues. How to check the statuses in the sample below in order to know if all order details are completed?

await this._db.OrderDetails.Where(x => x.OrderId) == id);

public class Order
    {
        public int Id { get; set; }
        
        [Required]
        public DateTime OrderDateTime { get; set; }
        [Required]
        [MaxLength(250)]
        public string CustomerName { get; set; }
        public string Status { get; set; }
        [MaxLength(50)]
        public string DoneBy { get; set; }
        public List<OrderDetail> OrderDetails { get; set; }


    }
public class OrderDetail
    {
        public int Id { get; set; }
        
        [Required]
        [MaxLength(100)]
        public string ProductCode { get; set; }
        [Required]
        [MaxLength(250)]
        public string ProductName { get; set; }
        [Required]
        public int BuyQuantity { get; set; }
        [Required]
        public int SellQuantity { get; set; }
        public double CostRatio { get; set; }
        public double UnitCost { get; set; }
        public double TotalBuyPrice { get; set; }
        public double TotalSellPrice { get; set; }
        [MaxLength(150)]
        public string ShippingNumber { get; set; }
        public string Status { get; set; }
        [MaxLength(150)]
        public string TrackingNumber { get; set; }
        [MaxLength(400)]
        public string Description { get; set; }
        public string Currency { get; set; }
        public int OrderId { get; set; }
        public int VendorId { get; set; }
        public Order Order { get; set; }
        public Vendor Vendor { get; set; }

    }

 

[jmcilhinney]

var areAllCompleted = this._db.OrderDetails.Where(od => od.OrderId == orderId)
                                           .All(od => od.Status == "completed");

You could also do this:

var areAllCompleted = this._db.OrderDetails.Where(od => od.OrderId == orderId && od.Status != "completed")
                                           .Any();

Note that both those are valid LINQ syntax but I can't guarantee that the LINQ to Entities provider will be able to convert them to SQL. If not, you can throw in a ToList or ToArray after the Where.

 

[raysefo]

@jmcilhinney thank you. My goal is to make the status completed of the order if all the order details are completed. So your second linq is more suitable I think.

 

[jmcilhinney]

That seems to suggest that you should be getting the Order itself rather than just the OrderDetails. You could do this:

var order = _db.Orders.Include(o => o.OrderDetails)
                      .Where(o => o.OrderId == orderId)
                      .First();

if (order.OrderDetails.All(od => od.Status == "completed"))
{
    order.Status = "completed";
}

or you could do this:

var order = _db.Orders.Where(o => o.OrderId == orderId &&
                                  o.OrderDetails.All(od => od.Status == "completed"))
                      .FirstOrDefault();

if (order != null)
{
    order.Status == "completed";
}

The former pulls the OrderDetails back from the database too and then uses LINQ to Objects to do the All, while the latter only pulls back the Order - not even that if it's not going to be updated - and uses LINQ to Entities to do the All.

 

[cjard]

Note, you can just use First(lambda) as an equivalence to Where(lambda).First(). I'd go for jmc's latter:

await _db.Orders.FirstOrDefaultAsync(o => o.OrderId == orderId && o.OrderDetails.All(od => od.Status == "completed"))

If you get default then that orderid either doesn't exist or has at least one non-completed status. If you want the OrderDetails downloading into the returned Order, Include them

Oh, and do strive to await on blazor server..