Encrypting Application Data in .NET - From AES Basics to Keychains, Rotation, and Searchable Trade-offs

If you have ever shipped a feature that stores personal data - emails, free-text notes, API tokens, identification numbers - you have probably had the same uncomfortable thought: the database is going to outlive the trust we have placed in it. Backups get copied to laptops. Snapshots end up on shared file shares. The encrypted-at-rest checkbox in your cloud console only protects against one specific kind of theft: someone walking off with a disk.

Encrypting the data before it reaches the database sounds simple on a slide - AES, a key, done. In a real codebase the simple version breaks the moment you try to rotate the key, or look up a record by email, or explain to a reviewer which key encrypted a given row.

This article walks through the design space for property-level encryption in .NET: the algorithm families and why production systems combine them, what an initialization vector is really protecting, why you want a keychain rather than a single key, and the unhappy menu of options for searching encrypted data. I will use my own library, EfCore.EncryptedProperties, as the recurring concrete example. The concepts are the point; the library is one way of expressing them.

1. What we actually mean by "document encryption"

The phrase covers more than one thing. To keep the scope honest, here is what this article is about and what it is not:

• In scope. Encrypting individual values - a column, a property, a JSON field - before they hit the database. The application holds the key (directly or via a KMS) and the database sees only ciphertext.
• Out of scope. Full-disk encryption (BitLocker, LUKS, Storage Service Encryption). TLS, which protects bytes in transit, not at rest. End-to-end messaging encryption, where the application server itself is not trusted with plaintext. MSSQL "Always encrypted" with keys inside database.

The threat model is the everyday one. Imagine the worst plausible thing happens to your database - a stolen backup, a copied snapshot, a misconfigured bucket - and the attacker has every row you ever wrote. Ideally they see opaque blobs that are useless without a key your application controls.

What this kind of encryption does not protect against is just as important. If an attacker compromises the application process, they can decrypt anything the process can decrypt - period. SQL injection that returns a row returns whatever the application would have decrypted on a normal request. A memory dump captures plaintext just after decryption. Encrypted properties are a fence around the data store, not around the application.

The tour: algorithm families → modes and IVs → envelope encryption → keychains → rotation → key locations → searching encrypted data → a worked example.

2. Two families of algorithms - and why you need both

Cryptography for confidentiality splits cleanly into two families.

Symmetric encryption uses one secret key for both encryption and decryption. AES is the dominant member. It is fast - modern CPUs have dedicated AES instructions and you can encrypt gigabytes per second per core - and it handles arbitrary-length data. The catch is operational: anyone who needs to encrypt or decrypt needs the key, so getting the key to those parties safely is your problem.

Asymmetric encryption uses a key pair: a public key encrypts, a private key decrypts. RSA is the textbook example. Key distribution is solved by construction - you can publish the public key anywhere - but two awkward properties make asymmetric crypto a poor fit for "encrypt a document":

1. It is slow. Two or three orders of magnitude slower than AES for the same payload.
2. The plaintext size is bounded by the key size. RSA-OAEP with a 2048-bit key tops out around 190 bytes per operation. Useful for a key, useless for a record.

The standard production answer is to use both, in a pattern called envelope encryption: a symmetric algorithm does the bulk work, and an asymmetric algorithm (or a KMS) protects the symmetric key. Section 4 unpacks this. The punchline is that "AES vs RSA" is a false choice - you almost always want the AES-encrypted payload and an RSA-wrapped key, in a stable envelope you can store and parse.

EfCore.EncryptedProperties picks AES-256-GCM for the payload and RSA-OAEP-SHA-256 for wrapping the symmetric keys. Both families are present, doing the job each is good at.

3. AES - mode and IV are not implementation details

Saying "we encrypt with AES" tells you almost nothing. AES is a block cipher; on its own it turns 16 plaintext bytes into 16 ciphertext bytes. Everything else - how you encrypt 17 bytes, how you handle the next 16, whether two identical plaintexts produce the same ciphertext - is decided by the mode. The mode is where security lives.

A practitioner's tour:

• ECB (Electronic Codebook). Each 16-byte block is encrypted independently. Identical plaintext blocks produce identical ciphertext blocks, which is why the famous "ECB-encrypted penguin" image is still recognizable. Do not use ECB.
• CBC (Cipher Block Chaining). Each block is XORed with the previous ciphertext block before encryption, broken initially by an initialization vector (IV). Avoids the ECB leakage but provides no integrity - a flipped ciphertext bit becomes a flipped plaintext bit downstream. You need a separate MAC, and people get the construction wrong constantly.
• CTR (Counter). Turns the block cipher into a stream cipher by encrypting successive counter values and XORing with plaintext. Fast, but again no integrity on its own.
• GCM (Galois/Counter Mode). CTR mode plus an authentication tag computed during encryption. The receiver verifies the tag before returning plaintext; tampering is detected. This is what you want.

GCM takes three inputs beyond the key and the plaintext: an IV (12 bytes is canonical), associated data (AAD - authenticated but not encrypted; useful for binding ciphertext to context), and produces a 16-byte authentication tag alongside the ciphertext.

The single most important rule about IVs in GCM: never reuse an IV with the same key. Not "weaker if you do" - catastrophically broken. Two messages encrypted with the same (key, IV) pair leak the XOR of their plaintexts, and the authentication key can be recovered, letting an attacker forge messages at will. This rule has buried more than one production system.

There are three reasonable ways to make sure that does not happen:

1. Random per encryption. Generate a fresh 96-bit IV with a CSPRNG every time. At 2⁹⁶ possible values, collisions are vanishingly unlikely until you have encrypted on the order of 2⁴⁸ messages with the same key.
2. Deterministic counter. Maintain a monotonic counter scoped to the key. Works well if you can guarantee no concurrent encryptors lose track of the counter; that is harder than it sounds on a horizontally-scaled service.
3. Synthetic / SIV. Derive the IV deterministically from the key + AAD + plaintext (AES-SIV, AES-GCM-SIV). Trades some performance for IV-misuse resistance.

EfCore.EncryptedProperties takes option 1, the standard library default:

// src/EfCore.EncryptedProperties/Cryptography/AesGcmEncryptor.cs
private const int IvSizeBytes = 12;
private const int TagSizeBytes = 16;

public static (byte[] Ciphertext, byte[] Tag, byte[] Iv) Encrypt(byte[] key, byte[] plaintext, byte[] aad)
{
    var iv = RandomNumberGenerator.GetBytes(IvSizeBytes);
    var ciphertext = new byte[plaintext.Length];
    var tag = new byte[TagSizeBytes];

    using var aesGcm = new AesGcm(key, TagSizeBytes);
    aesGcm.Encrypt(iv, plaintext, ciphertext, tag, aad);
    return (ciphertext, tag, iv);
}

Three implementation details worth pointing at: the 12-byte IV is the GCM standard size (faster than 16-byte IVs, no hashing step needed); the 16-byte tag is the maximum and the only reasonable choice when you have the room; the using on AesGcm matters because the type holds an unmanaged key handle.

A side-effect of random per-encryption IVs is that ciphertext is non-deterministic. Encrypt the string [e-mail] twice with the same key and you get two different blobs. This is exactly the property that makes the database unable to answer WHERE Email = '[e-mail]' - and we will deal with that head-on in section 8.

4. Envelope encryption - CEK, KEK, and the master key

So far we have one AES key, opaque and apparently magical. The naïve design puts it in appsettings.json and calls it done. Two requirements quickly break that design: rotation (the key needs to change without a re-encryption outage) and key custody (the key should live somewhere harder to reach than the application config).

The answer is to encrypt the data with a short-lived key, and store that short-lived key alongside the ciphertext, itself encrypted by a longer-lived key. That nesting is envelope encryption, and the production version uses three tiers:

• CEK - Content Encryption Key. A fresh AES key, generated for every single encrypted value. It encrypts the actual plaintext and is then immediately wrapped. The CEK is disposable.
• KEK - Key Encryption Key. A longer-lived AES key that wraps CEKs. Typically one per purpose (more on that in section 5). Rotated on a calendar.
• Master key. The root of trust. Lives in a hardened store - a KMS, an HSM, Azure Key Vault, or on a self-hosted machine, a PEM file. Wraps the KEKs. Rotated rarely.

Why three tiers? Because each tier has a different cost profile. CEKs are cheap to generate, expensive to lose-track-of (one per value). KEKs are slightly precious, rotated on schedule, must survive long enough to decrypt anything they ever wrote. Master keys are expensive to replace (they may live in an HSM with audit trails and physical access control), so you want as few operations against them as possible. The wrap-of-a-wrap structure lets each tier do the job it is good at.

The de facto wire format for an envelope-encrypted blob is JWE (JSON Web Encryption) compact serialization, which packs everything into five dot-separated base64url segments:

{header}.{wrapped CEK}.{IV}.{ciphertext}.{auth tag}

EfCore.EncryptedProperties serializes exactly that:

// src/EfCore.EncryptedProperties/Cryptography/JweCompactSerializer.cs
public static string Serialize(JweHeader header, byte[] wrappedCek, byte[] iv, byte[] ciphertext, byte[] tag)
{
    var headerB64 = Base64Url.Encode(Encoding.UTF8.GetBytes(header.ToJson()));
    var encKeyB64 = Base64Url.Encode(wrappedCek);
    var ivB64 = Base64Url.Encode(iv);
    var ciphertextB64 = Base64Url.Encode(ciphertext);
    var tagB64 = Base64Url.Encode(tag);
    return $"{headerB64}.{encKeyB64}.{ivB64}.{ciphertextB64}.{tagB64}";
}

The header is a small JSON object. It carries the algorithm identifiers and - crucially - the kid (key id) that says which KEK wrapped this CEK:

{
  "alg": "A256GCMKW",
  "enc": "A256GCM",
  "kid": "8f4c1b2e-3a9d-4c2e-9b1a-7c5e3a1d2f4b",
  "iv":  "...",
  "tag": "..."
}

alg describes the key-wrapping algorithm (AES-GCM key wrap with a 256-bit key). enc describes the content algorithm (AES-256-GCM). The iv and tag inside the header protect the wrapping operation itself; the iv and tag outside (segments 3 and 5) protect the payload. Two AES-GCM operations, two independent IVs and tags.

The envelope hierarchy:

            ┌───────────────────────────┐
            │  Master key (RSA / HSM)   │
            └──────────────┬────────────┘
                           │ wraps
                           ▼
            ┌───────────────────────────┐
            │   KEK (per-purpose AES)   │
            └──────────────┬────────────┘
                           │ wraps
                           ▼
            ┌───────────────────────────┐
            │   CEK (per-value AES)     │
            └──────────────┬────────────┘
                           │ encrypts
                           ▼
                       Plaintext

Why this matters for the database: every encrypted value carries the kid of its KEK in its own envelope. The application never has to guess which key to use for decryption - the ciphertext tells it. That property is what makes the next section possible.

5. Keychains - when you have more than one key

Time to look at the naïve "one key in config" design under operational pressure.

Scenario: the security team asks you to rotate the encryption key on a 90-day cadence. With one key in config, your options are:

1. Stop the world, re-encrypt every row, deploy the new key. Doable for a 10,000-row table. Catastrophic for a 100-million-row table.
2. Refuse to rotate. Tell the security team the data store cannot meet their policy.

Neither is good. There is also no way to answer the question "which key encrypted this row?" from the ciphertext, so even knowing what needs re-encrypting becomes a guessing game. And if your application stores both customer emails and free-text medical notes with the same key, a compromise of that key compromises both - there is no blast-radius story.

A keychain fixes all three. It is a versioned collection of keys, scoped by purpose, with one currently active for new writes and all older ones retained for reads:

• Writes always use the currently active key for the relevant purpose.
• Reads look at the kid in the ciphertext envelope and fetch that specific key from the chain, regardless of whether it is the active one.
• Purposes segregate keys by data class: email, notes, tokens, each with its own independent rotation schedule and blast radius.

Concretely, here is how the library models a KEK record:

// src/EfCore.EncryptedProperties/KeyManagement/EncryptedKeyRecord.cs
public sealed class EncryptedKeyRecord
{
    public required Guid Id { get; init; }
    public required string Purpose { get; init; }
    public required string RsaKeyId { get; init; }
    public required string Algorithm { get; init; }
    public required string EncryptedKey { get; init; }
    public required DateTimeOffset CreatedAt { get; init; }
    public required bool IsActive { get; init; }
}

