LINQ to实体无法识别方法'system.string decrypt（system.string，system.string）'方法，而且此方法无法转换为存储表达式 [英] 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

问题描述

嗨好人（开发人员/程序员）。



女士们和男士我在这里遇到了问题。我试图通过使用电子邮件过滤在combobox中显示电子邮件。我的问题是我的数据在用户表中是加密的。



当我试图解密时它会出现这个错误

Hi good people (developers / programmers).

Ladies and Gents I have a problem here. I am trying to display email in combobox by filtering using email. My problem is that my data is Encrypted in the users table.

When I am trying to Decrypt it it giving 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

如果有任何人有好主意，我该如何解决这个错误？



这是我的Lookup类

How can I solve this error any one have a good idea please?

Here is my Lookup class

public class Lookup
   {
       public long boundvalue { get; set; }
       public string boundtext { get; set; }
   }

这是我要过滤的代码

Here is my code to filter

public IEnumerable<Lookup> getUser(string fText)
           {
               var ret = new List<Lookup>
                             {
                                 new Lookup
                                     {
                                         boundvalue = 0,
                                         boundtext = ""
                                     }
                             };
               if (!string.IsNullOrEmpty(fText))
               {
                   ret.AddRange(_entities.Users.Where(x =>EncDec.Decrypt(x.UserVar01.Trim().Replace("_",string.Empty),
                   Enums.EncDecSecKeyToString(Enums.EncDecSecKey.Email)).Contains(fText.Trim()))
                                .Select(select => new Lookup
                                {
                                    boundvalue = select.UserID,
                                    boundtext = EncDec.Decrypt(select.UserVar01.Trim().Replace("_", string.Empty),
                                    Enums.EncDecSecKeyToString(Enums.EncDecSecKey.Email)),
                                }));

               }
               return ret;
           }

我的尝试：



public IEnumerable

What I have tried:

public IEnumerable

推荐答案

所以使用Linq到Sql，直到您使用.Where在数据库上执行的调用，它将尝试转换为SQL。



说到这一部分：

So with Linq to Sql, until the call you make using .Where is executed on the database, it will attempt to be converted to SQL.

Speaking of this portion:

_entities.Users.Where(x =>EncDec.Decrypt(x.UserVar01.Trim().Replace("_",string.Empty),
                   Enums.EncDecSecKeyToString(Enums.EncDecSecKey.Email)).Contains(fText.Trim()))
                                .Select(select => new Lookup
                                {
                                    boundvalue = select.UserID,
                                    boundtext = EncDec.Decrypt(select.UserVar01.Trim().Replace("_", string.Empty),
                                    Enums.EncDecSecKeyToString(Enums.EncDecSecKey.Email)),
                                }))

使用实体框架，您运行的语句基本上是SELECT * FROM Users WHERE Decrypt（UserVar01.Trim（））LIKE'％fText％' 。当尚未执行linq查询时，它会尝试将该Decrypt函数转换为SQL查询函数，因为你创建了Decrypt，这就是你得到错误的原因。


解决这个问题的方法是以某种方式执行此查询，以便它针对您的数据库运行。您可以通过foreach循环迭代它来执行此操作，也可以调用.ToList（）。根据数据库的大小，我不建议您调用.ToList，因为当您只需要10个记录时，可以加载100k记录。但是.ToList调用解决了你的迫切需求，但你可能会遇到很多其他问题。



所以这个例子将是

With entity framework, the statement you are running is essentially SELECT * FROM Users WHERE Decrypt(UserVar01.Trim()) LIKE '%fText%'. When the linq query has not been executed, it will try and translate that Decrypt function to a SQL query function and because you made that Decrypt, that is why you are getting the error you are seeing.

The work around for this is to execute this query in some manner so it runs against your DB. You can do this by iterating over it via a foreach loop or you can call .ToList(). Depending on the size of your database I do not recommend you calling .ToList as you could load 100k records when you only need 10 of them. But the .ToList call solves your immediate need, but you will probably have tons of other issues.

So this example would be

_entities.Users.ToList().Where.

但是我想你可能需要重新考虑您的数据存储以及解密数据库值以与纯文本值进行比较所需的事实。通常使用加密，您可以存储通常不在搜索条件中使用的加密值。或者你加密一些东西，然后当你需要它作为搜索条件的一部分或不需要时，你将加密纯文本数据/哈希它然后比较那些哈希（例如：密码验证）。

However I think you'll probably need to rethink your data storage and the fact you need to decrypt your DB values to compare against a plain text value. Typically with encryption you store the encrypted value which isn't usually used in search criteria. Or you encrypt something and then when you need it as part of a search criteria or what not, you'll encrypt the plain text data/hash it and then compare those hashes (Ex: password verification).

这篇关于LINQ to实体无法识别方法'system.string decrypt（system.string，system.string）'方法，而且此方法无法转换为存储表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！