CODEWITHSUNDEEP
sql-serverentity-framework-coresavechangesalways-encrypted
SpinDoctor
SpinDoctor

Reputation: 103

Updating/inserting into a table with Always Encrypted columns using EF Core 5

I'm having trouble using Entity Framework Core 5 with the "Always Encrypted" feature in a ASP.NET Core 5 API. I've configured an Azure Key Vault and updated the connection string as necessary. I can read encrypted column data successfully, with code like this:

await using var context = new RcContext();
Company c = await context.Companies.FindAsync(id);

where the Companies table has an encrypted column. The encrypted column is defined in the database as datatype varchar(16) and is returned as plain text in a string member of the entity.

However, trying to update a company or insert new companies using context.SaveChanges() is failing. I get the error

SqlException: Operand type clash: nvarchar(4000) encrypted with ... is incompatible with varchar(16) encrypted with ...

Some suggestions for solving this point to using SqlCommand from SqlClient or stored procedures, or increasing the column's size in the database to nvarchar(max).

Is EF Core not capable of using the normal SaveChanges() pattern to make updates to data in a SQL Server with Always Encrypted columns? How do I make this work with EF Core?

Upvotes: 4

Views: 2090

Answers (1)

SpinDoctor
SpinDoctor

Reputation: 103

With Always Encrypted, the SQL Client needs to know the size of the columns so it can do the encryption on the client. So columns must be attributed like:

[Column(TypeName = "varchar(16)")] public string PaymentCreditCard { get; set; }

I only had to attribute the encrypted columns, not every column. Our source base had not used data annotations prior to this effort, and it wasn't clear to me that they are required for Always Encrypted to work.

Upvotes: 3

Related Questions