Entity Framework Core and column level SQL Server encryption with stored procedures, how to map properties the right way?

Question

I have made work SQL Server encryption/decryption using stored procedures, symmetric keys using Entity Framework Core.

I am able to save the object in the database, I pass the SSNumber parameter to the stored procedure, it encrypts it and stores it in SSNumberEncrypted.

I also coded a stored procedure to get the record, I set the property SSNumber = DecryptByKey(SSNumberEncrypted). If I run it in SSMS, it works and I get the SSNumber, but when I execute the stored procedure from C#, the property value is always null, because of the NotMapped attribute.

Is there a way that I can accomplish this?

public class Test 
{
      public long Id { get; set; }
      [NotMapped]
      public string SSNumber { get; set; }
      public byte[] SSNumberEncrypted { get; set; }
}

Answer 1

You may try this:

public class Test 
{
      public long Id { get; set; }
      [NotMapped]
      public string SSNumber { get=>DecryptByKey(SSNumberEncrypted); 
                              set=>.......; }
      public byte[] SSNumberEncrypted { get; set; }
}