title: Entity Framework get SUM from child property
body: <p>I have the following model where I'd like to get the sum of all OrderTotalItems for all Orders of a Customer where the OrderTotalType (Enumeration) is "total" or 99:</p>
  
  <pre><code>public class Customer
  {
      ...
      public ICollection&lt;Order&gt; Orders { get; set; } = new Collection&lt;Order&gt;();
  }
  
  public class Order
  {
      ...
      public ICollection&lt;OrderTotalItem&gt; OrderTotalItems { get; set; } = new Collection&lt;OrderTotalItem&gt;();
  }
  
  public class OrderTotalItem
  {
      [Required]
      public int Id { get; set; }
      [Required]
      [Column(TypeName = "decimal(10, 4)")]
      public decimal Value { get; set; }
      [Required]
      public OrderTotalType Type { get; set; }
  }
  </code></pre>
  
  <p>I am building a CustomerAdminDTO to include all relevant data of a customer for the admin client:</p>
  
  <pre><code>public class CustomerAdminDto
  {
      public int Id { get; set; }
      public string UserId { get; set; }
      public Gender Gender { get; set; }
      public string FirstName { get; set; }
      public string LastName { get; set; }
      public string VATId { get; set; } = "";
      public bool VATIdValid { get; set; } = false;
      public DateTime Added { get; set; }
      public DateTime LastModified { get; set; }
      public decimal OrdersTotal { get; set; }
      public CustomerStatusShortDto CustomerStatus { get; set; }
      public CustomerAddressDto CustomerAddress { get; set; }
      public CustomerAddressDto BillingAddress { get; set; }
      public ICollection&lt;OrderListShortDto&gt; Orders { get; set; }
  }
  </code></pre>
  
  <p>In my data service I fill the DTO like that</p>
  
  <pre><code>var customerAdmin = await _context.Customers
      .Include(x =&gt; x.Addresses)
      .Include(x =&gt; x.CustomerStatus)
      .Include(x =&gt; x.Orders)
          .ThenInclude(x =&gt; x.OrderTotalItems)
      .Where(x =&gt; x.UserId == userid)
      .Select(customer =&gt; new CustomerAdminDto 
      {
          Id = customer.Id,
          UserId = customer.UserId,
          Gender = customer.Gender,
          FirstName = customer.FirstName,
          LastName = customer.LastName,
          VATId = customer.VATId,
          VATIdValid = customer.VATIdValid,
          Added = customer.Added,
          LastModified = customer.LastModified,
          OrdersTotal = customer.Orders.Sum(x =&gt; x.OrderTotalItems
              .Where(x =&gt; x.Type == Enums.OrderTotalType.Total)
              .Sum(x =&gt; x.Value)),
          CustomerStatus = new CustomerStatusShortDto
          {
              Id = customer.CustomerStatus.Id,
              Name = customer.CustomerStatus.Name,
          },
      ...
      }
      .FirstOrDefaultAsync();
  </code></pre>
  
  <p>where everything works, except the OrdersTotal.</p>
  
  <p>API compiles fine but throws the following error at runtime:</p>
  
  <blockquote>
    <p>Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot perform an aggregate function on an expression containing an aggregate or a subquery.</p>
  </blockquote>
  
  <p>Thanks for your hints!</p>