c# - How to handle divide by zero in Sum() with efcore

Question

I'm struggling with an error affecting a system we used for Insights for our customers. The code is as follows:

Insights MyInsights = new Insights();
MyInsights.UnpostedInvoiceValue = DatabaseContext.Invoice
.Where(z => z.OwnedBy.TenantId == CurrentTenant.TenantId && z.Posted == false)
.Sum(x => x.Items.Sum(y =>(y.PricePerUnit  /  y.ExchangeRate) * y.Quantity / y.Units.PriceFactor));

The issue is when ".ExchangeRate" or "y.Units.PriceFactor" is 0(Zero), as we all a divide by zero isn't possible, and so we get an exception for this.

As this system is multi-tenanted, this seems to affect all tenants even when just one has a 0(Zero) in their data. I would have expected this not to be the case because we have a Where clause which checks the Tenant Id.

The SQL generated is as follows:

SELECT (
 SELECT SUM((([y].[PricePerUnit] / [y].[ExchangeRate]) * [y].[Quantity]) / [y.Units].[PriceFactor])
 FROM [InvoiceItem] AS [y]
 LEFT JOIN [Units] AS [y.Units] ON [y].[UnitsId] = [y.Units].[UnitsId]
WHERE [z].[InvoiceId] = [y].[InvoiceId]
)
 FROM [Invoice] AS [z]
 WHERE ([z].[OwnedByTenantId] = @__CurrentTenant_TenantId_0) AND ([z].[Posted] = 0)

The exception returned is:

System.Data.SqlClient.SqlException: 'Divide by zero error encountered.' 

I see this issue being two-fold:

1. I don't understand why this is affecting all tenants when only one has a 0(Zero) in their data.
2. Ideally, if a 0(Zero) exists in one of those fields, Sum() should return 0, but I can't figure this out, everything I have tried inside the Sum() function doesn't compile.

Thanks

Answer 1

As mentioned in comments, EF is not well suitable for complex queries. But this one is doable.

var query = 
  from z in DatabaseContext.Invoice
  from y in z.Items
  where z.OwnedBy.TenantId == CurrentTenant.TenantId && z.Posted == false
  select new 
  {
     Value = y.ExchangeRate == 0 || y.Units.PriceFactor == 0 
       ? 0 
       : (y.PricePerUnit / y.ExchangeRate) * y.Quantity / y.Units.PriceFactor
  }

MyInsights.UnpostedInvoiceValue = query.Sum(x => x.Value);