Always encrypted with Entity Framework and Includes fails to materialise query

Question

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?

Revetment

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 – Usia

Which EF version are you using? There are significant changes between versions – Usia

Answer 1

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]

Sande

That's not what the error says. Include doesn't map to UNION either, it tells EF to use eager loading using joins – Usia

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 – Sande