Method cannot be translated into a store expression (LINQ)

Question

This is my get/set method where I encrypt the password:

[Required]
[DataType(DataType.Password)]
[Display(Name = "Password")]
public string Password {
    set {
        var emp = db.Employees.Find(2);
        password = EncryptDecrypt.Encrypt(emp.Password, "a15s8f5s6e2s3g1w5");
    }
    get {
        return password;
    }

}

I use the following code to allow the user to login and I also use it to decrypt the password:

private Employee slogin;

            using (var db = new Entities())
            {
                var erg = from s in db.Employees
                          where s.LastName.ToString() == model.UserName && s.Password == EncryptDecrypt.Decrypt(model.Password, "a15s8f5s6e2s3g1w5")
                          select s;
                slogin = erg.FirstOrDefault();

            }

Every time when I run the code I get a NotSupportedException here: slogin = erg.FirstOrDefault();

{"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."}

You should just query the employees table by Username and then afterwards evaluate the password if a record existed for the username. You are trying to do both at the same time. — tawman, Jun 02 '17 at 22:02
I hope this is not for a Production site. ASP.NET has its own tried and tested identity system, don't invent a square wheel yourself. For starters, passwords should be hashed, not encrypted. — H H, Jun 03 '17 at 08:20

score 3 · Answer 1 · answered Jun 02 '17 at 22:04

"cannot be translated into a store expression" means : I can't cook SQL from that.

The following ought to work but storing decryptable passwords like this is of course not a good practice:

var decryptedpassword = EncryptDecrypt.Decrypt(model.Password, "a15s8f5s6e2s3g1w5");

var erg = from s in db.Employees
          where s.LastName == model.UserName 
             && s.Password == decryptedpassword
          select s;

score 2 · Accepted Answer · edited Jul 09 '18 at 05:33

Decrypt the password outside the query:

var p=EncryptDecrypt.Decrypt(model.Password, "a15s8f5s6e2s3g1w5");
using (var db = new Entities())
{
            var erg = from s in db.Employees
                      where s.LastName == model.UserName && s.Password == p
                      select s;
            slogin = erg.FirstOrDefault();

}

An easier way:

using (var db = new Entities())
{
  slogin = db.Employees.FirstOrDefault(s=>s.LastName == model.UserName && s.Password == p);
}

Method cannot be translated into a store expression (LINQ)

2 Answers2