Perform a single query based on comparison of sum of child collections

c# entity-framework-core sql-server

Question

I have the following classes

    public class Order
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public DateTime Date { get; set; }
        public decimal TotalPaid { get { return Payments.Sum(x => x.Amount); } }
        public decimal Total { get { return OrderItems.Sum(x => x.TotalPrice); } }
        public bool PaidCompletely { get { return Total == TotalPaid; } }
        public List<OrderItem> OrderItems { get; set; } = new List<OrderItem>();
        public List<Payment> Payments { get; set; } = new List<Payment>();
    }

    public class OrderItem
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public decimal PricePerUnit { get; set; }
        public decimal Quantity { get; set; }
        public decimal TotalPrice { get { return PricePerUnit * Quantity; } }
    }

    public class Payment
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public decimal Amount { get; set; }
        public DateTime Date { get; set; }
    }

I want to get all orders that are not fully paid.

The following is not working

var OrdersNotFullyPaidShort = 
    context
    .Orders
    .Where(order => !order.PaidCompletely)
    .ToList();

giving the error

The LINQ expression 'Where(source: DbSet, predicate: (o) => !(o.PaidCompletely))' could not be translated...

The following works, but I have to rewrite all the logic again in the query, while I have the logic also defined in the classes:

var OrdersNotFullyPaidLong =
    context
    .Orders
    .Where(order => order.OrderItems.Sum(orderItem => orderItem.PricePerUnit * orderItem.Quantity) == order.Payments.Sum(payment => payment.Amount))
    .ToList();

and generates this quite ugly query:

      SELECT [o].[Id], [o].[Date]
      FROM [Orders] AS [o]
      WHERE (((
          SELECT SUM([o0].[PricePerUnit] * [o0].[Quantity])
          FROM [OrderItems] AS [o0]
          WHERE ([o].[Id] = [o0].[OrderId]) AND [o0].[OrderId] IS NOT NULL) = (
          SELECT SUM([p].[Amount])
          FROM [Payments] AS [p]
          WHERE ([o].[Id] = [p].[OrderId]) AND [p].[OrderId] IS NOT NULL)) AND ((
          SELECT SUM([o0].[PricePerUnit] * [o0].[Quantity])
          FROM [OrderItems] AS [o0]
          WHERE ([o].[Id] = [o0].[OrderId]) AND [o0].[OrderId] IS NOT NULL) IS NOT NULL AND (
          SELECT SUM([p].[Amount])
          FROM [Payments] AS [p]
          WHERE ([o].[Id] = [p].[OrderId]) AND [p].[OrderId] IS NOT NULL) IS NOT NULL)) OR ((
          SELECT SUM([o0].[PricePerUnit] * [o0].[Quantity])
          FROM [OrderItems] AS [o0]
          WHERE ([o].[Id] = [o0].[OrderId]) AND [o0].[OrderId] IS NOT NULL) IS NULL AND (
          SELECT SUM([p].[Amount])
          FROM [Payments] AS [p]
          WHERE ([o].[Id] = [p].[OrderId]) AND [p].[OrderId] IS NOT NULL) IS NULL)

Is there no way to use my first way of querying? What am I doing wrong, are my classes not well defined?

1
2
10/19/2019 10:28:50 AM

Popular Answer

I think this blogpost gives a good explanation on what happens and what possible solutions exist:

https://daveaglick.com/posts/computed-properties-and-entity-framework

0
10/20/2019 8:06:04 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow