Every now and then Stack Overflow provides fantastic opportunities to learn something new. One user asked whether SQL Server’s DECRYPTBYPASSPHRASE can be implemented with Entity Framework Core 2.2 so they can fetch encrypted strings in SQL.<\/p>\n\n\n\n\n\n\n\n
This post<\/a> led us to quickly implement a PoC for EF2.2. We’ve got a Model defined like so:<\/p>\n\n\n\n And a now, defining What EF then does, for each method call it discovers, it checks internal list of I ended up implementing three expression stubs: All in all, it ends up being a pretty trivial string building exercise after all. <\/p>\n\n\n\n Can we port it forward? When we tried applying the same code base to version 3 of the framework we found that code has changed quite a bit. For one, Expressions were no longer an option.<\/p>\n\n\n\n Good news was that the approach was still valid. Custom Method Call translation is still a thing in EF Core 3. Even better news, the functionality seems to have gotten a bit more polished in the new version and now it’s a bit easier to do.<\/p>\n\n\n\n In EF Core 3, we’ve got to deal with this is way easier than the previous version! <\/p>\n\n\n\n Working code can be found on my GitHub<\/a>, check out the difference<\/a> between implementations for both frameworks.<\/p>\n","protected":false},"excerpt":{"rendered":" Every now and then Stack Overflow provides fantastic opportunities to learn something new. One user asked whether SQL Server’s DECRYPTBYPASSPHRASE can be implemented with Entity Framework Core 2.2 so they can fetch encrypted strings in SQL.<\/p>\n","protected":false},"author":1,"featured_media":353,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[11],"tags":[12,23,59],"class_list":["post-346","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dev","tag-c","tag-ef-core","tag-sql"],"yoast_head":"\npublic partial class Model\n{\n public int Id { get; set; }\n public byte[] Encrypted { get; set; } \/\/ apparently encrypted data is stored in `VARBINARY`, which translates to `byte[]`, so I had to tweak it here\n [NotMapped] \/\/ this is still required as EF will not know where to get the data unless we tell it (see down below)\n public string Decrypted { get; set; } \/\/ the whole goal of this exercise here\n public Table2 Table2 { get; set; }\n}<\/code><\/pre>\n\n\n\nConcrete Repository<\/code> to access the DB:<\/p>\n\n\n\npublic IEnumerable<Model> GetAllById(int id)\n{\n \/\/ you will need to uncomment the following line to work with your key\n \/\/_dbContext.Database.ExecuteSqlCommand(\"OPEN SYMMETRIC KEY {1} DECRYPTION BY PASSWORD = '{2}';\", SymmetricKeyName, SymmetricKeyPassword);\n var filteredSet = Set.Include(x => x.Table2)\n .Where(x => x.Id == id)\n .Where(x => x.Table2.IsSomething)\n .Select(m => new Model\n {\n Id = m.Id,\n \/\/Decrypted = EF.Functions.DecryptByKey(m.Encrypted), \/\/ since the key's opened for session scope - just relying on it should do the trick\n Decrypted = EF.Functions.Decrypt(\"test\", m.Encrypted),\n Table2 = m.Table2,\n Encrypted = m.Encrypted\n }).ToList();\n \/\/ you will need to uncomment the following line to work with your key\n \/\/_dbContext.Database.ExecuteSqlCommand(\"CLOSE SYMMETRIC KEY {1};\", SymmetricKeyName);\n return filteredSet;\n}<\/code><\/pre>\n\n\n\nEF.Functions.Decrypt<\/code> is the key here. We basically have to do it twice: <\/p>\n\n\n\nIMethodCallTranslator<\/code> and if it discovers a match – it defers the function to SQL. Otherwise it will have to be run in C#. So all the plumbing we’re going to have to do next is basically needed to inject TranslateImpl<\/code> into that list.<\/p>\n\n\n\nThe
IMethodCallTranslator<\/code> itself<\/h2>\n\n\n\npublic class TranslateImpl : IMethodCallTranslator\n{\n\n private static readonly MethodInfo _encryptMethod\n = typeof(DbFunctionsExtensions).GetMethod(\n nameof(DbFunctionsExtensions.Encrypt),\n new[] { typeof(DbFunctions), typeof(string), typeof(string) });\n private static readonly MethodInfo _decryptMethod\n = typeof(DbFunctionsExtensions).GetMethod(\n nameof(DbFunctionsExtensions.Decrypt),\n new[] { typeof(DbFunctions), typeof(string), typeof(byte[]) });\n\n private static readonly MethodInfo _decryptByKeyMethod\n = typeof(DbFunctionsExtensions).GetMethod(\n nameof(DbFunctionsExtensions.DecryptByKey),\n new[] { typeof(DbFunctions), typeof(byte[]) });\n\n public Expression Translate(MethodCallExpression methodCallExpression)\n {\n if (methodCallExpression.Method == _encryptMethod)\n {\n var password = methodCallExpression.Arguments[1];\n var value = methodCallExpression.Arguments[2];\n return new EncryptExpression(password, value);\n }\n if (methodCallExpression.Method == _decryptMethod)\n {\n var password = methodCallExpression.Arguments[1];\n var value = methodCallExpression.Arguments[2];\n return new DecryptExpression(password, value);\n }\n\n if (methodCallExpression.Method == _decryptByKeyMethod)\n {\n var value = methodCallExpression.Arguments[1];\n return new DecryptByKeyExpression(value);\n }\n\n return null;\n }\n}<\/code><\/pre>\n\n\n\nDecryptByKey<\/code>, DecryptByPassphrase<\/code> and EncryptByPassphrase<\/code>, for example:<\/p>\n\n\n\npublic class DecryptByKeyExpression : Expression\n{\n private readonly Expression _value;\n\n public override ExpressionType NodeType => ExpressionType.Extension;\n public override Type Type => typeof(string);\n public override bool CanReduce => false;\n\n protected override Expression VisitChildren(ExpressionVisitor visitor)\n {\n var visitedValue = visitor.Visit(_value);\n\n if (ReferenceEquals(_value, visitedValue))\n {\n return this;\n }\n\n return new DecryptByKeyExpression(visitedValue);\n }\n\n protected override Expression Accept(ExpressionVisitor visitor)\n {\n if (!(visitor is IQuerySqlGenerator))\n {\n return base.Accept(visitor);\n }\n visitor.Visit(new SqlFragmentExpression(\"CONVERT(VARCHAR(MAX), DECRYPTBYKEY(\"));\n visitor.Visit(_value);\n visitor.Visit(new SqlFragmentExpression(\"))\"));\n return this;\n }\n\n public DecryptByKeyExpression(Expression value)\n {\n _value = value;\n }\n}<\/code><\/pre>\n\n\n\nBut the question remained <\/h2>\n\n\n\n
Good news<\/h2>\n\n\n\n
Solution<\/h2>\n\n\n\n
ISqlExpressionFactory<\/code> to get most of the job done. It gets passed around to plugins and has convenient methods to generate required expressions:<\/p>\n\n\n\npublic class TranslateImpl : IMethodCallTranslator\n {\n private readonly ISqlExpressionFactory _expressionFactory;\n\n private static readonly MethodInfo _encryptMethod\n = typeof(DbFunctionsExtensions).GetMethod(\n nameof(DbFunctionsExtensions.Encrypt),\n new[] { typeof(DbFunctions), typeof(string), typeof(string) });\n private static readonly MethodInfo _decryptMethod\n = typeof(DbFunctionsExtensions).GetMethod(\n nameof(DbFunctionsExtensions.Decrypt),\n new[] { typeof(DbFunctions), typeof(string), typeof(byte[]) });\n\n private static readonly MethodInfo _decryptByKeyMethod\n = typeof(DbFunctionsExtensions).GetMethod(\n nameof(DbFunctionsExtensions.DecryptByKey),\n new[] { typeof(DbFunctions), typeof(byte[]) });\n\n public TranslateImpl(ISqlExpressionFactory expressionFactory)\n {\n _expressionFactory = expressionFactory;\n }\n\n public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList<SqlExpression> arguments)\n {\n var args = new List<SqlExpression> { arguments[1], arguments[2] }; \/\/ cut the first parameter from extension function\n if (method == _encryptMethod)\n {\n return _expressionFactory.Function(instance, \"EncryptByPassPhrase\", args, typeof(byte[]));\n }\n if (method == _decryptMethod)\n {\n return _expressionFactory.Function(instance, \"DecryptByPassPhrase\", args, typeof(byte[]));\n }\n\n if (method == _decryptByKeyMethod)\n {\n return _expressionFactory.Function(instance, \"DecryptByKey\", args, typeof(byte[]));\n }\n\n return null;\n }\n }<\/code><\/pre>\n\n\n\nTalk is cheap. Show me the code<\/h2>\n\n\n\n