Adatbázis kezelése Entity Framework segítségével

Adatbázist a létrehozott DBContext osztályunkon keresztül tudunk kezelni. A Context reprezentálja a teljes adatbázisunkat és az általa biztosított metódusokkal tudjuk kezelni azt.

A DBContext leszármazott osztályunk implementálja az IDisposable interfészt. Ennek az oka az, hogy a DB felé irányuló kapcsolatok tipikus életciklusa (webes környezetben), hogy a felhasználó kér valami adatot, amit kiszolgálunk a DB-ből, majd bontjuk és felszabadítjuk a kapcsolatot. Ha a kapcsolatot nem szabadítanánk fel ebben az esetben, akkor előbb-utóbb elérnénk a DB szerverünk határait és az egy idő után nem lenne képes több ügyfelet kiszolgálni.

Éppen ezért a DB Context osztályunk használatakor törekedjünk arra, hogy vagy using utasításon belül használjuk, vagy gondoskodjunk róla, hogy az a használat után fel tudjon szabadulni megfelelően.

A lekérdezések esetén nem lesz nagy újdonság, mert a LINQ esetén megszokott metódusokkal és módon tudjuk kezelni az adatbázisunkat. Némi eltérés persze lesz, mert nem minden C# kódrészlet fordítható át SQL-re, illetve a szöveg összehasonlítás működésében biztos, hogy eltérés lesz.

Nézzünk is egy példát. Tételezzük fel, hogy az összes olyan könyvre szükségünk van, ami a címében tartalmazza a nyuszi szót. Ebben az esetben kézenfekvő lenne írni valami hasonlót a LINQ tapasztalataink alapján:

using var context = new KonyvekContext();
context.Books
    .Where(book => book.Title.Contains("nyuszi", StringComparison.InvariantCultureIgnoreCase))

Ennek elvileg minden olyan könyvet vissza kellene adnia, amiben szerepel a nyuszi szó az írásmódjától függetlenül. Azonban nem ez fog történni. Futás időben kivételt fogunk kapni, mert nem fogja tudni kifordítani a metódus hívásunkat SQL utasításokra a rendszer.

A kulcs az, hogy a Books itt nem egy IEnumerable<T> ami a memóriában tárolódik, hanem egy egy IQueryable<T>, ami majd a megadott utasítássort SQL-re képezi le és az eredményeket majd valahogy visszaadja.

Ha elhagyjuk a StringComparison.InvariantCultureIgnoreCase beállítást, akkor futás idejű hibát nem kapunk, de nem fog jól működni a keresésünk, mert a "nyuszi" és a "Nyuszi" nem ugyan azt jelenti, nem is beszélve a különböző írásmódú variációkról:

using var context = new KonyvekContext();
context.Books
    .Where(book => book.Title.Contains("nyuszi"))

Jelen esetben megoldás, hogy nem a Contains segítségével végezzük el a keresést, hanem valahogy rávesszük a rendszert arra, hogy az SQL Like operátorára fordítsa a kódot. Ehhez az EF.Functions osztály Like metódusát használhatjuk:

using var context = new KonyvekContext();
context.Books
    .Where(book => EF.Functions.Like(book.Title, "%nyuszi%"));

Az EF.Functions osztály olyan metódusokat tartalmaz, amiket a rendszer át tud fordítani az SQL oldalon függvényhívássá. Néhány fontosabb metódusa:

• double Random()

  Véletlen szám generálása 0 és 1 között.

• bool Like(string matchExpression, string pattern)

  Szöveges értékek és a minták helyettesítésére szolgál helyettesítő karakterek használatával. Ha a keresési kifejezés illeszthető a mintakifejezéshez, a LIKE operátor true értéket ad vissza. Két helyettesítő karaktert használunk a LIKE operátorral együtt: a % jelet és az _ karaktert. A százalékjel nullát, egyet vagy több számot vagy karaktert jelent. Az aláhúzás egyetlen számot vagy karaktert jelöl. Ezek a szimbólumok kombinációkban használhatók.

