Always Encryption在连接查询中无法正常工作的问题求助

Hey there! Let's break down why your query is throwing errors with the encrypted companyname field in table4, even after enabling column encryption in web.config. Here are the key issues to check and fixes to try:

1. LINQ Can’t Automatically Handle Encrypted Field Operations #

EF’s default LINQ-to-SQL translation doesn’t natively know how to interact with database-level encrypted fields. If your query is trying to do any of the following with companyname, it’ll fail:

• Projecting the encrypted field into your result
• Using it as a join condition
• Filtering based on its value

Fix: #

• Avoid in-query encryption operations: Fetch the non-encrypted parts of your data first, then handle the encrypted field separately. If you need the decrypted companyname, use a raw SQL query that explicitly calls your database’s decryption function (like SQL Server’s DECRYPTBYKEY). Example:

  var query = @"
      SELECT 
          user.*, mapper.*, type.*, DECRYPTBYKEY(companycategory.companyname) AS DecryptedCompanyName
      FROM table1 user
      LEFT JOIN table2 mapper ON user.CustomerId = mapper.CustomerId
      JOIN table3 type ON user.CustomerTypeId = type.Id
      JOIN table4 companycategory ON [your join condition]
      WHERE user.email = 'test@gmail.com'
  ";
  
  var result = testEntities.Database.SqlQuery<YourCustomViewModel>(query).FirstOrDefault();
  

• If you’re using EF Core, you can implement a value converter to handle encryption/decryption in code, but this works best if you’re managing encryption at the EF level (not purely database-level).

2. Incomplete Web.config & Database Encryption Setup #

Just enabling column encryption in web.config isn’t enough—you need to make sure both the connection string and database are properly configured:

• Check your connection string: It must include Column Encryption Setting=Enabled for SQL Server. Example:

  <connectionStrings>
    <add name="testEntities" 
         connectionString="Server=YourServer;Database=YourDB;User Id=YourUser;Password=YourPass;Column Encryption Setting=Enabled;" 
         providerName="System.Data.SqlClient" />
  </connectionStrings>
  

• Verify database keys: Ensure your database has a valid Database Master Key (DMK) and Column Encryption Key (CEK) created, and that companyname was encrypted using these keys. If the keys are missing or mismatched, decryption will fail silently (or throw an error).

3. Using Encrypted Fields as Join Keys #

If your query is joining tables using companyname (the encrypted field), this will never work—databases can’t compare encrypted values directly (even with deterministic encryption, EF won’t auto-decrypt for joins).

Fix: #

• Switch to using non-encrypted unique identifiers (like primary keys or GUIDs) for your join conditions. Reserve encrypted fields only for data that needs protection, not for relational logic.

4. Outdated EF Version Limitations #

EF 6 and earlier have very limited support for database-level column encryption. If you’re stuck on an older EF version, you’ll likely need to rely on raw SQL queries for any operations involving encrypted fields.

Fix: #

• Consider upgrading to EF Core 3.1 or later—it has better native support for SQL Server’s column encryption features, including transparent integration with encrypted fields in LINQ queries (when configured correctly).

内容的提问来源于stack exchange，提问作者Melody