Always Encrypted Issue with EF Core 3.0

[bngapiusr]

I'm trying to save data to always encrypted table in sqlserver 2016 database from asp.net 3.0 app but I'm getting Operand type clash: decimal(4,0) encrypted with... I'm using efcore 3.0 context.savechanges() method.
Is this supported in .Net 3.0 and where can I find examples on how to perform crud operations on always encrypted tables in .Net 3.0?
Any help will be appreciated,

Thanks,
Tony

[Wraith2]

Not sure if that's in EF or SqlClient, @roji does this sound like one of yours?

[roji]

Difficult to be sure without more info... I may be wrong, but AFAIK EF doesn't have any special behavior or support for Always Encrypted (i.e. it's a purely lower-level feature).

@bngapiusr can you please post the full exception details, plus ideally a small code sample so we understand what you're doing?

[yukiwongky]

@bngapiusr With Always Encrypted, a lot of implicit conversions that used to work for a non-encrypted column do not work on encrypted columns. For example, type decimal(4.0) cannot be converted to type decimal(18,2).

SqlClient driver does its best to derive the sql type from the client input value. For example, if the input value is 11.00 the driver derives the sql type decimal(4,2), and if this derived sql type does not match the encrypted column you're trying to insert into, you will get the operand type clash error.

To make this work with Always Encrypted, the client application will need to explicitly set the sqldbtype and provide the precision and scale of the parameter by using SqlParameter. With efcore, seems like the only way to do it is by executing raw queries. For example:

var parameter = new SqlParameter("@p1", SqlDbType.Decimal);
parameter.Precision = 18;  // set to the precision of your encrypted column
parameter.Scale = 2;  // set to the scale of your encrypted column
parameter.Value = 1100;  // set to the value you're trying to insert

var commandText = "INSERT INTO YourTable (EncryptedColumnName) VALUES (@p1)";
context.Database.ExecuteSqlCommand(commandText, parameter);

[bngapiusr]

Thanks everyone for your help.
@roji, this is the full error message and stack trace.

Microsoft.EntityFrameworkCore.DbUpdateException
  HResult=0x80131500
  Message=An error occurred while updating the entries. See the inner exception for details.
  Source=Microsoft.EntityFrameworkCore.Relational
  StackTrace:
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(DbContext _, ValueTuple`2 parameters)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()

Inner Exception 1:
SqlException: Operand type clash: decimal(4,0) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_CustomerDb', column_encryption_key_database_name = 'CustomerDb') is incompatible with decimal(18,2) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_CustomerDb', column_encryption_key_database_name = 'CustomerDb')
Statement(s) could not be prepared.

I only get this error when I try to insert, update and delete from an asp.net core 3 app.
this is the connection string setting.

"Server=YourServerName;Database=CustomerDb;Trusted_Connection=True;Column Encryption Setting=Enabled;MultipleActiveResultSets=true"

I also have a .Net Framwork and EF 6.3 mvc app that is using the same database and the Insert, update and delete functionality works great.
You can download the solutions from this repo. https://github.com/bngapiusr/AEDemo

[bngapiusr]

sorry did not mean to close this issue.

[Wraith2]

How is the definition of the column data type currently created? Is it done by EF or by you manually?

[ErikEJ]

@bngapiusr please provide a simple project that reproduces the issue, preferably not using EF Core.

[Wraith2]

I think @yukiwongky's explanation is what you'll need if you're doing it manually. If EF Core needs more input to be able to create the parameters it'd be useful to know what it's currently using to do so. Either way a repro will be good.

[bngapiusr]

@Wraith2, the column data type are created by EF core using code first from an existing database.

[yukiwongky]

@roji do you know how to set parameter specific properties in EFCore? Such as the precision and the scale.

@bngapiusr would be great if you can give us a repro script.

[roji]

@bngapiusr a simple repro project would be helpful here.

In any case, when sending parameters to the database, EF Core doesn't currently set parameter facets (size, precision, scale) to match the corresponding column's definition. It could do this - but I think there were discussions in the past on why that wouldn't be a good idea. Always Encrypted may be a good reason to revisit that.

@AndriySvyryd @smitpatel @ajcvickers do you have info on this? Should we open an issue in EF Core to discuss?

[smitpatel]

Could be related to dotnet/efcore#17799. i.e. the temp table we created for saving has decimal(18,2). We should move this to EF Core, as it seems like integration issue in EF.

[bngapiusr]

@bngapiusr a simple repro project would be helpful here.

In any case, when sending parameters to the database, EF Core doesn't currently set parameter facets (size, precision, scale) to match the corresponding column's definition. It could do this - but I think there were discussions in the past on why that wouldn't be a good idea. Always Encrypted may be a good reason to revisit that.

@AndriySvyryd @smitpatel @ajcvickers do you have info on this? Should we open an issue in EF Core to discuss?

@roji @yukiwongky download the project from this repo https://github.com/bngapiusr/AEDemo.

[roji]

Opened dotnet/efcore#18681 to track setting facets systematically on the EF Core side. This issue can probably be closed (leaving to the Sqlclient team).

[yukiwongky]

Closing as new issue is created on EFCore side.