• bool Glob(string matchExpression, string pattern)

  A GLOB operátor csak szöveges értékeket és helyettesítő karaktereket használó mintákat párosít. Ha a keresési kifejezés illeszthető a mintakifejezéshez, a GLOB operátor true értéket ad vissza. A LIKE operátorral ellentétben a GLOB megkülönbözteti a kis- és nagybetűket, és a UNIX szintaxisát követi a következő helyettesítő karakterek megadásakor, ami a ? lehet és a * karakter. A csillag nullát vagy több számot vagy karaktert , míg a kérdőjel egyetlen számot vagy karaktert jelöl.

• string Hex(byte[] bytes)

  Hexadecimális szöveggé konvertál bytokat. Főleg BLOB típusú mezők esetén hasznos.

• TProperty Collate(TProperty property, string collation)

  A használandó karakterillesztést határozza meg.

Adatok kinyerése

Mivel a DB táblákat IQueryable<T> interfész implementáció reprezentálja, ezt közvetlenül nem tudjuk felhasználni a programunkban arra, hogy mondjuk egy listába beletegyük. Használat előtt konvertálnunk kell az SQL eredményeket.

Itt jönnek képbe az Entity Framework Extension metódusai, mint a ToList<T>. Ez, ahogy a neve mutatja, egy List<T> objektummá alakítja nekünk az eredményeket, amit fel tudunk használni, illetve ha szükséges, akkor még a memóriában LINQ segítségével tovább tudjuk manipulálni az adatot, de akár használhatjuk a ToArray() metódust is, ha tömbre van szükségünk.

Az eddig említett metódusoknak van aszinkron változata is, amik a C# nevezéktant követve Async végződéssel rendelkeznek és Task<T> visszatérésűek. Ezek alkalmazása ajánlott, de itt megjegyezném, hogy ezeket minden esetben az await kulcsszóval kell alkalmazni.

EF esetén ezen metódusok aszinkron változatai direkt grafikus kliens alkalmazásokra lettek kitalva, ahol nem lenne szerencsés, hogy ha a betöltés az egész programot megakasztaná. Ebből adódóan az Async végződésű metódusok használata nem fog teljesítmény növekedést eredményezni az alkalmazásunkban.

A tömb és lista akkor ideális választás, ha tudjuk, hogy a lekérdezés nem fog több millió sort eredményezni. Több millió sor eredménynél már a memória fogyasztással is érdemes számolni. Éppen ezért az AsAsyncEnumerable<T>() metódussal egy aszinkron streammé tudjuk konvertálni a lekérdezés eredményét, amit egyesével, aszinkron módon fel tudunk dolgozni.

Táblák összekapcsolása

Több táblás lekérdezések esetén elkerülhetetlen, hogy a tábla adatokat összekapcsoljuk. Az SQL esetén kitárgyaltuk, hogy erre valóak a JOIN műveletek és a legnépszerűbb az inner join, amikor két vagy több tábla metszetét vesszük.

Entity Framework esetén ha a tábláink rendelkeznek megfelelően beállított Navigation property-kel, akkor ezek mentén Join írása nélkül is tudunk adatokat lekérdezni, mégpedig az Include metódus segítségével.

Ez a lambda kifejezésben megadott navigation property alapján elvégzi a háttérben a az inner join műveletet. Nézzünk egy példát. Ha a könyvek közül azokra a könyveknek az adataira vagyok kíváncsi, ahol a szerző keresztneve Gábor, akkor a teljes könyv adat eléréséhez az alábbi lekérdezést írhatnám:

using var context = new KonyvekContext();
context.Books
    .Include(book => book.Publisher)
    .Include(book => book.Author)
    .Where(book => book.Author.FirstName == "Gábor");

