CODEWITHSUNDEEP
linqentity-framework.net-4.0linq-to-entitiesexpression-trees
icrf
icrf

Reputation: 364

Sharing a where clause between LINQ statements

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.

Upvotes: 4

Views: 1104

Answers (3)

Mic
Mic

Reputation: 810

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

Upvotes: 3

okrumnow
okrumnow

Reputation: 2416

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

Upvotes: 0

Jeff Mercado
Jeff Mercado

Reputation: 134591

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

Upvotes: 1

Related Questions