linqpad - big NOT IN clause is going over the limit - any other way?- Linq to SQL

Question

So i am taking the linqpad challenge: http://www.linqpad.net/Challenge.aspx and i've got a report to create of all prospects from one table not in another table, my linq is:

//get customers from one table
var salesNotCancelled = Sales.Where(a=>a.Canceled == 2).Select(x => x.Customer_ID).ToArray();

//query against the other table removing customers based on id
var query=Customers.Where(
        !salesNotCancelled.Contains(a.Customer_ID)
    );
query.Dump();

SQL output is using a 'where not in (ids...)' as expected

BUT error is the hard limit for RPC calls:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is

incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

Is this a case of SQL being better for reports, or am I (probably) doing it wrong?

Any pointers?

Answer 1

Remove the call to .ToArray(). Doing that you prevent half of the query from executing on the server.

Answer 2

If your database has foreign key constraints, LINQPad will automatically create association properties. So you can construct your query in a single step as follows:

Customers.Where (c => !c.Sales.Any (s => s.Canceled == 2))

In other words, "Give me customers without any cancelled sales". Here's the same thing in query syntax:

from c in Customers
where !c.Sales.Any (s => s.Canceled == 2)
select c

If there's no foreign key constraints, you'll need to add a predicate to perform the join:

Customers.Where (c => !Sales.Any (s => s.Customer_ID == c.Customer_ID && s.Canceled == 2))