SQL-lekérdezések

Az Entity Framework Core lehetővé teszi az SQL-lekérdezések legördülő menüjét a relációs adatbázis használatakor. Az SQL-lekérdezések akkor hasznosak, ha a kívánt lekérdezés nem fejezhető ki LINQ használatával, vagy ha egy LINQ-lekérdezés miatt az EF nem hatékony SQL-t hoz létre. Az SQL-lekérdezések normál entitástípusokat vagy kulcs nélküli entitástípusokat, amelyek a modell részét képezik.

Alapszintű SQL-lekérdezések

A FromSql használatával sql-lekérdezésen alapuló LINQ-lekérdezést indíthat:

var blogs = await context.Blogs
    .FromSql($"SELECT * FROM dbo.Blogs")
    .ToListAsync();

Az SQL-lekérdezések olyan tárolt eljárás végrehajtására használhatók, amely entitásadatokat ad vissza:

var blogs = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToListAsync();

Paraméterek átadása

Az alábbi példa egyetlen paramétert ad át egy tárolt eljárásnak úgy, hogy egy paraméterhelyőrzőt befoglal az SQL-lekérdezési sztringbe, és megad egy további argumentumot:

var user = "johndoe";

var blogs = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToListAsync();

Bár ez a szintaxis hasonlíthat a hagyományos C# sztring-interpolációra, a megadott értéket beágyazzák egy DbParameter-be, és a létrehozott paraméternév oda kerül, ahol a {0} helyőrzőt megadták. Ez FromSql biztonságossá teszi az SQL-injektálási támadásoktól, és hatékonyan és helyesen küldi el az értéket az adatbázisnak.

Tárolt eljárások végrehajtásakor hasznos lehet nevesített paramétereket használni az SQL-lekérdezési sztringben, különösen akkor, ha a tárolt eljárás opcionális paraméterekkel rendelkezik:

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

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

Ha több vezérlésre van szüksége az elküldött adatbázisparaméter felett, létrehozhat egy DbParameter is, és paraméterértékként is megadható. Ez lehetővé teszi a paraméter vagy az olyan aspektusok pontos adatbázistípusának beállítását, mint például a mérete, pontossága vagy hossza:

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

var blogs = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToListAsync();

Dinamikus SQL és paraméterek

A FromSql-t és a paraméterezését, amennyire csak lehet, használni kell. Vannak azonban olyan forgatókönyvek, amikor az SQL-t dinamikusan kell összeadni, és az adatbázisparaméterek nem használhatók. Tegyük fel például, hogy egy C# változó a szűrni kívánt tulajdonság nevét tartalmazza. Csábító lehet az SQL-lekérdezések használata, például a következők:

var propertyName = "User";
var propertyValue = "johndoe";

var blogs = await context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToListAsync();

Ez a kód nem működik, mivel az adatbázisok nem teszik lehetővé az oszlopnevek paraméterezését (vagy a séma bármely más részét).

Először is fontos figyelembe venni egy lekérdezés dinamikus létrehozásának következményeit – SQL-en vagy más módon. Ha elfogad egy oszlopnevet egy felhasználótól, az lehetővé teszi számukra, hogy olyan oszlopot válasszanak, amely nem indexelt, így a lekérdezés rendkívül lassan fut, és túlterheli az adatbázist; vagy lehetővé teheti számukra, hogy olyan oszlopot válasszanak, amely olyan adatokat tartalmaz, amelyeket nem szeretne felfedni. A valóban dinamikus forgatókönyvek kivételével általában jobb, ha két lekérdezést használ két oszlopnévhez, ahelyett, hogy paraméterezéssel összecsukja őket egyetlen lekérdezésbe.

Ha úgy döntött, hogy dinamikusan szeretné összeállítani az SQL-t, FromSqlRawkell használnia, amely lehetővé teszi a változóadatok közvetlenül az SQL-sztringbe való interpolálását adatbázisparaméter használata helyett:

var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");

var blogs = await context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToListAsync();

A fenti kódban az oszlop neve közvetlenül az SQL-be van beszúrva C# karakterlánc-interpolációval. Az Ön felelőssége annak biztosítása, hogy ez a sztringérték biztonságos legyen, és tisztítsa azt meg, ha nem biztonságos forrásból származik. Ez azt jelenti, hogy képes legyen észlelni az olyan speciális karaktereket, mint a pontosvesszők, megjegyzések és más SQL-szerkezetek, és ezeket vagy megfelelően elkerülni, vagy elutasítani az ilyen bemeneteket.

Másrészt az oszlopértéket egy DbParameterkeresztül küldi el a rendszer, ezért az SQL-injektálással szemben biztonságos.

LINQ-lekérdezések összeállítása

A kezdeti SQL-lekérdezés tetejére LINQ-operátorokkal komponálhat; az EF Core az SQL-t alkérdésként fogja kezelni, és az adatbázisban összerakja. Az alábbi példa egy Table-Valued függvényből (TVF) kiválasztott SQL-lekérdezést használ. Majd a LINQ használatával végzi a szűrést és a rendezést.

var searchTerm = "Lorem ipsum";

var blogs = await context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToListAsync();

A fenti lekérdezés a következő SQL-t hozza létre:

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM (
    SELECT * FROM dbo.SearchBlogs(@p0)
) AS [b]
WHERE [b].[Rating] > 3
ORDER BY [b].[Rating] DESC

Kapcsolódó adatokkal együtt