Ha a két összekapcsolandó táblánk között nem lenne navigation property beállítva, akkor a Join metódus segítségével hozhatunk létre inner joint. Például ha a kiadókat akarjuk összekapcsolni kulcs alapján a szerzőkkel, akkor valami hasonló kifejezést írhatnánk:

using var context = new KonyvekContext();
var results = context.Publishers
    .Join(Authors, pub => pub.Id, author => author.Id, (pub, author) => new
{
    pub.Adress,
    author.FirstName,
    author.LastName,
});

A Join első paramétere a másik tábla, amit kapcsolni szeretnénk. Ezt követi két lambda kifejezés ami a két táblából meghatározza, hogy mit mivel kell összekapcsolni, majd egy harmadik lambda, ami a két tábla eredményeiből kiszelektálja a szükséges adatokat.

Érdemes megjegyezni, hogy a navigation propertyk nélkül összekapcsolt táblák esetén a Join eredménye lehet null. Ha inner join helyett left join-t szeretnénk, akkor a LeftJoin metódust kell meghívnunk. Ennek a paraméterezése megegyezik a Join metóduséval.

Egy speciális join típus a GroupJoin, ami csoportosítást is elvégez:

using var context = new KonyvekContext();
var results = context.Publishers
    .GroupJoin(Authors, pub => pub.Id, author => author.Id, (pub, authors) => new
{
    pub.Adress,
    author = authors.FirstOrDefault(),
});

Itt a legutolsó paraméter által meghatározott lambda kifejezés típusaiban van eltérés. Itt nem egy könyvhöz rendelünk egy-egy szerzőt, hanem egy könyvhöz rendeljük csoportosítva a szerzőket. Ebből adódóan az authors nem véletlen lett többesszám, mivel ennek a típusa egy IEnumerable<Author> lesz.

Adatok felvitele és módosítása

Adatokat a létrehozott DB Context osztályunk kipublikált DBSet<T> típusú kollekcióin keresztül tudunk hozzáadni az adatbázishoz, a kollekcióknál megszokott Add és AddRange metódusokkal. Eltérés azonban, hogy az Add és AddRange hívása után azonnal az adatok nem kerülnek be az adatbázisba, csak a memóriában tárolódnak, hogy majd egyszer hozzá kell őket adni.

Ennek az oka az, hogy az Entity Framework alapértelmezetten nyomon követi az entitások változásait és a tényleges frissítő parancsokat csak a SaveChanges metódus meghívásakor küldi ki.

Ezáltal a módosítások csak a ténylegesen megváltozott tulajdonságokra és kapcsolatokra vonatkoznak. További előny, hogy a nyomon követett entitások szinkronban maradnak az adatbázisnak küldött módosításokkal, ami segít minimalizálni az oda-vissza utak számát.

Egy másik előny, hogy így külön Update mechanizmusra nincs szükségünk, mivel egy entitás frissítése csupán annyi, hogy kikérjük a DB-ből, módosítjuk a tulajdonságokat amiket szeretnénk, majd hívunk egy SaveChanges() metódust és megtörténik minden.

Ez a nyomon követés igen hasznos, ha módosításokat is szeretnénk végezni az entitásokon, de lassít és felesleges, ha csak szimplán ki szeretnénk kérni adatokat, amiket majd megjelenítünk. Ebben az esetben a táblát leíró DBSet<T> tulajdonságon hívhatunk egy AsNoTracking() metódust a lekérdezésünk előtt, ami a nyomon követést kikapcsolja. Például a korábbi szerző neve alapú lekérdezésünk kiegészítve az AsNoTracking() opcióval így néz ki:

using var context = new KonyvekContext();
context.Books
    .AsNoTracking()
    .Include(book => book.Publisher)
    .Include(book => book.Author)
    .Where(book => book.Author.FirstName == "Gábor");