A few details worth highlighting. The Id is what ends up in every ciphertext envelope as kid. Purpose is the data domain. RsaKeyId records which master key version wrapped this KEK - important if you ever rotate the master key, because old KEKs still point at the master version that wrapped them. EncryptedKey is the KEK itself, already wrapped by the master key, so the database row is safe to back up.

The keychain manager handles the "which key to use" decision:

// src/EfCore.EncryptedProperties/KeyManagement/KeyChainManager.cs
public async ValueTask<KeyMaterial> GetActiveKeyAsync(string purpose, CancellationToken cancellationToken = default)
{
    // ...cache check, locking elided...
    var record = await _storage.GetActiveAsync(purpose, cancellationToken);
    var shouldRotate = record is not null && ShouldRotate(record);

    if (record is not null && !shouldRotate)
    {
        var decrypted = await DecryptKekAsync(record, cancellationToken);
        // cache + return
        return decrypted;
    }
    // ...generate a new KEK, persist it, log the event, return it...
}

private bool ShouldRotate(EncryptedKeyRecord record)
{
    if (_options.RotationPolicy.KeyRotateAfter is not { } maxAge)
        return false;

    return DateTimeOffset.UtcNow - record.CreatedAt > maxAge;
}

Two things to notice. First, GetActiveKeyAsync is the only operation that ever creates a new KEK - and it only does so when there is no active record or the existing record is past its rotation age. Second, GetKeyForDecryptAsync(keyId) exists separately for the read path, which looks up by kid from the envelope, not by purpose. Reads never trigger rotation. Writes do.

Wiring purposes into the entity model is a one-line affair per property. The library supports both an attribute and the fluent API:

public sealed class Customer
{
    public Guid Id { get; set; }

    [Encrypted("email")]
    public string Email { get; set; } = string.Empty;

    [Encrypted(KeyPurpose = "notes")]
    public EncryptedValue<string> SecretNotes { get; set; } = default!;
}

Email and SecretNotes now live on independent key chains. Rotating email does nothing to notes and vice-versa. A KEK compromise scoped to notes doesn't expose any emails. That is the segregation property a single-key design cannot give you.

One last point: a key chain retains old KEKs by default. They are not deleted, just no longer active for writes - there is always one more row out there encrypted under an old key, until you prove otherwise.

6. Rotation - lazy beats eager

There are two strategies for "rotate the key." Most teams imagine the wrong one.

Eager rotation is the strategy most people draw on the whiteboard: when the new key arrives, re-encrypt every existing row with it, then retire the old key. This works for a 10,000-row table. On a 100-million-row table it is an outage, and on a multi-tenant database it is a multi-day migration with rollback procedures and a lot of meetings.

Lazy rotation is the strategy that actually scales. When the new key takes over:

1. New writes use the new key.
2. Existing rows stay exactly as they are - encrypted under their old key - and the old key is retained in the chain.
3. Reads pull the kid from each row's envelope and decrypt with whichever key wrapped it.

Nothing happens to the existing 100 million rows. No outage, no migration, no rollback plan. The cost is that the old key has to stick around, which - for properly wrapped keys whose plaintext lives only in your master key store - is essentially free.

The library opts in to rotation with a single fluent call. The check itself is the ShouldRotate method shown above:

services.AddEncryptedProperties(cfg => cfg
    .WithFileRsaKeyProvider("rsa-key.pem", "rsa-v1")
    .WithDatabaseKeyChain(SqlClientFactory.Instance, connectionString)
    .WithKeyChainRotation(policy =>
    {
        policy.KeyRotateAfter = TimeSpan.FromDays(90);
    }));

After 90 days the next write for a given purpose triggers KEK generation, the old KEK becomes IsActive = false and stays in the table, and life goes on.

When do you need to re-encrypt eagerly? Only when there is a reason a particular old key cannot continue to exist: a credible suspicion of compromise, a regulator who requires "no data older than X encrypted under key Y", a master-key change that no longer unwraps the old KEK. Then you run a background job that reads each affected row, decrypts with the old KEK, re-saves (which encrypts with the new KEK), and once the count goes to zero you can drop the old record. It is doable, it is just not the default rotation path.

The other half of rotation is auditability - the part that turns into a compliance question. Reviewers want to know: when did the key change? Who triggered it? Did any decryptions fail in the window after? The library emits four event ids for exactly that conversation:

