Using an expression with a linq query

Question

At the moment I have a linq query with a method residing inside of it. I'm getting the error LINQ to Entities does not recognize the method. So I found I can convert the method to be an expression LINQ to Entities does not recognize the method but I'm wondering is there a clean and easy way to add the expression to my linq query.

Original Method

public bool IsAvailable()
{
    return Eligibility.ProgramType == InteractionProgramTypes.Available;
}

Changed to

public System.Linq.Expressions.Expression<Func<InteractionProgram, bool>> IsAvailable()
{
    return i => i.Eligibility.ProgramType == InteractionProgramTypes.Available;
}

Linq query without expression

x => x.ActivityDate <= endDate && x.IsAvailable()

Linq query with expression

x => x.ActivityDate <= endDate && x.IsAvailable().Compile()

When doing that I get the compiler error, && operator cannot be applied to operands.

May I ask how do I append the expression to my current linq query.

what do you mean by `.Compile()`, what is the result of this? — Jeric Cruz, Sep 25 '17 at 14:23
@JericCruz https://msdn.microsoft.com/en-us/library/bb345362(v=vs.110).aspx — Master, Sep 25 '17 at 14:25
the error is provided by SQL ? or on the compilation / interpretation ? — GGO, Sep 25 '17 at 14:29
`x.IsAvailable().Compile()` simply returns a function. You should *invoke* it `x.IsAvailable().Compile()(someval)` . — L.B, Sep 25 '17 at 14:29
Linq to Entities should be able to "convert" your expression to Sql query, which not happened because it doesn't know how — Fabio, Sep 25 '17 at 14:34
There isn't clear or easy way out of the box. Either break the DRY principle and duplicate the condition in your queries or use some 3rd party library like [LINQKit](https://github.com/scottksmith95/LINQKit). — Ivan Stoev, Sep 25 '17 at 14:41
can you provide more information on how you perform linq query? I think we need to make `IsAvailable.Compile()` to a regular expression something like `x.ProgramType == InteractionProgramTypes.Available`; — Jeric Cruz, Sep 25 '17 at 14:44
The suggested duplicate is a lot narrower than this question. It asks for a solution to a specific implementation problem that could be used as one of many possible solutions to the problem OP is describing here, and it is neither the only possible nor the most elegant solution top OP's problem. I am voting to reopen this question. As usual, I will be happy to re-close it if a better duplicate surfaces. — Sergey Kalinichenko, Sep 25 '17 at 17:31

Sergey Kalinichenko · Accepted Answer · 2017-09-25T14:52:03.813

Since you already converted IsAvailable to return Expression<Func<InteractionProgram,bool>>, all you need to do is to pass the result of calling this method to the Where method of IQueryable<T>:

var res = ctx.InteractionPrograms.Where(InteractionProgram.IsAvailable());

Note that in order for this to compile your IsAvailable method needs to be static. Moreover, you could make it a property for an even better readability:

class InteractionProgram {
    public static Expression<Func<InteractionProgram,bool>> IsAvailable {get;} =
        i => i.Eligibility.ProgramType == InteractionProgramTypes.Available;
    ... // other members of the class
}

...
var res = ctx.InteractionPrograms.Where(InteractionProgram.IsAvailable);

what about the other condition x.ActivityDate <= endDate. Where would that go?

Other conditions go into separate Where clauses either immediately before or immediately after IsAvailable condition. EF driver will combine the two expressions for you, resulting in a single query on RDBMS side.

Another alternative to sharing this expression would be creating an extension method on the EF context that returns IQueryable<InteractionProgram> pre-filtered for availability:

public static IQueryable<InteractionProgram> AvailableInteractionPrograms(this MyDbContext dbCtx) =>
    dbXtx.InteractionPrograms.Where(i =>
        i.Eligibility.ProgramType == InteractionProgramTypes.Available
    );

This hides the function behind a shared method.

@Chris Because it does not use anything from the instance. Basically, an expression has an extra level of "meta indirection" to it - instead of operating on "this" instance directly, it produces an operation (a function) that would operate on any instance that you choose to pass to it. In this case, though, there would be no passing of the instance at all. Instead, the EF driver would take the function apart, and use its content to build an SQL condition. — Sergey Kalinichenko, Sep 25 '17 at 14:55
What you say makes sense to me in terms of why it *can* be static, I'm still not sure I understand why it *must* be static. What error does the compiler actually give I don't have a test environment to test this for myself... — Chris, Sep 25 '17 at 14:59
@Chris It is a pretty safe assumption that the call would be done from a method outside the `InteractionProgram` class, so the compiler is going to complain that it cannot access `InteractionProgram.IsAvailable` method or property from a static context. — Sergey Kalinichenko, Sep 25 '17 at 15:03

Using an expression with a linq query

1 Answers1