0

I recently encrypted a column in SSMS using the Encrypt Columns functionality using randomised encryption. The column's data type is varchar(200) and the property in C# is string with MaxLength(200).

I have this code:

var user = ctx.ActiveUsers
              .Include(u => u.UserLocations.Select(ul => ul.Location.Region))
              .Include(u => u.UserReadOnlyLocations)
              .FirstOrDefault(u => u.Username == username);

where UserReadOnlyLocations is an object that contains Location as a navigational property.

When I remove either the first or second .Include so there is only one, this works without error. However if I run it as above, I get the error:

SqlException: Operand type clash: varchar is incompatible with varchar(200) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'cek', column_encryption_key_database_name = 'DB') collation_name = 'Latin1_General_BIN2'

Is there a fix/workaround for this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dhughes
  • 645
  • 1
  • 7
  • 19
  • I'm kind of curious if this happens when its a string without a maxlength – TheGeneral Apr 23 '18 at 03:52
  • Theres a few problems still with entity framework and encrypted columns (https://github.com/aspnet/EntityFramework6/issues/389) In some of these situations they've managed to skirt the issue by converting to a view, or stored procedure to pull the results you need via EF. Also wondering if something like an nvarchar might work? – Rando Apr 23 '18 at 03:58
  • @TheGeneral I tried firstly without MaxLength but that didn't work :( – dhughes Apr 23 '18 at 04:02
  • @Rando we're using EF in a lot of places - converting to sprocs/views etc would be a massive shift for us. – dhughes Apr 23 '18 at 04:04
  • @brokenbeta It is, and is quite the pain in the ass, unfortunately EF has a fair few things it still needs to be made to do which im sure in time will get better, but theres still certain situations where a raw query is required (i.e. running user functions, and possibly encrypted columns). Sidenote have you attempted things like adding .AsNoTracking() to your includes, or creating a dummy table with different datatypes to see how EF handles encryption on the types (nvarchar, text etc.) – Rando Apr 23 '18 at 04:10
  • @Rando do you know if any other ORMs handle encryption better? Or third party packages? I haven't tried .AsNoTracking() or dummy table etc to resolve this issue yet - sounds like I may have to ;) – dhughes Apr 23 '18 at 04:16
  • @brokenbeta theres some that handle certain items better, but then others atrociously, EF handles most things pretty well and allows for pretty fast development as long as you don't want to go out of the standard. I've found i now need to do a little bit of EF and a little bit of straight sql in all my programs (for instance bulk inserts / uploads are way faster using straight up sql if you end up over 1000 rows, especially so if any of your tables have multiple keys) – Rando Apr 23 '18 at 04:23

0 Answers0