Convert method to Linq expression for query

Question

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

Method '' does not support SQL translation

The method might look like this:

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

Used as such:

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

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

I think that if I converted it to an expression like this, it would work, but it is not so preferable, since the installation is 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 get Linq to evaluate a method as an expression? Or "convert" a method call to an expression in a linq query? Something like that:

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

We use Linq-to-SQL in our application.

Answer 1

Well, having a static property containing expressions seems to me good.

The only way to make it work with methods is to create a method that 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 through

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

or as an extension method

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

But none of them will work with var dividends = ctx.TransactionLogs.Where(x => x.IsDividend()); , because x => x.IsDividend(); itself is an expression tree, and the database provider cannot translate "IsDividend" into an SQL statement.

But the other two parameters will at least allow you to pass parameters (which does not work if you save expressions as instances or static properties).

Answer 2

I think LINQ to SQL does not fully support even general and built-in functions. (At least EF does not). And what's more, when it comes to custom methods. I predict that your variant with the expression will fall, as well as the variant with the method call, if you did not call it after the enumeration (ToList or a similar method). I propose to maintain a balance between 1) stored procedures on the server, 2) normal conditions, hard-coded in Where clauses in C #, 3) generation of expression trees in C #. All these points are relatively complicated, of course (and, in my opinion, there is no simple and general solution).

Answer 3

Try using extension methods, for example:

 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 this:

 var dividends=db.TransactionLogs.OnlyDividends(); 

or

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

Answer 4

For this scenario you can use Func. Linq works great with them. Here is a simple example of using Func in a Linq query. Feel free to modify and use.

 Func<int,bool> isDivident = x => 3==x; int[] values = { 3, 7, 10 }; var result = values.Select (isDivident );