// src/EfCore.EncryptedProperties/EncryptedPropertiesEventIds.cs
public static readonly EventId KeyCreated         = new(1000, nameof(KeyCreated));
public static readonly EventId KeyRotated         = new(1001, nameof(KeyRotated));
public static readonly EventId KeyPreloadFailed   = new(1002, nameof(KeyPreloadFailed));
public static readonly EventId DecryptionFailed   = new(1003, nameof(DecryptionFailed));

The KeyRotated log line carries every field a reviewer asks for - old key id, new key id, which master-key version wrapped each, both creation timestamps:

_logger.LogInformation(
    EncryptedPropertiesEventIds.KeyRotated,
    "Rotated encrypted property KEK for purpose {Purpose} from key {OldKeyId} to key {NewKeyId}. " +
    "Old RSA key {OldRsaKeyId}, new RSA key {NewRsaKeyId}. " +
    "Old created at {OldCreatedAt}; new created at {NewCreatedAt}.",
    ...);

Ship rotation events in the audit log by default - the reviewer will ask.

7. Where does the master key live?

Three deployment shapes, in increasing order of operational maturity:

In-memory. The provider holds an RSA instance that exists for the lifetime of the process and disappears when it exits. Useful for tests, local demos, and integration test fixtures where you do not want to manage on-disk state. Not for anything you intend to read tomorrow:

services.AddEncryptedProperties(cfg => cfg
    .WithInMemoryRsaKeyProvider(RSA.Create(2048), "test-rsa-v1")
    .WithInMemoryKeyChain());

File on disk (PEM). The provider points at a PEM file. If the file is missing on startup, it generates a fresh RSA key and writes one. This is the "self-hosted, single-box" deployment shape, and it works well for it - small services, on-prem installations, hobby projects where the operational surface needs to stay small:

// src/EfCore.EncryptedProperties/Providers/FileRsaKeyProvider.cs
public FileRsaKeyProvider(string filePath, string keyId, int keySizeInBits = 2048)
{
    KeyId = keyId;

    if (File.Exists(filePath))
    {
        _rsa = RSA.Create();
        _rsa.ImportFromPem(File.ReadAllText(filePath));
    }
    else
    {
        _rsa = RSA.Create(keySizeInBits);
        var dir = Path.GetDirectoryName(filePath);
        if (!string.IsNullOrEmpty(dir))
            Directory.CreateDirectory(dir);
        File.WriteAllText(filePath, _rsa.ExportRSAPrivateKeyPem());
    }
}

The trade-off is honest: the PEM file is now a thing your backup strategy must understand. Lose the PEM and every encrypted column is unrecoverable. Treat the file as any other application secret - file-system permissions, no source control, included in DR procedures.

KMS - Azure Key Vault. The provider keeps a KeyClient pointed at a Key Vault key. The local process never sees the RSA private key. To wrap a fresh KEK, the local process fetches the public key (cached) and encrypts. To unwrap an existing KEK, the local process sends the wrapped bytes to Key Vault and gets back the unwrapped CEK:

// src/EfCore.EncryptedProperties/Providers/AzureKeyVaultRsaKeyProvider.cs
public async ValueTask<byte[]> UnwrapKeyAsync(
    byte[] ciphertext,
    string rsaKeyId,
    CancellationToken cancellationToken = default)
{
    // ...
    var cryptoClient = _cryptoClients.GetOrAdd(
        rsaKeyId,
        keyId => new CryptographyClient(new Uri(keyId), _credential));

    var result = await cryptoClient.UnwrapKeyAsync(
        KeyWrapAlgorithm.RsaOaep256,
        ciphertext,
        cancellationToken);
    return result.Key;
}

Two consequences. First, UnwrapKeyAsync is a network call, so the keychain manager aggressively caches unwrapped KEKs in memory. Second, the operational story is dramatically better: RBAC on the key, audit logs of every wrap/unwrap, soft-delete and purge protection, and a private key that is never extractable from the vault.

The same envelope ends up on disk in all three cases. The stolen-laptop difference between the file and Key Vault deployments is enormous - in the first case the attacker has the PEM and can decrypt at will; in the second they have opaque blobs and no way to call into the vault.

8. The search problem - and the menu of bad answers

