ORM alapú adatbáziskezelés alapjainak elsajátítása CRUD műveletek írásán keresztül. LINQ-to-Entities lekérdezések írásának gyakorlása.
A labor elvégzéséhez szükséges eszközök:
- Microsoft SQL Server (LocalDB vagy Express edition)
- SQL Server Management Studio
- Visual Studio 2022 .NET 6 SDK-val telepítve
- Adatbázis létrehozó script: mssql.sql
Amit érdemes átnézned:
- EF Core előadás anyaga
- SQL nyelv
- Microsoft SQL Server használata segédlet és videó
- A használt adatbázis sémája
Felkészülés ellenőrzése:
- A gyakorlatra való felkészüléshez használható ezen kérdőív.
- A gyakorlaton beugróként ugyanezen kérdőívet fogjuk használni, legalább 50% elérése szükséges.
- Gyakorlatvezetői instrukció: a hallgató nyissa meg a kérdőívet és töltse ki. A válaszok megadása után az utolsó oldalon a "View results" gombra kattintva megtekinthető az eredmény és az elért pontszám. A hallgató ezen az oldalon álljon meg és mutassa meg eredményét a gyakorlatvezetőnek.
Az utolsó feladatot leszámítva a feladatokat a gyakorlatvezetővel együtt oldjuk meg. Az utolsó feladat önálló munka, amennyiben marad rá idő. A közös feladatok megoldásai megtalálhatóak az útmutatóban is. Előbb azonban próbáljuk magunk megoldani a feladatot!
Az adatbázis az adott géphez kötött, ezért nem biztos, hogy a korábban létrehozott adatbázis most is létezik. Ezért először ellenőrizzük, és ha nem találjuk, akkor hozzuk létre újra az adatbázist. (Ennek mikéntjét lásd a "Tranzakciókezelés" gyakorlat anyagában.)
Az EF, mint ORM eszköz használatához az alábbi összetevőkre van szükség:
- objektummodel kódban
- relációs modell az adatbázisban - ez már kész
- leképezés (mapping) az előbbi kettő között, szintén kódban megadva
- maga az Entity Framework Core, mint komponens
- Entity Framework Core kompatibilis adatbázis driver
- adatbázis kapcsolódási adatok, connection string formátumban
Az objektummodellt és a leképezést generáltatni fogjuk az adatbázis alapján - ez az ún. Reverse Engineering modellezési módszer.
- Hozzunk létre Visual Studio-ban egy .NET 5 alapú C# nyelvű konzolalkalmazást. Ehhez válasszuk ki a C# nyelvű konzolalkalmazás sablonok közül a simát (ne a .NET Framework jelölésűt). Futtatókörnyezetként válasszuk a .NET 6-ot. Próbáljuk ki, hogy működik-e, kiíródik-e a "Hello World". .NET6-ban nem kell
Main
függvényt írni, a belépési pontnak szánt kódfájlban (jelenleg a Program.cs-ben) írhatunk egyből utasításokat, nem kell se függvényt, se osztályt létrehoznunk. - Nyissuk meg a Package Manager Console-t (PMC) a Tools ➡️ NuGet Package Manager ➡️ Package Manager Console menüponttal
- Telepítsük fel az EF kódgenerátor eszközt projektfüggőségként, illetve az SQL Server adatbázis drivert. A generátor eszköznek már kapcsolódnia kell az adatbázishoz, amihez szüksége van a driverre. PMC-ben hajtsuk végre:
Install-Package Microsoft.EntityFrameworkCore.Tools
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Ezek a csomagok függőségként magát az Entity Framework Core-t is telepítik.
- Generáltassuk az adatbázismodellt az alábbi paranccsal. Paraméterei: kapcsolódási adatok (Connection), adatbázis driver neve (Provider), a projekten belül a generálandó fájlok könyvtára (OutputDir), a generálandó adatbáziskontextus neve (Context). Dokumentáció itt. A connection string-ben ne felejtsük el átírni az értékeket, pl. a neptun kódot kitölteni. A connection string szerkezete SQL Server esetén: kulcs=érték párok pontosvesszővel elválasztva. Nekünk most az alábbi adatok kellenek:
- Server - a szerver neve
- Database - adatbázis neve a szerveren belül (ez ennél a gyakorlatnál a neptun kód lesz)
- Trusted_Connection=True - ez a Windows authentikációt takarja
- NoPluralize - a táblák eléréshez többesszámosított property nevek generálódnának alapból, viszont ez csak angol nyelvű táblaneveknél működne jól, ezért ezt a funkciót kikapcsoljuk
A connection string szerkezete gyártónként eltér és elég sok paramétere lehet. Bővebben itt.
Szintén PMC-ben:
Scaffold-DbContext -Connection "Server=(localdb)\mssqllocaldb;Database=<neptun>;Trusted_Connection=True;" -Provider Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context ACMEShop -NoPluralize
A generált kódban figyeljük meg az alábbiakat:
- az egyes táblabeli soroknak (rekordoknak) megfelelő C# osztályokat (pl. Termek.cs), azon belül az oszlopoknak megfelelő egyszerű property-ket és a kapcsolódó tábláknak megfelelő más osztályok típusával rendelkező navigációs property-ket (pl.
Termek.Afa
). Ezek alkotják az objektummodellt. - a kontextusosztályt (pl. ACMEShop.cs), ami az általunk megadott névvel jött létre és magának az adatbázisnak a leképezése. Tartalmazza:
- a tábláknak megfelelő
DbSet<RekordTipus>
property-ket, ezek a tábláknak felelenek meg - az adatbázis szintű konfigurációt (
OnConfiguring
függvény), pl. kapcsolódási adatokat, bár azt inkább külön konfig fájlban szoktuk tárolni, nem a kódban (erre van is figyelmeztetés a generált kódban) - a leképezések kódját az
OnModelCreating
függvényben. Minden rekordtípus minden property-jére megadja, hogy melyik tábla melyik oszlopára lépződik. Navigációs property-knél megadja a kapcsolat egyes résztvevőinek számosságát (1-N-es, N-N-es), valamint ha van, akkor az idegen kulcs kényszert is.
- a tábláknak megfelelő
Mindezzel minden összetevőt létrehoztunk az EF alapinfrastruktúrához.
Írjuk meg az első EF Core lekérdezésünket. Az SQL mérés alapján itt is kérdezzük le az összes vevőt, majd írjuk ki azonosítójukat, nevüket és felhasználói nevüket is.
A Program.cs-be:
using (ACMEShop ctx = new ACMEShop()) // kontext létrehozása. Using blokk, mert használat után illik az adatbáziskapcsolatot lezárni.
{
foreach (Vevo vevo in ctx.Vevo) //a ctx.Vevo a lekérdezésünk, a Vevo táblát reprezentálja
{
Console.WriteLine($"{vevo.Nev} ({vevo.Id}, {vevo.Login})");
}
}
Próbáljuk ki, örvendezzünk, hogy milyen egyszerű és elegáns a kód. Semmilyen SQL stringet / SQL kódot nem kellett írnunk.
Minden olyan ORM használatakor, ahol az ORM állítja elő az SQL-t, elementárisan fontos, hogy lássuk, milyen SQL fut le az adatbázisszerveren. Nyomkövetni lehet az adatbázis oldalán (adatbázis eszközzel), illetve a programunk oldalán (nyomkövető komponenssel) is. Előbbire példa SQL Server esetén az SQL Server Profiler Trace eszköze. Mi most az utóbbi irányt követjük. A kontext OnConfiguring
függvényébe:
optionsBuilder.UseSqlServer("connection string") //ez a rész maradjon változatlan
.LogTo(message => System.Diagnostics.Debug.WriteLine(message),LogLevel.Information); //ez a rész ékelődjön be a ; elé
A debug kimenet az Output ablakra van kötve - viszont csak akkor, ha a Visual Studio debugger csatlakoztatva van. Ezért fontos, hogy ha látni akarjuk az EF naplókat, akkor Debug módban (zöld nyíl, F5 billentyű) futtassuk az alkalmazást.
Próbáljuk ki, az Output ablak alján meg kell jelennie egy hasonló SQL-nek:
info: [időbélyeg] RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (26ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [v].[ID], [v].[Email], [v].[Jelszo], [v].[KozpontiTelephely], [v].[Login], [v].[Nev], [v].[Szamlaszam]
FROM [Vevo] AS [v]
Ha hibakeresés miatt részletesebb naplóra van szükség, akkor a LogLevel
típusú paramétert (ideiglenesen) állítsuk Loglevel.Debug
-ra.
Minden részfeladatot a using
blokkon belül írjunk. Ha zavar a többi részfeladat kódja, kommentezzük ki őket. Minden részfeladatnál ellenőrizzük a kiírt eredményt az adatbázisadatok alapján is (pl. Management Studio-ban) és figyeljük meg a generált SQL-t is az Output ablak alján.
-
Kérdezze le az összes vevőt, írja ki a nevüket, azonosítójukat és felhasználónevüket! - Ezt már megoldottuk!
Megoldás
foreach (Vevo vevo in ctx.Vevo) { Console.WriteLine($"{vevo.Nev} ({vevo.Id}, {vevo.Login})"); }
-
Listázza ki, hogy eddig milyen nevű termékeket rendeltek!
Megoldás
//bármelyik jó a két alábbi lekérdezés közül var termeknevek = ctx.MegrendelesTetel.Select(mt => mt.Termek.Nev).Distinct(); //MegrendelesTetel -> Termek //var termeknevek = ctx.Termek.Where(mt => mt.MegrendelesTetel.Any()).Select(t => t.Nev); //Termek -> MegrendelesTetel foreach (string? tn in termeknevek) { Console.WriteLine(tn); }
Használjuk a navigációs property-ket és a
System.Linq
névtérben található LINQ operátorokat. Kiindulhatunk a termék, illetve a megrendeléstétel táblából (DbSet
-ből) is. Érdemes kipróbálni mind a két változatot - nem ugyanolyan SQL generálódik.A
string
utáni kérdőjel nem kötelező, azzal jelezzük, hogy az érték lehetnull
is. Hiszen a terméknév nem kötelező adat az adatbázisban. -
Hány nem teljesített megrendelésünk van (a státusz alapján)?
Megoldás
Console.WriteLine(ctx.Megrendeles.Count(mr => mr.Statusz.Nev != "Kiszállítva"));
Ez ujjgyakorlat navigációs propertyt használva.
-
Melyek azok a fizetési módok, amiket soha nem választottak a megrendelőink?
Megoldás
var fmList=ctx.FizetesMod.Where(fm => !fm.Megrendeles.Any()).Select(fm=>fm.Mod); foreach (string? fm in fmList) { Console.WriteLine(fm); }
-
Rögzítsünk be egy új vevőt! Írjuk ki az újonnan létrejött rekord kulcsát!
Megoldás
Vevo ujvevo = new Vevo { Nev = "Teszt Elek", Login = "t.elek", Jelszo = "******", Email = "[email protected]" }; ctx.Add(ujvevo); ctx.SaveChanges(); Console.WriteLine(ujvevo.Id);
Az adatváltozásokat (pl. beszúrások) a kontext gyűjti. Az
Add
sor után a kontext nyilvántartja az új példányunkat új rekordként, de még nem csinál az adatbázison semmit. ASaveChanges
a felgyűlt módosításokat egy füst alatt érvényesíti az adatbázison és vissza is frissíti az érintett C# oldali objektumokat - így aSaveChanges
után az új vevő azonosítója ki lesz töltve. Debuggerrel láthatjuk is, ahogy aSaveChanges
soron átjutva kitöltődik az azonosító. Ellenőrizzük a változást az adatbázisban.A megoldás után ezt a részt kommentezzük ki, ne szúrjunk be minden kipróbálásnál új sort.
-
A kategóriák között hibásan szerepel az Fajáték kategória név. Javítsuk át a kategória nevét Fakockákra!
Megoldás
var fakocka = ctx.Kategoria.Single(k => k.Nev == "Fajáték"); fakocka.Nev = "Fakockák"; ctx.SaveChanges();
Első lépésként le kell kérdeznünk a módosítandó entitást, elvégezni a módosítást, amit a kontext megint csak nyilvántart, végül érvényesíteni a módosításokat. A Watch ablakban figyeljük meg a kontext által nyilvántartott állapot változását a
ctx.Entry(fakocka).State
kifejezés figyelésével. Ellenőrizzük a változást az adatbázisban.A megoldás után ezt a részt kommentezzük ki, ha nincs az adatbázisban fajáték, a
Single
kivételt fog dobni, mivel pontosan egy darab rekordot vár.Sajnos ez a művelet így két SQL parancs (egy
SELECT
és egyUPDATE
), jelenleg az EF Core nem tudja ezt egy utasításként megoldani, bár vannak hozzá kiterjesztések erre a célra. -
Mely kategóriákban van a legtöbb termék? Írjuk ki ezen kategóriák nevét.
Megoldás
//naív megoldás, csak egy eredmény Console.WriteLine(ctx.Kategoria.OrderByDescending(k => k.Termek.Count()) .Select(k=>k.Nev).First()); //navigációs propertyvel - kivételt dob! var maxkat=ctx.Kategoria .Where(k => k.Termek.Count == ctx.Kategoria.Max(kk=>kk.Termek.Count)) .Select(k=>k.Nev); foreach (var k in maxkat) { Console.WriteLine(k); } //navigációs propertyvel - működik, de két lekérdezés var maxkat = ctx.Kategoria.Select(kk => kk.Termek.Count) .AsEnumerable() // ez elsüti a lekérdezést, minden kategória termékszámát lekérdezzük .Max(); var ktg = ctx.Kategoria.Where(k => k.Termek.Count == maxkat).Select(k=>k.Nev); foreach (var k in ktg) { Console.WriteLine(k); } //másik megközelítés: azokat a kategóriákat keressük, amiknél nincs több termékes kategória //egy lekérdezés és működik is var maxkat= ctx.Kategoria .Where(k1=>!ctx.Kategoria.Any(k2=> k2.Termek.Count > k1.Termek.Count)) .Select(k=>k.Nev); foreach (var k in maxkat) { Console.WriteLine(k); }
Sajnos elég nehéz olyan lekérdezést összerakni C#-ban, ami az SQL összerakásánál ne dobna kivételt vagy ne több lekérdezésből állna 😰. Nem minden logikailag helyes, forduló LINQ kód alakítható SQL-lé, ráadásul ez csak futási időben derül ki. Érdemes ilyenkor megírni a sima SQL-t és ahhoz hasonlóan összerakni a C# kódot, plusz minden lekérdezést legalább egyszer erősen ajánlott kipróbálni futás közben is.
-
Az egyes telephelyekre hány rendelés volt eddig? Írjuk ki a telephelyek azonosítóját és a rendelések számát!
Megoldás
var tmegrList = ctx.Telephely.Select(t => new { t.Id, MegrendelesSzam = t.Megrendeles.Count }); foreach (var tmegr in tmegrList) { Console.WriteLine($"{tmegr.Id}-{tmegr.MegrendelesSzam}"); }
Gyakran alkalmazzuk a
Select
operátorban az anonim típusokat. Ezekben a típusokban mi mondjuk meg, hogy milyen nevű property-k legyenek és azok hogyan töltődjelenek fel. Bár a típus nevét nem ismerjük (anonim), de tudunk hivatkozni a property-jeire a megadott névvel.
-
Mely termékek ÁFA kulcsa 15%-os? Írjuk ki ezen termékek nevét!
-
Melyik város(ok)ba kérték a legtöbb rendelést? (Nehéz! 😱)
Tipp
Lásd a közös feladatokból a hasonlót. Egy lehetséges megoldás, ha először a megrendeléseket csoportosítjuk a kapcsolódó város neve szerint a
GroupBy
operátorral, majd vesszük minden csoport kulcsát és a csoport számosságát. Ezután ezen allekérdezésből kiindulva kell kikeresni, hogy melyek azok az allekérdezés elemek, amelyeknél nincsenek nagyobb számosságú más allekérdezés elem. -
Melyek azok a vevők, akik legalább 2-szer rendeltek már? Írjuk ki ezen vevők nevét és hogy hányszor rendeltek! (Nehéz! 😱).
Tipp
Így használhatjuk a
SelectMany
LINQ operátort egyv
vevő összes rendelésének összeszedésére:v.Telephely.SelectMany(t => t.Megrendeles)
. ASelectMany
-t akkor használjuk, ha egy kollekció minden elemhez egy kollekciót rendelünk, de az eredményt nem listák listájaként szeretnénk megkapni, hanem csak sima kilapított listaként -
Mely számláknál nem egyezik meg a kiállítás és teljesítés dátuma? Írjuk ki ezen számlák azonosítóját!
-
Írjuk ki a 2008. februári rendelések azonosítóját és ezen rendelések dátumát!
-
Írjuk ki azon rendelések azonosítóját, dátumát és határidejét, amelyeknél a határidő 5 napnál szűkebb a rendelés dátumához képest! (Nehéz! 😱, TIPP:
EF.Functions.DateDiffDay(<dátum>,<határidő>)
megadja adátum
éshatáridő
között eltelt napok számát) -
Hány vevőnek van gmail-es e-mail címe?
-
Melyik vevőknek van egynél több telephelye? Írjuk ki ezen vevők nevét és telephelyeik számát!
-
Mely vevő(k) adták le a legtöbb tételből álló rendelést? (Több ilyen is lehet!) Írjuk ki ezen vevők nevét! (Nehéz! 😱)
Tipp
Lásd a közös feladatokból a hasonlót. Egy lehetséges megoldás, ha először minden megrendeléshez kiszámoljuk a kapcsolódó vevő nevét és a megrendelés tételeinek számát. Ezután ezen allekérdezésből kiindulva kell kikeresni, hogy melyek azok az allekérdezés elemek, emelyeknél nincsenek nagyobb tételszámú más allekérdezés elem.
Az itt található oktatási segédanyagok a BMEVIAUBB04 tárgy hallgatóinak készültek. Az anyagok oly módú felhasználása, amely a tárgy oktatásához nem szorosan kapcsolódik, csak a szerző(k) és a forrás megjelölésével történhet.
Az anyagok a tárgy keretében oktatott kontextusban értelmezhetőek. Az anyagokért egyéb felhasználás esetén a szerző(k) felelősséget nem vállalnak.