Convert Method to Linq Expression for query

Question

In our application we want to have standard methods for various conditions in our database. For instance, we have different types of transactions, and we want to create standard methods for retrieving them within other queries. However, this gives us the error:

Method '' has no supported translation to SQL

The method might look like this:

public static bool IsDividend(this TransactionLog tl)
{
    return tl.SourceTypeID == (int)JobType.Dividend || tl.SourceTypeID == (int)JobType.DividendAcct;
}

To be used as such:

var dividends = ctx.TransactionLogs.Where(x => x.IsDividend());

Of course, if I copy the logic from IsDividend() into the Where clause, this works fine, but I end up duplicating this logic many places and is hard to track down if that logic changes.

I think if I would convert this to an expression like this it would work, but this is not as preferable a setup as being able to use methods:

public Expression<Func<TransactionLog, bool>> IsDividend = tl => tl.SourceTypeID == (int)JobType.Dividend || tl.SourceTypeID == (int)JobType.DividendAcct;

var dividends = ctx.TransactionLogs.Where(IsDividend);

Is there a way to force Linq to evaluate the method as an expression? Or to "transform" the method call into an expression within a linq query? Something like this:

var dividends = ctx.TransactionLogs.Where(tl => ToExpression(tl.IsDividend));

We are using Linq-to-SQL in our application.

Answer 1

Well having static property containing the expressions seems fine to me.

The only way to make it work with Methods would be to create a method which returns this expression, and then call it inside where:

public class TransactionLog 
{
    Expression<Func<TransactionLog, bool>> IsDividend() {
        Expression<Func<TransactionLog, bool>> expression = tl => tl.SourceTypeID == (int)JobType.Dividend || tl.SourceTypeID == (int)JobType.DividendAcct;
        return expression;
    }
}

public class TransactionLogExtension
{
    Expression<Func<TransactionLog, bool>> IsDividend(this TransactionLog log) {
        Expression<Func<TransactionLog, bool>> expression = tl => tl.SourceTypeID == (int)JobType.Dividend || tl.SourceTypeID == (int)JobType.DividendAcct;
        return expression;
    }
}

and use it via

var dividends = ctx.TransactionLogs.Where(TransactionLog.IsDividend());

or as extension method

var dividends = ctx.TransactionLogs.Where(x.IsDividend());

But none of it is will work with var dividends = ctx.TransactionLogs.Where(x => x.IsDividend()); because x => x.IsDividend(); itself is an expression tree and your database provider can't translate "IsDividend" into an SQL statement.

But the other two options will at least allow you to pass in parameters (which doesn't work if you store the Expressions as instance or static properties).