Convert Method to Linq Expression for query

c# expression-trees linq linq-to-sql

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.

Popular Answer

Try using Extension Methods, like so:

public static class TransactionLogExtensions {
    public static IQueryable<TransactionLog> OnlyDividends(this IQueryable<TransactionLog> tl)
    {
        return tl.Where(t=>t.SourceTypeID == (int)JobType.Dividend || t.SourceTypeID == (int)JobType.DividendAcct);
    }
}

Call it like so:

var dividends=db.TransactionLogs.OnlyDividends();

or

var dividends=db.TransactionLogs.OnlyDividends().OrderBy(...);


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why