A Include operátor a kapcsolódó adatok betöltésére használható, ugyanúgy, mint bármely más LINQ-lekérdezés esetén:

var searchTerm = "Lorem ipsum";

var blogs = await context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToListAsync();

A LINQ-val való íráshoz az SQL-lekérdezés összeállítása szükséges, mivel az EF Core a megadott SQL-t allekérdezésként fogja kezelni. A összeállítható SQL-lekérdezések általában a SELECT kulcsszóval kezdődnek, és nem tartalmazhatnak olyan SQL-funkciókat, amelyek nem érvényesek egy al lekérdezésben, például:

• Záró pontosvessző
• SQL Serveren egy záró lekérdezésszintű tipp (például OPTION (HASH JOIN))
• SQL Serveren egy ORDER BY záradék, amely nem használható OFFSET 0 VAGY TOP 100 PERCENT a SELECT záradékban

Az SQL Server nem teszi lehetővé a tárolt eljáráshívások írását, ezért ha további lekérdezési operátorokat próbál alkalmazni egy ilyen hívásra, az érvénytelen SQL-t eredményez. A FromSql vagy FromSqlRaw után közvetlenül AsEnumerable vagy AsAsyncEnumerable használatával győződjön meg arról, hogy az EF Core nem próbál meg írni tárolt eljárásokat.

Változáskövetés

A FromSql vagy FromSqlRaw használó lekérdezések pontosan ugyanazokat a változáskövetési szabályokat követik, mint bármely más LINQ-lekérdezés az EF Core-ban. Ha például a lekérdezés projekt entitástípusokat ad meg, az eredmények alapértelmezés szerint nyomon lesznek követve.

Az alábbi példa egy Table-Valued függvényből (TVF) kiválasztott SQL-lekérdezést használ, majd letiltja a változáskövetést a AsNoTrackinghívásával:

var searchTerm = "Lorem ipsum";

var blogs = await context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToListAsync();

Skaláris (nem entitás) típusok lekérdezése

Bár a FromSql hasznos a modellben definiált entitások lekérdezéséhez, SqlQuery lehetővé teszi, hogy az SQL-en keresztül egyszerűen lekérdezhesse a skaláris, nem entitás típusú típusokat, anélkül, hogy alacsonyabb szintű adatelérési API-kat kellene leküldnie. A következő lekérdezés például lekéri az összes azonosítót a Blogs táblából:

var ids = await context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToListAsync();

var ids = await context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" FROM "Blogs"
                    """)
    .ToListAsync();

var ids = await context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" FROM "Blogs"
                    """)
    .ToListAsync();

LINQ-operátorokat is írhat az SQL-lekérdezésen keresztül. Azonban mivel az SQL egy részlekérdezéssé válik, amelynek kimeneti oszlopára az SQL EF, amelyet az Entity Framework ad hozzá, hivatkozást tesz, el kell neveznie a kimeneti oszlopot Value. A következő lekérdezés például az azonosító átlagát meghaladó azonosítókat adja vissza:

var overAverageIds = await context.Database
    .SqlQuery<int>($"SELECT [BlogId] AS [Value] FROM [Blogs]")
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToListAsync();

var overAverageIds = await context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" AS "Value" FROM "Blogs"
                    """)
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToListAsync();

var overAverageIds = await context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" AS "Value" FROM "Blogs"
                    """)
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToListAsync();

SqlQuery bármilyen, az adatbázis-szolgáltató által támogatott skaláris típussal használható. Ha olyan típust szeretne használni, amelyet az adatbázis-szolgáltató nem támogat, konvenciókonfiguráció előtti konfigurációs segítségével definiálhat hozzá értékkonvertálást.

SqlQueryRaw lehetővé teszi az SQL-lekérdezések dinamikus felépítését, ahogyan FromSqlRaw teszi az entitástípusok esetében.

Nem lekérdezési SQL parancsok végrehajtása

Bizonyos esetekben előfordulhat, hogy olyan SQL-t kell végrehajtani, amely nem ad vissza adatokat, általában az adatbázisban lévő adatok módosításához vagy egy tárolt eljárás meghívásához, amely nem ad vissza eredményhalmazokat. Ez a ExecuteSqlkeresztül végezhető el.

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
}

Ez végrehajtja a megadott SQL-t, és visszaadja a módosított sorok számát. ExecuteSql biztonságos paraméterezéssel véd az SQL-injektálással szemben, ahogyan FromSql, és ExecuteSqlRaw lehetővé teszi az SQL-lekérdezések dinamikus felépítését, ahogyan FromSqlRaw a lekérdezésekhez.

Korlátozások

Az entitástípusok SQL-lekérdezésekből való visszaadásakor néhány korlátozást figyelembe kell venni:

• Az SQL-lekérdezésnek adatokat kell visszaadnia az entitástípus összes tulajdonságához.
• Az eredményhalmaz oszlopneveinek meg kell egyezniük azoknak az oszlopneveknek, amelyekre a tulajdonságok megfeleltetve vannak. Vegye figyelembe, hogy ez a viselkedés eltér az EF6-tól; Az EF6 figyelmen kívül hagyta az SQL-lekérdezések tulajdonság–oszlop leképezését, és az eredményhalmaz oszlopneveinek meg kellett egyezniük a tulajdonságnevekkel.
• Az SQL-lekérdezés nem tartalmazhat kapcsolódó adatokat. Sok esetben azonban a Include operátorral kiegészítheti a lekérdezést a kapcsolódó adatok visszaadásához (lásd: Kapcsolódó adatok).