使用Entity Framework多Include查询含加密列时出现错误

Hey there! Let's break down why your EF Core query is throwing errors after setting up column-level random encryption in SSMS, and how to fix it:

1. 确保EF Core supports Always Encrypted #

Randomized encryption falls under SQL Server's Always Encrypted feature, and EF Core won't handle encryption/decryption automatically unless you explicitly enable it.

• First, install the required NuGet package: Microsoft.EntityFrameworkCore.SqlServer.AlwaysEncrypted
• Then update your DbContext's configuration to enable Always Encrypted:

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
      optionsBuilder.UseSqlServer("YourConnectionStringHere", 
          opts => opts.EnableAlwaysEncrypted());
  }
  

2. Fix column type mismatch #

Randomized encryption converts plaintext data to binary, so keeping the column as varchar(200) will cause truncation or type conflicts.

• In SSMS, alter the encrypted column to use a varbinary type (e.g., varbinary(MAX) or a size large enough for your encrypted data—usually 2-3x the original plaintext length works).
• Keep your C# string property with the MaxLength(200) attribute; EF Core's Always Encrypted support will handle the string ↔ binary conversion automatically.

3. Debug navigation property loading #

Your query includes nested navigation properties (UserReadOnlyLocations and its related Location), so the error might be coming from an encrypted column in those related entities.

• Simplify the query first: Test without the Include clauses to see if you can retrieve ActiveUsers successfully. If that works, add the includes one by one to pinpoint which related entity is causing the issue.
• Check all encrypted columns: Verify that any encrypted columns in UserReadOnlyLocations or Location follow the same rules (varbinary type in DB, properly configured in EF Core with Always Encrypted enabled).

4. Verify key access permissions #

Always Encrypted relies on encryption keys (stored in Azure Key Vault or local key stores), and your app needs proper access to these keys to decrypt data.

• Ensure your app's runtime identity (your local user account for dev, or service principal in production) has permissions to read the encryption key associated with the column.
• Double-check that the key is still valid and accessible in SSMS (under Security > Always Encrypted Keys).

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