Sharing a where clause between LINQ statements

Question

I have several LINQ-to-Entities queries that hit the same two tables and have the same few conditions. What I'm trying to do is share the where clause between multiple queries. For example, say I have this:

from t0 in db.Table0
from t1 in db.Table1
from t2 in db.Table2
where t0.Field7 == 'something' 
    && t1.Field1 > t2.Field3 
    && t2.NavigationProperty(t => t.Field4 == t1.Field2).Any()
select t0

What I would like is to be able to say something like this:

Func<Table1, Table2, bool> specialCondition
    = (t1, t2) => t1.Field1 > t2.Field3 
        && t2.NavigationProperty(t => t.Field4 == t1.Field2).Any();

from t0 in db.Table0
from t1 in db.Table1
from t2 in db.Table2
where t0.Field7 == 'something' && specialCondition(t1, t2)
select t1

This generates the The LINQ expression node type 'Invoke' is not supported in LINQ to Entities. which makes sense. It can't parse the arbitrary .NET function/delegate code into something it can pass to the database.

Everything I've read on the internet says expression trees, however, can work just fine. My problem is while I can declare it (it's pretty much the same syntax as the function), I don't know how to use it.

Expression<Func<Table1, Table2, bool>> specialCondition
    = (t1, t2) => t1.Field1 > t2.Field3 
        && t2.NavigationProperty(t => t.Field4 == t1.Field2).Any();

What do I do with this? How do I pass it to LINQ2EF?

Update: Working solution with LinqKit as recommended by @Mic, pulled from NuGet (note the .AsExpandable() and the .Invoke and the snipped imported LinqKit reference and namespace):

Expression<Func<Table1, Table2, bool>> specialCondition
    = (t1, t2) => t1.Field1 > t2.Field3 
        && t2.NavigationProperty(t => t.Field4 == t1.Field2).Any();

from t0 in db.Table0.AsExpandable()
from t1 in db.Table1
from t2 in db.Table2
where t0.Field7 == 'something' && specialCondition.Invoke(t1, t2)
select t1

Checking in LinqPad, both the first completely inlined version and the final LinqKit Expression version generate the same SQL and results. Thanks a lot to you all for your suggestions and help.

score 3 · Accepted Answer · answered Dec 15 '11 at 09:34

3

Such constructions are not possible in native L2S/L2Entities provider. You must use LINQKit library, which supports expression expanding.

answered Dec 15 '11 at 09:34

Mic

810
10
15

1

Looks hopeful. I was apparently wrong about the above Expression instantiation being that easy. It compiles, but doesn't run in the context of EF. When I include LinqKit and add `.AsExpandable()` to the first table and add `specialCondition.Compile()(t1, t2)` I get `InvalidCastException: Unable to cast object of type 'System.Linq.Expressions.InstanceMethodCallExpressionN' to type 'System.Linq.Expressions.LambdaExpression'.` which looks similar to [another question](http://stackoverflow.com/questions/7795986/how-to-use-an-expressionfuncmodel-bool-in-a-linq-to-ef-where-condition) – icrf Dec 15 '11 at 15:31
Oops, I still had `Compile` in there instead of `Invoke` which appears to work fine. Thanks a lot for the point in the right direction. I'll edit the question with the functional code. – icrf Dec 15 '11 at 17:46

score 1 · Answer 2 · answered Dec 14 '11 at 22:36

Create the delegate using a CompiledQuery. I'm pretty sure it works in LINQ to Entities. You just need to include the DataContext type as the first parameter.

Func<MyDataContext, Table1, Table2, bool> specialCondition =
    CompiledQuery.Create(
        (MyDataContext dc, Table1 t1, Table2 t2) =>
            t1.Field1 > t2.Field3
         && t2.NavigationProperty(t => t.Field4 == t1.Field2).Any());

Then to use it, you should be able to do this:

var query =
    from t0 in db.Table0
    from t1 in db.Table1
    from t2 in db.Table2
    where t0.Field7 == 'something' && specialCondition(db, t1, t2)
    select t1

That's a good idea, but upon further research, CompiledQuery isn't supported with DbContext in EF4.1 just yet, which is unfortunately what I'm using. It is a known limitation and they're working to get it added back in by the next release: [link](http://blogs.msdn.com/b/adonet/archive/2011/03/02/ef-4-1-is-coming-dbcontext-api-amp-code-first-rtw.aspx) — icrf, Dec 15 '11 at 14:20

okrumnow · Answer 3 · 2011-12-15T06:54:26.647

0

Rewrite your LINQ query to LINQ expression syntax and pass your expression to Where(expression).

edited Dec 15 '11 at 06:54

answered Dec 14 '11 at 22:39

okrumnow

2,346
23
39

All my LINQ queries are declarative (a syntax I find much easier to read) and not lambda. In trying to put together a short test, I find my problem is the expression has multiple inputs. If I had one table instead of two and I tacked a .Where() onto that one table, the expression does indeed work fine. My repeated condition is a relationship between several fields of more than one tables. Is there any way to pass more than one input to the expression? – icrf Dec 15 '11 at 14:35

Sharing a where clause between LINQ statements

3 Answers3