10

I have defined a GenericRepository class which does the db interaction. 

 protected GenericRepository rep = new GenericRepository();

And in my BLL classes, I can query the db like: 

public List<Album> GetVisibleAlbums(int accessLevel)
{
    return rep.Find<Album>(a => a.AccessLevel.BinaryAnd(accessLevel)).ToList();  
}

BinaryAnd is an extension method which checks two int values bit by bit. e.g. AccessLevel=5 => AccessLevel.BinaryAnd(5) and AccessLevel.binaryAnd(1) both return true.

However I cannot use this extension method in my LINQ queries. I get a runtime error as follows:
LINQ to Entities does not recognize the method 'Boolean BinaryAnd(System.Object, System.Object)' method, and this method cannot be translated into a store expression.

Also tried changing it to a custom method but no luck. What are the workarounds?

Should I get all the albums and then iterate them through a foreach loop and pick those which match the AccessLevels?

tshepang
  • 12,111
  • 21
  • 91
  • 136
Kamyar
  • 18,639
  • 9
  • 97
  • 171

3 Answers3

10

I realize this already has an accepted answer, I just thought I'd post this in case someone wanted to try writing a LINQ expression interceptor.

So... here is what I did to make translatable custom extension methods: Code Sample

I don't believe this to be a finished solution, but it should hopefully provide a good starting point for anyone brave enough to see it through to completion.

Lord Terabyte
  • 198
  • 1
  • 5
  • I found that in the extension method `.Where(p=> p.Key == 25)` gives a error about p being unbound. So I had to declare the lambda like this `Expression> exp = (p) => p.Key ==25;` and then use it in `.Where(exp)`. Considering the reusability of the extension, it is still great. There is an open bounty here if you want to post your answer. I would really appreciate you posting a comment if you make updates to your code. This is worthy of a codeplex project I think. http://stackoverflow.com/questions/10826275/iqueryable-extension-method-for-linq2entities – AaronLS Jun 05 '12 at 17:01
  • 1
    @AaronLS I did indeed update it. I wanted to add support for compiled queries and custom extension method to expression translations. Not sure if it's a good solution yet, I was just trying stuff out. You can check it out here: [Code](http://pastebin.com/G3QXS1gb) – Lord Terabyte Jun 08 '12 at 00:53
  • This is super awesome, you should put it in nuget – undefined Sep 06 '16 at 11:32
7

You can only use the core extension methods and CLR methods defined for your EF provider when using Entity Framework and queries on IQueryable<T>. This is because the query is translated directly to SQL code and run on the server.

You can stream the entire collection (using .ToEnumerable()) then query this locally, or convert this to a method that is translatable directly to SQL by your provider.

That being said, basic bitwise operations are supported:

The bitwise AND, OR, NOT, and XOR operators are also mapped to canonical functions when the operand is a numeric type.

So, if you rewrite this to not use a method, and just do the bitwise operation on the value directly, it should work as needed. Try something like the following:

public List<Album> GetVisibleAlbums(int accessLevel)
{
    return rep.Find<Album>(a => (a.AccessLevel & accessLevel > 0)).ToList();  
}

(I'm not sure exactly how your current extension method works - the above would check to see if any of the flags come back true, which seems to match your statement...)

Reed Copsey
  • 554,122
  • 78
  • 1,158
  • 1,373
2

There are ways to change the linq query just before EF translates it to SQL, at that moment you'd have to translate your ''foreign'' method into a construct translatable by EF.

See an previous question of mine How to wrap Entity Framework to intercept the LINQ expression just before execution? and mine EFWrappableFields extension which does just this for wrapped fields.

Community
  • 1
  • 1
Davy Landman
  • 15,109
  • 6
  • 49
  • 73