LINQ to SQL - Always Encrypted Column - Not working

Question

I am getting this error

The data types varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256',..are incompatible in the equal to operator. Statement(s) could not be prepared".

This is happening when I have enabled Always Encryption in DB and now trying to retrieve data using LINQ to SQL from DB.

Has anyone successfully implemented LINQ to SQL with always encryption option?

Did you already search for this error message? I find e.g. https://stackoverflow.com/questions/56280320/always-encrypted-linq-and-where-contains or https://stackoverflow.com/questions/43195142/always-encrypted-column-linq-query-equals-not-working — Klaus Gütter, Jan 31 '22 at 05:24
@KlausGütter Yeah I have gone through these link and as per there solution they are asking to change datatype of column in database. But I just want to know is there any alternative with minimum changes or not changing database column types. — Yogesh Dukhande, Jan 31 '22 at 06:49
@chetan from cust in db.cust where cust.custid=custid select cust — Yogesh Dukhande, Jan 31 '22 at 06:52
Please provide enough code so others can better understand or reproduce the problem. — Community, Feb 08 '22 at 17:48

score -1 · Answer 1 · answered Feb 01 '22 at 11:31

You need to use attribute as below for the columns you want to keep the data encrypted on your database. The “EncryptColumn” parameter in the “EntityFrameworkCore.EncryptColumn.Attribute” class will be used.

[EncryptColumn]
public string EmailAddress

Next you can use "IEncryptionProvider" interface in ApplicationDBContext.cs.
Next determin your encryption key and and create encryption provider in construtor of ApplicationDBContext.cs.

private readonly IEncryptionProvider _provider; 
public ApplicatioDbContext(){
      Initialize.EncryptionKey = "your_encryption_key";
      this._provider = new GenerateEncryptionProvider();
}

Finally, we indicate that we will use the encryption provider within the “OnModelCreating” method.
```
modelBuilder.UseEncryption(this._provider);
```

You can get more explanation with examples here Encrypt Your Database Columns with EntityFramework & .NET Core

LINQ to SQL - Always Encrypted Column - Not working

1 Answers1