11

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.

Hannes Sachsenhofer
  • 1,835
  • 1
  • 23
  • 38
  • Try using `var lamda = x => x.Id > 1000;`. Don't know it'll help, but it might... – Alxandr Dec 30 '10 at 11:21
  • @Alxandr - that's not legal actually. Lambda expressions can be compiled to either `Func<>` or `Expression>` and in your example the compiler won't be able to tell which one you want, and will throw an error. – Joel Mueller Dec 30 '10 at 19:07

2 Answers2

20

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;
Joel Mueller
  • 28,324
  • 9
  • 63
  • 88
  • Thanks. Unfortunately it won't compile, the compiler error is: Argument 2: cannot convert from 'System.Linq.Expressions.Expression>' to 'System.Func' – Hannes Sachsenhofer Jan 03 '11 at 12:52
  • 1
    That error message seems to indicate that you're calling `Where` or an `Any` on an object that implements IEnumerable but not IQueryable - because the [IEnumerable](http://msdn.microsoft.com/en-us/library/bb534803.aspx) versions expect a Func, where the [IQueryable](http://msdn.microsoft.com/en-us/library/bb548547.aspx) versions expect an Expression. If it won't accept an Expression, the object may not be IQueryable... – Joel Mueller Jan 03 '11 at 18:37
  • It's defined as an EntitySet, as generated by SQLMetal/the Visual Studio Designer for LINQ to SQL table associations. – Hannes Sachsenhofer Jan 04 '11 at 07:58
  • 1
    [This article](http://blogs.msdn.com/b/meek/archive/2011/01/05/linq-macros.aspx) might be of help in your situation... – Joel Mueller Jan 06 '11 at 16:24
  • Thanks for the link, Joel. It doesn't solve my above problem, but it's very useful anyway and gave me some new ideas :) – Hannes Sachsenhofer Jan 07 '11 at 11:08
  • @JoelMueller article is not available now :( – genuinefafa Mar 19 '20 at 12:44
0

Does Table1 refer to the same namespace? In the first example you're querying against the Table1 objects that are directly under dataContext, in the second example you're querying against the Table1 objects that is a property of the Table2 objects, and in the last example you're using a anonymous function which fix the issue.

I would look up the type of the Table1 objects that is a property of a Table2 object and compare it to a Table1 object that is connected directly to the dataContext. My guess is that they differ and your lambda expression is using the type of the object that is connected to the dataContext.

Tomas Jansson
  • 22,767
  • 13
  • 83
  • 137