Ennek a használata csak olvasás esetén ajánlott, amikor biztosak vagyunk benne, hogy nem fogjuk módosítani az adatot. Ha mégis, akkor nekünk kell manuálisan gondoskodni arról, hogy módosítás történjen és ne egy új entitás felvitele egy SaveChanges() hívás esetén.

Tételezzük fel, hogy a könyv kezelő rendszerünkben minden könyv címéhez hozzá kell fűznünk az "(elfogyott)" szöveget. Ebben az esetben írhatunk egy ilyen query-t:

using var context = new KonyvekContext();
var books = context.Books.ToList();
books.ForEach(book => book.Title += " (elfogyott)");
context.SaveChanges();

A gond ezzel a kéréssel, hogy ez egy SELECT, majd több (annyi db amennyi rekord a táblában van) UPDATE utasításra fog fordulni és a korábbi SQL tapasztalatunkból tudjuk, hogy ez egy darab UPDATE parancs segítségével megvalósítható lenne. Éppen ezért az Entity Framework Core 7-es verziója bevezette az ExecuteUpdate metódust, amivel pont ezt tudjuk elérni:

using var context = new KonyvekContext();
context.Books.ExecuteUpdate(book => book.SetProperty(b => b.Title, b => b.Title + "(elfogyott)"));

A hívásban meg kell mondanunk, a SetProperty metódusban, hogy melyik tulajdonságot szeretnénk módosítani, majd egy ezt követő lambda kifejezésben, hogy mire szeretnénk módosítani.

Ha törölni szeretnénk, akkor az ExecuteDelete használható tömeges törlésre. Például ha minden olyan könyvet szeretnénk törölni az adatbázisból, aminek a címében szerepel a ".NET" szó, akkor a következő módon tudnánk kivitelezni:

using var context = new KonyvekContext();
context.Books
    .Where(book => book.Title.Contains(".NET"))
    .ExecuteDelete();

Ha csak egy, nyomkövetéssel kikérdezett entitást szeretnénk törölni, akkor használhatjuk a DBSet<T> Remove metódusát. Ezt követően hívnunk kell egy SaveChanges() metódust a tényleges törléshez. Például ha korábban a helloVilagkonyv változóba kikértem ennek a könyvnek az adatait, akkor törölni így tudom:

using var context = new KonyvekContext();
var helloVilagkonyv = context.Books
            .Where(book => book.Title == "Helló Világ! Helló C#!")
            .First();
context.Books.Remove(helloVilagkonyv);
context.SaveChanges();

SQL parancsok küldése

Az Entity Framework nem tudja támogatni minden adatbázis egyedi szolgáltatását, mivel nem is célja. Éppen ezért előfordulhat, hogy SQL parancsokat kell majd küldenünk a szervernek.

SQL futtatására az egyik ilyen metódus, amit használhatunk, az a FromSql. Ezt az Entity Framework korábbi változatai FromSqlInterpolated néven ismerik. Ez a metódus bármelyik DBSet<T> típusú kollekción alkalmazható. Ennek egy interpolált szövegként tudunk paramétert adni. Például:

using var context = new KonyvekContext();
context.Books.FromSql($"SELECT * FROM Books").ToList();

Eddig rendben is van, de mi van akkor ha egy felhasználó által megadott értéket szeretnénk az SQL-be illeszteni?
Ilyenkor ügyelni kell arra, hogy elkerüljük az SQL Injection támadásokat. A FromSql és a FromSqlInterpolated metódusok biztonságosak az SQL Injection támadással szemben szemben, és mindig külön SQL paraméterként integrálják a paraméteradatokat. Éppen ezért kell minden esetben a paramétert interpolált szövegként átadni:

using var context = new KonyvekContext();
int ev = 2000;
context.Books.FromSql($"SELECT * FROM Books WHERE PublishYear > {ev}").ToList();

Első ránézésre a fenti SQL szintaxis sima string interpolációnak tűnhet, de a megadott érték egy DbParameter-be van csomagolva, és a generált paraméternév beszúrásra kerül oda, ahova a {ev} helyőrző volt megadva.

