EF Core 6: Custom functions with DbFunctionAttribute
Tags: csharp, efcore, database
Published: 2021-10-29 00:38:30

---
title: EF Core 6: Custom functions with DbFunctionAttribute 
published: true
description: We've already looked at way to implement SQL functions via method translation. That went reasonably well, but next time we had to do something similar we discovered that our code &hellip; Continue reading &#8220;EF Core 6: custom functions with DbFunction Attribute&#8221;
tags: csharp, efcore, database
canonical_url: https://blog.wiseowls.co.nz/index.php/2021/10/29/ef-core-6-custom-functions-with-dbfunction-attribute/
cover_image: https://blog.wiseowls.co.nz/wp-content/uploads/2020/02/tobias-fischer-PkbZahEG2Ng-unsplash-scaled-e1582017537106.jpg
series: Under the hood of Entity Framework
---

We’ve already looked at way to implement SQL functions via [method translation](https://blog.wiseowls.co.nz/index.php/2020/01/26/custom-functions-ef-core-3/). That went reasonably well, but next time we had to do something similar we discovered that our code is broken with newer versions of EF Core. We [fixed it again](https://blog.wiseowls.co.nz/index.php/2021/08/18/ef-core-6-0-does-our-imethodcalltranslator-hack-still-work/).

Not anymore
-----------

Looking through changelogs, we noticed that EF Core 2.0 came with support for [mapping scalar functions](https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.0/#database-scalar-function-mapping). It is remarkably simple to set up:

```csharp
public class MyDbContext : DbContext
    {

        [DbFunction("DECRYPTBYPASSPHRASE", IsBuiltIn = true, IsNullable = false)]
        public static byte[] DecryptByPassphrase(string pass, byte[] ciphertext) => throw new NotImplementedException();

        [DbFunction("DECRYPTBYKEY", IsBuiltIn = true, IsNullable = false)]
        public static byte[] DecryptByKey(byte[] ciphertext) => throw new NotImplementedException();
...
```


and even easier to use:

```csharp
var filteredSet = Set
                .Select(m => new Model
                {
                    Id = m.Id,
                    Decrypted = MyDbContext.DecryptByPassphrase("TestPassword", m.Encrypted).ToString(),
                    Decrypted2 = MyDbContext.DecryptByKey(m.Encrypted2).ToString(), // since the key's opened for session scope - just relying on it should do the trick
                }).ToList();
```


Initially the attribute was offering limited configuration options, but starting with EF Core 5.0, [this is not an issue](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.dbfunctionattribute?view=efcore-5.0#properties).

One gotcha with `DECRYPT*` functions is they return `varbinary`. Trying to use our own `EF.Functions.ConvertToVarChar` is not going to work since we disabled custom plugins. We want to get rid of this code after all. But apparently `.ToString()` works as intended:

```
SELECT [m].[Id], CONVERT(varchar(100), DECRYPTBYPASSPHRASE(N'TestPassword', [m].[Encrypted])) AS [Decrypted], CONVERT(varchar(100), DECRYPTBYKEY([m].[Encrypted2])) AS [Decrypted2], [t].[Id], [t].[IsSomething], [m].[Encrypted], [m].[Encrypted2]...
```


Full example source is [in GitHub](https://github.com/tkhadimullin/ef-core-custom-functions/tree/feature/ef-6.0-dbfunction), along with other takes we decided to leave in place for history.

Conclusion
----------

Defining custom EF functions was one of the biggest articles we wrote. And finding out how to fit it together probably was the most challenging and time-consuming project we undertook in recorded history. One can say we totally wasted our time, but I’d like to draw a different conclusion. We had fun, learned something new and were able to appreciate the complexity behind Entity Framework – it is not just an engineering marvel – it is also a magical beast!