Wprowadzenie
Pracując z Entity Framework, na ogół za często nie schodzimy na poziom samego silnika bazy danych i pracujemy z abstrakcjami dostarczonymi przez Entity Framework. Ale wcześniej czy później pojawi się potrzeba skorzystania z jakiejś funkcji, która nie jest bezpośrednio wspierana w Entity Framework. W tym wpisie pokażę Ci, w jaki sposób zmapować taką funkcję na metodę w kodzie. W przykładzie posłużymy się w tym celu funkcją JSON_VALUE z SQL Servera.
Audit log
Zanim przejdę do właściwego tematu wprowadzenie do przykładu. Załóżmy, że chcemy w bazie danych zapisywać log z operacji biznesowych w systemie (log zdarzeń). Oczywiście można to zrobić na wiele różnych sposób, lepszych lub gorszych 🙂
W takiej tabeli z logami, poza standardowymi danymi jak data zdarzenia, użytkownik, chcielibyśmy zapisać jakieś dodatkowe informacje, które są specyficzne dla danego typu logu. Na przykład id produktu, id zamówienia i tak dalej. Dane te wykorzystamy później na przykład do wyświetlenia ładnej wiadomości, która będzie zawierała aktualne informacje (np. nazwę produktu), a nie wartość z momentu dodania logu.
Możemy do tego wykorzystać kolumnę tekstową, do której będziemy wrzucali jsona z wartościami. Później podczas wyciągania logów możemy skorzystać z funkcji JSON_VALUE i wyciągnąć tylko rekordy, które nas interesują (na przykład dla określonego produktu). Takie zapytanie w sql wyglądałoby tak:
| SELECT * | |
| FROM [AuditLogs] | |
| WHERE JSON_VALUE([Data], N'$.ProductId') = '1612D8E7-8A18-41AD-8BFA-100E1E2D3854' |
Kolumna Data w zapytaniu wyżej zawiera jsona z wartościami.
W przykładzie wykorzystam klasę AuditLog:
| public class AuditLog | |
| { | |
| public Guid Id { get; set; } = Guid.NewGuid(); | |
| public DateTimeOffset Created { get; set; } = DateTimeOffset.UtcNow; | |
| public string LogType { get; set; } | |
| public string Data { get; set; } | |
| } |
Mapowanie funkcji na metodę
Aby zmapować funkcję na metodę, którą później można wykorzystać na przykład w zapytaniach LINQ, musimy zrobić dwie rzeczy:
- Utworzyć pustą metodę, którą użyjemy w kodzie,
- Skonfigurować mapowanie metody na funkcje z użyciem ModelBuilder.
W tym celu w swoich projektach tworzę klasę DatabaseFunctions, do której wrzucam obie te rzeczy. Dzięki temu później łatwo to przerzucić do innego projektu. W przykładzie klasa ta wygląda tak:
| public static class DatabaseFunctions | |
| { | |
| public static string JsonValue(string source, string path) => throw new NotSupportedException(); | |
| public static void Configure(ModelBuilder modelBuilder) | |
| { | |
| modelBuilder | |
| .HasDbFunction(typeof(DatabaseFunctions).GetMethod(nameof(JsonValue))) | |
| .HasTranslation(args => SqlFunctionExpression.Create("JSON_VALUE", args, typeof(string), null)); | |
| } | |
| } |
W trzeciej linijce znajduje się definicja metody, którą użyjemy za chwilę w zapytaniu. Parametry metody odpowiadają parametrom funkcji w SQL Serverze. Natomiast w linijce 7 rozpoczyna się kod odpowiedzialny za mapowanie metody na funkcje. W pierwszej kolejności określamy, jaką metodę będziemy mapowali (wywołanie HasDbFunction), tutaj przekazujemy metodę z trzeciej linijki. Na końcu za pomocą metody HasTranslation określamy mapowanie. Najistotniejszy jest pierwszy parametr metody Create, określający nazwę funkcji w SQL Serverze.
Na końcu pozostaje nam jeszcze użyć metodę Configure podczas konfiguracji modelu. Wywołuję ją w metodzie OnModelCreating klasy DataContext:
| public class DataContext : DbContext | |
| { | |
| //pozostała zawartość | |
| protected override void OnModelCreating(ModelBuilder modelBuilder) | |
| { | |
| base.OnModelCreating(modelBuilder); | |
| DatabaseFunctions.Configure(modelBuilder); | |
| } | |
| } |
Użycie funkcji
Tak przygotowaną metodę/funkcję możemy już użyć w zapytaniach. W naszej przykładowej aplikacji chcemy wyświetlić logi dla określonego produktu. Dlatego w jsonie z danymi będziemy szukali pola o nazwie ProductId, które zawiera id produktu. Kod wyświetlający będzie wyglądał tak:
| private static async Task Show() | |
| { | |
| await using var db = new DataContext(); | |
| var logs = await db.AuditLogs | |
| .Where(l => DatabaseFunctions.JsonValue(l.Data, "$.ProductId") == _productId.ToString()) | |
| .ToListAsync(); | |
| foreach (var auditLog in logs) | |
| { | |
| Console.WriteLine($"Log: {auditLog.LogType}, Date: {auditLog.LogType}, Data: {auditLog.Data}"); | |
| } | |
| } |
W szóstej linijce w warunku Where używamy przed chwilą dodaną metodę DatabaseFunctions.JsonValue, która zostanie zmapowana na funkcję JSON_VALUE.
W przykładowej aplikacji na konsoli wyświetli się coś takiego:
Pierwszy czerwony prostokąt pokazuje wykonane zapytanie. Ładnie tutaj widać wywołanie funkcji JSON_VALUE w warunku WHERE. Natomiast drugi prostokąt pokazuje zwrócone rekordy.
Przykład
Na githubie znajduje się przykład do tego wpisu – https://github.com/danielplawgo/EFCoreFunctions. Po jego pobraniu należy w pierwszej kolejności ustawić poprawnego connection stringa do bazy. W przykładzie dla ułatwienia jest on określony w klasie DataContext w metodzie OnConfiguring. Później wystarczy już tylko uruchomić aplikację.
Podsumowanie
W Entity Framework Core używanie funkcji bazodanowych nie jest niczym skomplikowanym. Wystarczy tylko zmapować ją na metodę w kodzie i skonfigurować podczas budowy modelu. Dzięki mapowaniu możemy skorzystać z funkcji bez konieczności pisania zapytań w czystym SQLu.
Szkolenie modularny monolit w .NET 5
Zainteresował Ciebie ten temat? A może chcesz więcej? Jak tak to zapraszam na moje autorskie szkolenie o modularnym monolicie w .NET.
Nie znałem tego, dzięki ! 🙂