This is the section the architecture diagrams usually skip. If you encrypt a column properly - fresh per-record CEK, random IV, AES-GCM ciphertext - then the same plaintext produces different ciphertext every single time. That is the security property you want. It is also what makes the database unable to answer:

// This is not going to work the way you hope.
var customer = await db.Customers
    .SingleOrDefaultAsync(c => c.Email == "[e-mail]");

The WHERE compares two ciphertext blobs that will never match - even though the plaintexts do. Indexes on encrypted columns are useless for equality lookup. Joins are useless. LIKE is hopeless. ORDER BY returns rows sorted by random ciphertext.

There are five answers, all with trade-offs. I will name what each one buys and leaks - there is no free option here.

Option A - Deterministic encryption. Drop the randomness: encrypt with a deterministic scheme (AES-SIV with a fixed nonce derivation, or just AES-ECB on a short fixed-size value if you really must) so that the same plaintext always produces the same ciphertext. Buys equality queries, joins, and equality indexes. Leaks equality patterns - anyone with read access to the column sees which rows share a value. For high-cardinality opaque identifiers (think: external system ids), that may be fine. For low-cardinality fields like country, gender, or boolean status, it is a near-perfect frequency-analysis target.

Option B - Blind index (HMAC column). Keep the ciphertext non-deterministic, but store a second column containing HMAC(secret, normalized_plaintext) and index that column normally. To look up by email, compute the HMAC of the query value and search on the index. Buys equality lookup, with the index always pointing at the ciphertext row. Leaks equality on the indexed values (same plaintext → same HMAC) but does not affect the ciphertext column itself, and you control per-column which fields get an index. This is the standard pragmatic pattern and the one most production .NET apps end up with.

Option C - Order-preserving / order-revealing encryption. Special encryption schemes whose ciphertext preserves the ordering of plaintext, so range queries (WHERE age BETWEEN ... AND ...) work. Buys range queries. Leaks the full order of every value, which is a lot - for a column of birthdates, the attacker effectively gets the column. Reasonable for very narrow use cases, dangerous as a general pattern.

Option D - Searchable symmetric encryption (SSE) / fully homomorphic encryption (FHE). Cryptographic schemes that allow query operations directly on ciphertext. Buys real search without revealing plaintext; leaks much less in theory. Performance is academic-grade slow, real .NET implementations are scarce, and the math is unforgiving. Realistic for very few teams.

Option E - Don't search the encrypted store. Keep the encrypted column opaque. Put the searchable representation somewhere else - a separate index service, a search engine like Elasticsearch with its own access controls, or an in-memory lookup populated at write time. Buys full encryption properties on the database column. Pays a separate piece of infrastructure to operate.

In matrix form:

Option	Equality lookup	Range lookup	What it leaks	Operational cost
A: Deterministic	Yes	No	Equality patterns	Low
B: Blind index	Yes	No	Equality on indexed value	Low–medium
C: Order-preserving	Yes	Yes	Full order	Medium
D: SSE / FHE	Yes (limited)	Maybe	Less, but specialized	Very high
E: Out-of-band index	Yes	Yes (depends on store)	Nothing on the encrypted column	Medium–high (second system)

EfCore.EncryptedProperties is firmly in the opaque-column camp. The README says it plainly:

Do not query encrypted columns by plaintext... Ciphertext changes on each write, even for the same plaintext. For lookups, keep a separate non-encrypted lookup column such as a normalized hash.

That is option B, applied at the application layer rather than baked into the library. The library encrypts the column; you add a sibling hash column if you need lookup. The boundary is intentional - what the right hash function is, whether it should be salted per-tenant, and what the normalisation rules are (lowercased? Unicode-normalised? trimmed?) are policy questions the library cannot answer for you.

A common shape for the blind-index pattern in EF Core looks like:

public sealed class Customer
{
    public Guid Id { get; set; }

    [Encrypted("email")]
    public string Email { get; set; } = string.Empty;

    // Blind index: HMAC(server-side-secret, Email.Trim().ToLowerInvariant())
    public string EmailHash { get; set; } = string.Empty;
}

EmailHash carries an index in the database. Writes compute the HMAC; lookups compute the HMAC of the query value and match on EmailHash. The encrypted column remains untouched, and you have a deliberate, narrow leak - "these rows share an email" - that you have consciously accepted.

