title: Using EF Core 2.2 to decrypt a string using SQL Server DECRYPTBYKEY
body: <p>Basically I have a POCO model that has a encrypted string. Using EF core 2.2.</p>
  
  <p>We use DECRYPTBYKEY to decrypt strings using SYMMETRIC KEY.</p>
  
  <p>I am using DBSet.FromSQL to pass in SQL query which calls open symmetric key, get the data including the decrypted value, close symmetric key.</p>
  
  <p>FromSQL only allows you to bring back an entity rather than a string by itself.</p>
  
  <p>I have tried adding an decrypted string value on the model and have tried to then set that in FromSQL query.</p>
  
  <p>This actually populates ok when the repository DBSet does not have any .Include in it.</p>
  
  <p>When the DBSet does have .Include (to filter the DBSet on a foreign key table) there's a runtime error which complains about the decrypted string not being a column on the database table - which of course it isn't. So having .Include is calling the SQL on base table first.</p>
  
  <p>If I put the [NotMapped] attribute on the decrypted string column then when the FromSQL query runs it doesn't populate it.</p>
  
  <p>So how can I use this decrypted string column without using [NotMapped] but with using .Include on the DBSet?</p>
  
  <p>I've added code so you can see the problem more. There's no way to add an implementation of Decrypt on the Model as suggested in one answer. Decrypt method requires the DbSet to call FromSQL. The DbSet originates comes from the ConcreteRepository. There's also no way that I can see to call an adhoc SQL Query to return 1 string.</p>
  
  <p><strong>Snip from Original SQL (SQL Server)</strong></p>
  
  <pre><code>    OPEN SYMMETRIC KEY {1} DECRYPTION BY PASSWORD = '{2}';
  
      SELECT  * , --other fields
              CONVERT(VARCHAR(60), DECRYPTBYKEY(A.Encrypted)) AS Decrypted
      FROM    dbo.Model A
      JOIN table2 t2 ON ...
      JOIN table3 t3 ON ...
  
     WHERE A.Id= 123
  
     CLOSE SYMMETRIC KEY {1};",
  </code></pre>
  
  <p><strong>Concrete Repository</strong></p>
  
  <pre><code>public async Task&lt;IEnumerable&lt;Model&gt;&gt; GetAllById(int id)
  {
  
              var filteredSet = Set.Where(x =&gt; x.Id == id)
                 .Include(x =&gt; x.Table2)
                 .Where(x =&gt; x.Table2.IsSomething).ToList();
  
              var models = filteredSet.Select(f =&gt;
                  GetDecryptValue($"Id = {f.Id}");
  
              return models;
  
  }
  
  
  </code></pre>
  
  <p><strong>Base Repository</strong></p>
  
  <pre><code>protected DbSet&lt;TEntity&gt; Set =&gt; _dbContext.Set&lt;TEntity&gt;();
  
  public virtual TEntity GetDecryptValue(string filterCriteria)
          {
              string buildSelectStmt = $"SELECT TOP 1 Encrypted FROM Model";
              string buildSelectStmt2 = $"SELECT *, CONVERT(VARCHAR(MAX), DECRYPTBYKEY(@Value)) AS Decrypted FROM Model";
  
              buildSelectStmt = $"{buildSelectStmt} WHERE {filterCriteria}";
              buildSelectStmt2 = $"{buildSelectStmt2} WHERE {filterCriteria}";
  
              string sql = string.Format(@"
                  DECLARE @Value NVARCHAR(MAX)
                  SET @Value = ({0});
                  OPEN SYMMETRIC KEY {1} DECRYPTION BY PASSWORD = '{2}';
                  {3};
                  CLOSE SYMMETRIC KEY {1};",
                  buildSelectStmt, SymmetricKeyName, SymmetricKeyPassword, buildSelectStmt2);
  
              var result = Set.FromSql(sql);
  
              return result.FirstOrDefault();
          }
  </code></pre>
  
  <p><strong>Model</strong></p>
  
  <pre><code>    public partial class Model
      {
          public int Id { get; set; }
          public string Encrypted { get; set; }
          [NotMapped]
          public string Decrypted { get; set; }
      }
  </code></pre>