Always encrypted with Entity Framework and Includes fails to materialise query

user_198662 0
Viewed 963

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?

always-encrypted entity-framework linq c#
1 Answers

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]

user_77651900
Related