I'm having an odd issue.
If I do this statement:
Customer.Invoices.Any(o.Total != 0);
I get a terribly formatted SQL query using joins, NOT using the total, AND selecting every related record that just takes far too long to execute.
Basically, it's ignoring the ANY.
SELECT b.field1 AS COL1, b.field2 AS COL2..... etc
from Customer a LEFT JOIN Invoice AS b ON a.Pk = b.CustomerFk where a.PK = @p0
This query, which should be nearly identical, runs 50x faster and has logical SQL.
context.Invoices.Any(o => o.Customer == customerRecord && o.Total != 0);
SELECT COUNT(1) from (SELECT a.field1 as COL1, a.field2 AS COL2..... etc
from Invoice a where a.CusFk = @p0 AND a.Total <> 0) AS TMP_COUNT
How do I get the first query (using the model's relations) to actually produce an acceptable SQL query (such as the second one?)