5

I have a class Like this :

public class menu{
public string Permission{get;set;}
}

The value Of Permission is Encripted . I want all records where Permission is True. To do this, I use this query :

return 
_menuSettings.Where(row => Convert.ToBoolean(Utilities.Encryption.Decrypt(row.Permission,"key"))==true).ToList();

but I get this error :

LINQ to Entities does not recognize the method 'Boolean ToBoolean(System.String)' method, and this method cannot be translated into a store expression.

I searched on google, but I can't solve it .

thanks

Trent Sartain
  • 446
  • 2
  • 10
Psar Tak
  • 682
  • 1
  • 9
  • 27
  • 2
    `Convert.ToBoolean` couldn't be translated to T-SQL, Linq to Entities couldn't recognize it. Have a look at this question: http://stackoverflow.com/questions/34061637/linq-to-entities-does-not-recognize-the-method-system-web-mvc-fileresult/34061692#34061692 – Salah Akbari Dec 20 '15 at 13:42
  • Related posts - [LINQ to Entities does not recognize the method](https://stackoverflow.com/q/7259567/465053) & [Entity Framework Specification Pattern Implementation](https://stackoverflow.com/q/2352764/465053) – RBT Mar 01 '19 at 11:26
  • Another related post - [LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression](https://stackoverflow.com/q/5899683/465053) – RBT Mar 01 '19 at 11:29

2 Answers2

3

What you are asking for cannot be achieved by db query. I'm afraid you are stuck with in memory filtering (hope your don't have too many records) like this

return 
_menuSettings.AsEnumerable().Where(...

here AsEnumerable() will switch the context from Linq to Entities to Linq to Objects

Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
2

Not every method is convertible to SQL thats the essence of that message.

In your case you can compare against the string "true".

_menuSettings.Where(row => Utilities.Encryption.Decrypt(row.Permission,"key").ToLower()=="true").ToList();

Now as mentioned the message means the method is not convertible to SQL. So then by no real surprise get that Utilities.Encryption.Decrypt is also not supported.

Then continue with the same concept of taking things that don't work, out of the query.

The quick and dirty way is to realize/project the data (use ToList() or ToIEnumerable() before you filter with the non-supported filter).

Meaning that you take everything out of the table and filter it on your server instead on the DBMS (SQL server).

Like this. (i have split it into more lines for readability)

var projection = _menuSettings.ToList();
var result = projection.Where(row => Utilities.Encryption.Decrypt(row.Permission,"key").ToLower()=="true").ToList();

A wise choice is to find a good way to limit the projection size before you do heavy work like this.

Thomas Andreè Wang
  • 3,379
  • 6
  • 37
  • 53
  • 2
    And now he'll get `Utilities.Encryption.Decrypt` not supported – Ivan Stoev Dec 20 '15 at 13:49
  • I getting this Error `LINQ to Entities does not recognize the method 'System.String Decrypt(System.String, System.String)' method, and this method cannot be translated into a store expression. ` – Psar Tak Dec 20 '15 at 13:49