Tárolt eljárások végrehajtásakor hasznos lehet elnevezett paraméterek használata az SQL lekérdezésünkben, különösen akkor, ha a tárolt eljárás nem kötelező paraméterekkel rendelkezik:

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToList();

A FromSql és FromSqlInterpolated nem teszi lehetővé dinamikus lekérdezések gyártását, mint a következő:

using var context = new KonyvekContext();
string mezo = "PublishYear";
int ev = 2000;
context.Books.FromSql($"SELECT * FROM Books WHERE {mezo} > {ev}").ToList();

Ennek oka az, hogy a példában szereplő FromSql hívásban az oszlopnév is paraméter és az adatbázisok nem teszik lehetővé az oszlopnevek paraméterezését. Ha ilyen SQL lekérdezéseket szeretnénk gyártani, akkor a FromSqlRaw metódus használható:

using var context = new KonyvekContext();
string mezo = "PublishYear";
var ev = new SqlParameter("ev", "2000");
context.Books.FromSqlRaw($"SELECT * FROM Books WHERE {mezo} > @ev", ev).ToList();

A fenti kódban az oszlop nevét közvetlenül az SQL-be illesztjük be. Ebben az esetben mindig a kód írójának felelőssége, hogy megbizonyosodjon arról, hogy ez a szöveg biztonságos, és eltávolítsa belőle a speciális karaktereket, például a pontosvesszőket, megjegyzéseket és más SQL-konstrukciókat.

Abban az esetben, ha nem lekérdező parancsokat szeretnénk végrehajtani, akkor az ExecuteSql metódust használhatjuk, ami a létrehozott DB context osztályunk Database tulajdonságán keresztül érhető el:

using var context = new KonyvekContext();
context.Database.ExecuteSql($"DELETE FROM Books");

Az ExecuteSql és a ExecuteSqlInterpolated metódusokra ugyanaz igaz, mint a FromSql és FromSqlInterpolated metódusokra. Ezek is védettek SQL Injection ellen, de dinamikus parancs összeállítást nem tesznek lehetővé. Erre a az ExecuteSqlRaw metódus használható, de ezt érdemes erősen fenntartásokkal használni, mivel itt is a programozó felelőssége, hogy biztonságos SQL-t használjon.

A Generált SQL megtekintése

Hibakeresési célokra haszos, ha mondjuk látni tudjuk, hogy a műveleteink pontosan milyen SQL utasításokra is fordulnak. A generált SQL megtekintéséhez a DB context osztályunk OnConfiguring metódusában konfigurálnunk kell egy Log metódust, amit logolásra fog használni a rendszer. Ez lehet akár a Console vagy Debug osztály WriteLine metódusa, vagy akár bármilyen egyedi Log megoldás.

A metódus, amivel a naplózást engedélyezni tudjuk, az az OnConfiguring paramétereként kapott DbContextOptionsBuilder osztály LogTo metódusa. Ennek egy olyan metódust/lambdát tudunk átadni, ami egy string paramétert vár.

A metódus beállítása után kapunk naplózást, de a generált SQL parancsokat még nem fogjuk benne látni. Ennek az oka az, hogy ezek szenzitív adatok és ha rossz kézbe kerülnek, akkor biztonsági kockázatot is jelenthetnek az alkalmazásunkra nézve. Éppen ezért hívják a metódust, amit meg kell hívnunk a szenzitív adatok naplózásának engedélyezéséhez EnableSensitiveDataLogging()-nak.

Ezt éles környezetben nem érdemes bekapcsolva hagyni, mert az ördög sosem alszik. A naplózással kiegészített OnConfiguring() metódus:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlite($"Data Source={DbFile}");
    optionsBuilder.EnableSensitiveDataLogging();
    optionsBuilder.LogTo(log => Debug.WriteLine(log));
}

Tags Alapok, Entity, Framework, Használat, Iqueryable