Tárolt eljárások és nézetek

Az SQL nyelvben lehetőségünk van nézetek létrehozására. Ezek lényegében virtuális táblák, amelyek egy SELECT lekérdezés eredményén alapulnak és az adatokat nem fizikailag tárolják az adatbázisban, hanem az őket létrehozó lekérdezést futtatják le minden alkalommal. Leginkább akkor van hasznuk, ha egy bonyolult lekérdezést egyszerűsíteni szeretnénk és nem szeretnénk minden egyes alkalommal az SQL-t megírni, illetve az adatok logikai elkülönítésére is igazán hasznosak.

Az Entity Framework közvetlenül nem támogatja a nézetek létrehozását egy DBContext metóduson keresztül, de ez nem jelenti azt, hogy nem tudunk nézeteket alkalmazni. De mielőtt a megvalósításba belevágnánk, nézzük meg, hogy hogyan hozunk létre egy nézetet.

Nézetet a CREATE VIEW parancs segítségével hozunk létre, ami után a nézet nevét kell megadnunk, majd az AS után az SQL utasítássort, ami az adatokat szolgáltatja. Például:

CREATE VIEW SZERZOK AS
SELECT concat(Vezeteknev, Keresztnev) as 'Szerzo' FROM Szerzo
DISTINCT BY 'Szerzo'

Ez az utasítássor létrehoz egy Szerzo nevű nézetet, amiből adatokat további SELECT utasításokkal tudunk továbbszűrni, ha szükséges.

Entity Framework-el nézetket egy egyedi migrációban tudunk létrehozni, meglévőeket pedig ugyan úgy DbSet<T> típusként fel kell vennünk a DBContext leszármazott osztályunkba a nézet mezőit leíró objektummal. Az entitás konfigurálásakor pedig meg kell adnunk az EntityTypeBuilder vagy ModelBuilder segítségével azt, hogy melyik „táblából” vegye az adatokat.

Ezen megoldás hátránya, hogy a DBContext ezután migrációk esetén ugyanolyan táblának kezeli a nézetet, mint a normál táblákat. Ez értelemszerűen nem kívánt mellékhatásokat eredményezhet. Éppen ezért ha ezen megoldás mellett döntünk, akkor az OnModelCreating metódusban migráció esetén érdemes ignoráltatni a nézeteinket:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  if (IsMigration)
    modelBuilder.Ignore<Szerzok>();
}

Egy jobb megoldás, ha egy IQueryable<T> tulajdonságot veszünk fel, ami mögé az adatokat közvetlenül az adatbázisból egy SQL utasítással szállítjuk:

public IQueryable<Szerzo> Szerzok 
    => Database.SqlQuery<Szerzo>("SELECT * FROM SZERZOK");

Ilyen módon nem kell bajlódnunk a migráció ignorálásával, csak a létrehozásával. A létrehozáshoz egy migrációt kell létrehoznunk a dotnet ef migrations add parancs segítségével.

using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;

namespace Konyvek.Database.Migrations
{
    public partial class View : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql("""
                CREATE VIEW SZERZOK AS
                SELECT concat(Vezeteknev, Keresztnev) as 'Szerzo' FROM Szerzo
                DISTINCT BY 'Szerzo'
                """);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql("""
                DROP VIEW IF EXISTS SZERZOK
                """);
        }
    }
}

A migrációban két metódus van, amivel foglalkoznunk kell. Az Up a létrehozásért felelős, a Down pedig a visszavonásért. Az itt paraméternek kapott migrationBuilder Sql metódusával tudjuk a létrehozó és törlő SQL utasításokat lefuttatni.

Tárolt eljárások

Egyes adatbázis-kezelő rendszerek lehetőséget adnak arra, hogy az SQL nyelvükön tárolt eljárásokat hozzunk létre. A tárolt eljárások lényegében olyan függvények, amelyek az adatbázisban élnek az adatokkal együtt és azokon műveleteket tudnak végezni. Ezeknek a segítségével akár komplett üzleti logikát is implementálhatunk az adatbázisunkban. A tárolt eljárások előnye az, hogy mivel a DB-ben élnek, az adatbázis-kezelő előre le tudja őket fordítani és egyéb optimalizációkat tud a műveleteken végrehajtani, ezáltal gyorsabb tud lenni a végrehajtásuk.

Alkalmazásfejlesztési szempontból a tárolt eljárásoknak két nagy baja van. Az egyik baj az, hogy az egy felelősség elve sérülhet nézőponttól függően, mert az adatbázis nem csak az adatok megbízható tárolásáért felelős, hanem a logikának a tárolásáért is. A másik baj pedig az, hogy ha az adatbázisunk tárolt eljárásokat tartalmaz, akkor a logika, ami ezekben van, nem hordozható SQL rendszerek között az eltérő nyelvjárások miatt. Ezen hordozhatatlanság miatt manapság nem sűrűn alkalmaznak tárolt eljárásokat, csak ott, ahol tényleg gyorsítani lehet vele az alkalmazás futását.

A tárolt eljárások használatára az Entity Framework lehetőséget ad. Tételezzük fel, hogy az adatbázisunk egy blogot ír le, amiben szükségünk van az egyes bejegyzésekhez tartozó kommentek számára. Ha Microsoft SQL szervert használunk, akkor ezt valami hasonló módon tudnánk leimplementálni SQL-ben:

CREATE FUNCTION dbo.CommentedPostCountForBlog(@id int)
RETURNS int
AS
BEGIN
    RETURN (SELECT COUNT(*)
        FROM [Posts] AS [p]
        WHERE ([p].[BlogId] = @id) AND ((
            SELECT COUNT(*)
            FROM [Comments] AS [c]
            WHERE [p].[PostId] = [c].[PostId]) > 0));
END

Ezt az eljárást a lekérdezéseinkben SQL-ből meg tudnánk hívni gond nélkül, de akkor buknánk a LINQ előnyeit. Ha LINQ segítségével szeretnénk az ilyen eljárásokat használni, akkor ezeket mappelni kell. Ennek az első lépése, hogy készítünk egy C# metódust, amit az Entity Framework át tud fordítani SQL-re.

Az, hogy hol vesszük fel ezen metódust majdnem mindegy, de érdemes talán a DBContext környékén tartani jelezve, hogy ez egy olyan metódus, amit a DB kínál. Például definiálhatunk a DBContext-ben egy ilyen metódust:

public int ActivePostCountForBlog(int blogId)
    => throw new NotSupportedException();

A NotSupportedException() dobása szándékos, hiszen ez a metódus „csak” mappelést szolgál, nem kell, hogy tényleges implementációval rendelkezzen. Ezt követően jelezni kell az Entity Framework-nek, hogy ha ezzel találkozik egy LINQ kontextusban, akkor a DB-ben lévő eljárást hajtsa végre a NotSupportedException() dobása helyett.

Ezt az OnModelCreating() metódusban tudjuk megtenni, ahol hozzáférésünk van a modelBuilder osztályhoz. Itt a HasDbFunction meghívásával tudjuk összerendelni a kettőt:

modelBuilder
    .HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog)))
    .HasName("CommentedPostCountForBlog");

Ezt követően LINQ-ből használva a metódusunkat az Entity Framework ki tudja generálni a helyes végrehajtandó SQL utasítássort.

var query1 = from b in context.Blogs
             where context.ActivePostCountForBlog(b.BlogId) > 1
             select b;

A fenti LINQ kérés valami hasonló SQL utasítássort fog eredményezni:

SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1

Tags eljárás, function, migráció, nézet, procedure, tárolt, View