I stumbled over some strange LINQ to SQL behaviour - can anybody shed some light on this?
I want to define a lambda expression and use it in my LINQ statement. The following code works fine:
[...]
Func<Table1, bool> lambda = x => x.Id > 1000;
var result = dataContext.Table1s.Where(lambda);
[...]
But when I try to use my lambda expression in a statement on an associated table
[...]
Func<Table1, bool> lambda = x => x.Id > 1000;
var result = dataContext.Table2s.Where(x => x.Table1s.Any(lambda));
[...]
I get an exception:
Unsupported overload used for query operator 'Any'.
But, and this I don't get: It works fine when I put my lambda directly into the query:
[...]
var result = dataContext.Table2s.Where(x => x.Table1s.Any(y => y.Id > 1000));
[...]
WHY?!
Thanks.
Okay, here's the deal: dataContext.Table1s
is of type IQueryable<T>
. IQueryable<T>
defines Where
and Any
methods that take a predicate of type Expression<Func<T, bool>>
. The Expression<>
wrapper is critical, as this is what allows LINQ to SQL to translate your lambda expression to SQL and execute it on the database server.
However, IQueryable<T>
also includes IEnumerable<T>
. IEnumerable<T>
also defines Where
and Any
methods, but the IEnumerable version takes a predicate of type Func<T, bool>
. Because this is a compiled function and not an expression, it can't be translated to SQL. As a result, this code...
Func<Table1, bool> lambda = x => x.Id > 1000;
var result = dataContext.Table1s.Where(lambda);
...will pull EVERY record out of Table1s
into memory, and then filter the records in memory. It works, but it's really bad news if your table is large.
Func<Table1, bool> lambda = x => x.Id > 1000;
var result = dataContext.Table2s.Where(x => x.Table1s.Any(lambda));
This version has two lambda expressions. The second one, being passed directly into Where
, is an Expression
that includes a reference to a Func
. You can't mix the two, and the error message you're getting is telling you that the call to Any
is expecting an Expression
but you're passing in a Func
.
var result = dataContext.Table2s.Where(x => x.Table1s.Any(y => y.Id > 1000));
In this version, your inner lambda is automatically being converted to an Expression
because that's the only choice if you want your code to be transformed into SQL by LINQ to SQL. In the other cases, you're forcing the lambda to be a Func
instead of an Expression
- in this case you're not, so it works.
What's the solution? It's actually pretty simple:
Expression<Func<Table1, bool>> lambda = x => x.Id > 1000;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With