12

I am having issues getting always encrypt to play nice with Entity Framework.

I am targetting .Net 4.6.1, have enabled Column Encryption Setting=Enabled in my connection string and i can successfully make a call and receive the decrypted content using 

var results = dbContext.EncryptedTable.ToList()

EncryptedTable has 1 column encrypted using deterministic with a datatype of Varchar(Max).

DbContext has CodeFirst backing of 

Property(x => x.EncryptedColumn)
    .HasColumnName("EncryptedColumn").IsRequired().IsUnicode(false);

Once i start to use includes on my dbContext things start to go bad.

This works

var id = Guid.Parse("123-456-789");
var result = dbContext
    .TableA
    .Include(x => x.EncryptedTable)
    .FirstOrDefault(x => x.id == id);

This throws error: Operand type clash: varchar is incompatible with varchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256',

var id = Guid.Parse("123-456-789");
var result = dbContext.TableA
    .Include(x => x.TableB)
    .Include(x => x.EncryptedTable)
    .FirstOrDefault(x => x.id == id);

Doing a SQL profile on the 2 calls in can see the 2nd one is failing on the call to exec sp_describe_parameter_encryption.

Is this scenario supported with EF and always encrypted?

ArunPratap
  • 4,816
  • 7
  • 25
  • 43
Lukie
  • 905
  • 1
  • 11
  • 21
  • The error has nothing to do with `Include` - the column types don't match. I'd bet that if you tried to load something from the encrypted *entity* (ORMs have entities and relations, not tables), you'd get the same error – Panagiotis Kanavos Oct 24 '19 at 07:24
  • Which EF version are you using? There are significant changes between versions – Panagiotis Kanavos Oct 24 '19 at 07:25

1 Answers1

0

The reason that your last query didn't work is that always encrypted feature doesn't support complex query. Union is one of the not supported syntax that appear when you're using EF include syntax (for one to many relationship).

You may need to rework your query into 2 queries instead to avoid using union. [Sorry for any grammar mistake]

waltz4dmoon
  • 3
  • 1
  • That's not what the error says. `Include` doesn't map to `UNION` either, it tells EF to use eager loading using *joins* – Panagiotis Kanavos Oct 24 '19 at 07:23
  • EF Include does use UNION syntax on certain condition (couldn't find any reference about the algorithm). Here is an example link that you could use to avoid the always encrypted error [link](https://entityframework.net/improve-ef-include-performance) – waltz4dmoon Oct 25 '19 at 03:23