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 visszaadhatnak normál entitástípusokat vagy olyan 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();

Jegyzet

FromSql az EF Core 7.0-ban vezették be. A régebbi verziók használatakor használja inkább a FromSqlInterpolated.

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();

Jegyzet

FromSql csak közvetlenül egy DbSet-en használható. Nem állítható össze tetszőleges LINQ-lekérdezésen keresztül.

Paraméterek átadása

Figyelmeztetés

Az SQL-lekérdezések használatakor ügyeljen a paraméterezésre

A felhasználó által megadott értékek SQL-lekérdezésbe való bevezetésekor ügyelni kell az SQL-injektálási támadások elkerülésére. Az SQL-injektálás akkor fordul elő, ha egy program egy felhasználó által megadott sztringértéket integrál egy SQL-lekérdezésbe, és a felhasználó által megadott érték a sztring leállítására és egy másik rosszindulatú SQL-művelet végrehajtására készül. További információ az SQL-injektálásról ezen az oldalon található.

A FromSql és FromSqlInterpolated metódusok biztonságosak az SQL-injektálás ellen, és mindig integrálják a paraméteradatokat külön SQL-paraméterként. A FromSqlRaw módszer azonban sebezhető lehet az SQL-injektálási támadásokkal szemben, ha helytelenül használják. További részletekért lásd alább.

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();

Jegyzet

Az átadott paramétereknek pontosan meg kell egyeznie a tárolt eljárásdefinícióval. Különös figyelmet kell fordítani a paraméterek sorrendjére, ügyelve arra, hogy ne hagyja ki vagy ne tévesszen meg egyiket sem – vagy fontolja meg a névvel ellátott paraméter jelölésének használatát. Győződjön meg arról is, hogy a paramétertípusok megfelelnek, és hogy az aspektusaik (méret, pontosság, skálázás) igény szerint vannak beállítva.

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.

Figyelmeztetés

Legyen nagyon óvatos a FromSqlRawhasználatakor, és mindig győződjön meg arról, hogy az értékek biztonságos forrásból származnak, vagy megfelelően vannak megtisztítva. Az SQL-injektálási támadások katasztrofális következményekkel járhatnak az alkalmazásra nézve.

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

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 AsEnumerable vagy AsAsyncEnumerable után közvetlenül FromSql vagy FromSqlRaw 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ások követése

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();

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();

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.

SqlQuery nem korlátozódik a skaláris típusokra. Az eredményhalmaz minden sorát tetszőleges CLR-típusba is kivetítheti, amely nem része az EF-modellnek, feltéve, hogy a típus rendelkezik az eredményhalmaz minden oszlopának tulajdonságával. A további részletekért lásd alább a Leképezetlen típusok lekérdezése című részt.

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

Leképezetlen típusok lekérdezése

Jegyzet

A leképezetlen típusok SqlQuery általi visszaadását az EF Core 8.0-ban vezették be.

A skaláris típusokon kívül a SqlQuery képes egy leképezhető CLR-típus példányait is feltölteni egy SQL-lekérdezés eredményhalmazából, anélkül, hogy ezt a típust felvenné az EF-modellbe. A lekérdezési eredményekhez használt típus tartalmazhat az EF Core által támogatott gyakori leképezési szerkezeteket, például paraméteres konstruktorokat és leképezési attribútumokat. A használt típusnak rendelkeznie kell egy tulajdonságot az eredményhalmaz minden értékéhez, de nem kell egyeznie az adatbázis egyik táblájának sem. Például a következő típust adva:

public class BlogPost
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public DateOnly PublishedOn { get; set; }
    public int BlogId { get; set; }
}

A típus ugyanúgy kérdezhető le SqlQuery , mint a skaláris típusok:

var start = new DateOnly(2022, 1, 1);
var end = new DateOnly(2023, 1, 1);
var posts = await context.Database
    .SqlQuery<BlogPost>($"SELECT * FROM Posts AS p WHERE p.PublishedOn >= {start} AND p.PublishedOn < {end}")
    .ToListAsync();

A használt típusnak rendelkeznie kell egy tulajdonságpal az eredményhalmaz minden értékéhez, de az adatbázis egyik táblájának sem kell egyeznie – például az oszlopok egy részhalmazát, vagy több tábla összekapcsolásának eredményét.

Jegyzet

Az ily módon használt le nem képezett típusok nem rendelkeznek kulcsokkal, és nem lehetnek kapcsolataik más típusokkal. A kapcsolatokkal rendelkező típusokat le kell képezni a modellben.

Leképezetlen összetett típusok

A visszaadott SqlQuery megfeleltetetlen típus beágyazott összetett típusokat is tartalmazhat, így az eredményhalmazból több oszlopot is csoportosíthat beágyazott értékobjektumba. Vegyük például egy Address összetett típust, amely egy Customer eredménytípusba van ágyazva:

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Address Address { get; set; }
}

[ComplexType]
public class Address
{
    public string Line1 { get; set; }
    public string City { get; set; }
    public string PostCode { get; set; }
    public string Country { get; set; }
}

A beágyazott összetett típusú tulajdonságok az eredményhalmazból ugyanazok az oszlopnév-konvenciók használatával vannak feltöltve, amelyeket a modell összetett típusainak leképezésekor használnak, ahol minden beágyazott tulajdonság egy összetett tulajdonságnévvel és aláhúzásjellel összekapcsolt beágyazott tulajdonságnévvel ellátott oszlopra van leképezve (például Address_City):

var customers = await context.Database
    .SqlQuery<Customer>(
        $"""
         SELECT [Id], [Name], [Address_Line1], [Address_City], [Address_PostCode], [Address_Country]
         FROM [Customers]
         """)
    .ToListAsync();

A modell összetett típusaihoz hasonlóan a beágyazott típusnak nincs kulcsa, és nem definiálhat más típusokkal való kapcsolatokat.

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).
  • Last updated on