1

I've got a situation where I need to use a custom expression in a LINQ to Entities query (because I want to have custom logic that L2E wouldn't otherwise understand:

var query = db.MyTable.Where(MyPredicateExpression)

But I'd rather use query comprehension syntax:

var query = from x in db.MyTable where [x matches the predicate of MyPredicateExpression]

I know this is possible, because L2E supports it in other places:

var query = from x in db.MyTable where x.Length > 10

How do they make that work?

Edit: I'm using devart's LinqConnect for Oracle, which may behave somewhat differently than Microsoft L2E.

Joshua Frank
  • 13,120
  • 11
  • 46
  • 95

2 Answers2

1

Entity Framework and LINQ to SQL do not support this scenario, because the translation of MyPredicateExpression should be added to expression tree translator.
I recommend you to create a stored function performing the predicate check and add this function to DataContext. You will be able to use a query like the following in this case:

var query = from x in db.MyTable where context.MyPredicateFunction(x.Field) select x;

Update. Here is the updated query that takes into account your comments: 

int[] values = new int[] { 1, 2, 3 };
var query = from x in db.MyTable where values.Contains(x.AuditState) select x;

Update 2. You can add a Queryable property to your context that will be obtaining the necessary set of MyTable objects as shown in the following example: 


public partial class MyDataContext {  
  IQueryable<MyTable> GetSpecialTables {  
    get {   
      int[] values = new int[] { 1, 2, 3 };
      return this.MyTables.Where(x => values.Contains(x.AuditState));  
    }  
  }
}

Replace MyDataContext with the actual name of your context.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • Can I add a stored function that is not a database function? Here's what I'm trying to do: I've got a field called AuditState that tracks the state of an object, and I often need to do a where clause like: WHERE AuditState = 1 OR AuditState = 2 OR AuditState = 3. Repeating this throughout is annoying, and so I want to put this in a function. But I don't want to make it a function in Oracle, just some logic that gets injected into the query, as in: WHERE IsSpecial(AuditState). How can I add something like this to the data context? – Joshua Frank Jun 02 '11 at 14:56
  • @Joshua, you have missed the where clause. – Devart Jun 02 '11 at 14:58
  • @Devart: I know, I accidentally committed the edit before I was done. Thanks for your help on this. – Joshua Frank Jun 02 '11 at 14:59
  • @Devart: The problem here is that I would need to copy the values array everywhere I want to use the routine, which defeats the point. What I need is a way to tell the expression parser about my logic, so that I can write x.AuditState.IsSpecial (or, less ideally, context.IsSpecial(x.AuditState)), and *during expression translation*, it will translate this to "x.AuditState = 1 OR x.AuditState = 2 OR x.AuditState = 3". If we can hook the translation enough to specify a database function or stored proc, it ought to be possible to specify custom SQL code too. – Joshua Frank Jun 02 '11 at 17:06
  • @Devart: Thanks so much for your help. Totally solved me problem, although I worked out a more convenient way to arrange the code, which I've updated in your original answer. – Joshua Frank Jun 10 '11 at 18:16
0

If I understand the problem correctly, you can either use an extension method OR call a function that returns a bool.

Tom Hines
  • 111
  • 3
  • That would work in Linq to Objects, where the method is evaluated client side, but not in Linq to Entities, where the method has to be translated into SQL for execution on the server. – Joshua Frank Jun 01 '11 at 17:04