The most important point in this section: pick your search policy explicitly. Either you can search a field (and you have decided what you are willing to leak) or you cannot (and the application knows that). The failure mode is shipping a feature that assumes search works, then discovering at code review that the column is encrypted and the query returns nothing.

9. Worked example - putting it on an entity

To make all of this concrete, here is the worked walkthrough.

The entity. Two encrypted properties on different purposes; one transparent (Email), one explicit-async (SecretNotes). A Name left in plaintext because it is needed for queries and not sensitive enough to warrant the engineering overhead:

public sealed class Customer
{
    public Guid Id { get; set; }

    public string Name { get; set; } = string.Empty;

    [Encrypted("email")]
    public string Email { get; set; } = string.Empty;

    [Encrypted("notes")]
    public EncryptedValue<string> SecretNotes { get; set; } = default!;
}

The DI wiring.

builder.Services.AddEncryptedProperties(cfg =>
{
    cfg.WithFileRsaKeyProvider(rsaKeyFile, rsaKeyId);
    cfg.WithDatabaseKeyChain(SqlClientFactory.Instance, connectionString);
    cfg.WithKeyChainPreloadOnStartup();
});

builder.Services.AddDbContext<ApiSampleDbContext>((sp, options) =>
{
    options.UseSqlServer(connectionString);
    options.UseEncryptedProperties(sp);
});

For a production deployment, swap WithFileRsaKeyProvider for WithAzureKeyVaultRsaKeyProvider and you keep the rest. WithKeyChainPreloadOnStartup() adds a hosted service that unwraps every stored KEK during host startup - so if the master key is unreachable or misconfigured, the application fails fast instead of throwing on the first encrypted read.

What lands in the database. Customers.Email is a string column holding a five-segment base64url JWE - header.wrappedCek.iv.ciphertext.tag. The kid in the header points at a row in the KEK table, which stores RSA-wrapped KEK material. The database backup contains zero plaintext keys and zero plaintext values; the PEM file (or Key Vault key) is the only thing that can unlock it.

What happens on rotation day. With KeyRotateAfter = TimeSpan.FromDays(90), the next write past day 90 triggers GetActiveKeyAsync, which sees the active KEK is past policy, generates a new one, persists it with IsActive = true, and flips the old one to IsActive = false. From there on, new writes get the new kid; old rows keep their old kid; reads work for both. The application logs a single KeyRotated event with old + new key ids, both RSA key versions, and both creation timestamps - exactly what an audit will ask for. No row migration runs. No customer table is iterated.

What happens when decryption fails. Maybe the PEM was rotated without preserving the old key. Maybe a row arrived from a bad migration. The library logs a DecryptionFailed event with entity type, property name, purpose, and kid - enough to triage without grepping the codebase.

10. Closing - a checklist for your own design

A checklist for the design review:

• Use authenticated encryption. AES-GCM or ChaCha20-Poly1305. Never ECB. CBC only with a separate, correctly-constructed MAC.
• Never reuse an IV with the same key. Random 96-bit IVs are the safe default.
• Envelope. Separate CEK / KEK / master key. "One AES key in config" will not survive its first rotation request.
• Master key in a KMS in production. Azure Key Vault, AWS KMS, GCP KMS, Vault. A PEM on disk is fine for self-hosted single-box only.
• One keychain per data domain. Independent rotation, independent blast radius.
• Lazy rotation. New writes get the new key; old rows decrypt by kid from the chain. Eager re-encryption is the rare exception.
• Log every key event. Created, rotated, decryption failed, preload failed. The reviewer will ask.
• Decide your search policy explicitly. Deterministic, blind index, out-of-band, or genuinely opaque. Don't let a developer discover at code review that the column they planned to query is encrypted.
• Plaintext is still in your process. Encryption at rest is a fence around the data store, not around the application.

If you are building this in .NET on EF Core, you are welcome to use EfCore.EncryptedProperties - it ships everything above: AES-256-GCM with random IVs, JWE envelopes with kid per row, per-purpose keychains, file/in-memory/Azure Key Vault providers, lazy rotation with audit events, and an opaque-column stance that nudges you toward an explicit search policy.

If you build your own, the checklist above is the part that matters. The library is one way to express it. What there isn't, is a way to skip the design work.