Dotazy SQL.

Článek
09/12/2023

Entity Framework Core umožňuje rozevírací seznam dotazů SQL při práci s relační databází. Dotazy SQL jsou užitečné, pokud požadovaný dotaz nelze vyjádřit pomocí LINQ nebo pokud dotaz LINQ způsobí, že EF vygeneruje neefektivní SQL. Dotazy SQL můžou vracet běžné typy entit nebo typy entit bez klíčů, které jsou součástí vašeho modelu.

Tip

Ukázku pro tento článek najdete na GitHubu.

Základní dotazy SQL

Můžete použít FromSql k zahájení dotazu LINQ na základě dotazu SQL:

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

Poznámka:

FromSql byla zavedena v EF Core 7.0. Pokud používáte starší verze, použijte FromSqlInterpolated místo toho.

Dotazy SQL je možné použít ke spuštění uložené procedury, která vrací data entity:

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

Poznámka:

FromSqllze použít pouze přímo na .DbSet Nelze ji sestavit pomocí libovolného dotazu LINQ.

Předávání parametrů

Upozorňující

Při používání dotazů SQL věnujte velkou pozornost parametrizaci.

Při zavádění jakýchkoli hodnot zadaných uživatelem do dotazu SQL je potřeba věnovat pozornost útokům prostřednictvím injektáže SQL. Injektáž SQL nastane, když program integruje uživatelem poskytnutou řetězcovou hodnotu do dotazu SQL a hodnota zadaná uživatelem se vytvoří tak, aby ukončil řetězec a provedl další škodlivou operaci SQL. Další informace o injektáži SQL najdete na této stránce.

Tyto FromSql metody FromSqlInterpolated jsou bezpečné proti injektáži SQL a vždy integrují data parametrů jako samostatný parametr SQL. Metoda FromSqlRaw však může být ohrožena útoky prostřednictvím injektáže SQL, pokud se nesprávně používá. Další informace naleznete níže.

Následující příklad předá jeden parametr uložené proceduře zahrnutím zástupného symbolu parametru do řetězce dotazu SQL a poskytnutím dalšího argumentu:

var user = "johndoe";

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

I když tato syntaxe může vypadat jako běžná interpolace řetězců jazyka C#, zadaná hodnota se zabalí do DbParameter vygenerovaného názvu parametru, do kterého {0} byl zadaný zástupný symbol. To zajišťuje FromSql bezpečí před útoky prostřednictvím injektáže SQL a efektivně a správně odesílá hodnotu do databáze.

Při spouštění uložených procedur může být užitečné použít pojmenované parametry v řetězci dotazu SQL, zejména pokud uložená procedura obsahuje volitelné parametry:

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

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

Pokud potřebujete větší kontrolu nad odesílanou parametrem databáze, můžete také sestavit DbParameter a zadat ji jako hodnotu parametru. To vám umožní nastavit přesný typ databáze parametru nebo omezující vlastnosti, jako je jeho velikost, přesnost nebo délka:

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

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

Poznámka:

Parametry, které předáte, musí přesně odpovídat definici uložené procedury. Věnujte zvláštní pozornost řazeníparametrůch Také se ujistěte, že typy parametrů odpovídají a že jejich omezující vlastnosti (velikost, přesnost, měřítko) jsou nastavené podle potřeby.

Dynamické sql a parametry

FromSql a jeho parametrizace by se měla používat všude, kde je to možné. Existují však určité scénáře, kdy sql musí být dynamicky rozčleněný dohromady a parametry databáze nelze použít. Předpokládejme například, že proměnná jazyka C# obsahuje název vlastnosti, podle které se má filtrovat. Může být lákavé použít dotaz SQL, například následující:

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

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

Tento kód nefunguje, protože databáze neumožňují parametrizaci názvů sloupců (ani žádné jiné části schématu).

Nejprve je důležité zvážit důsledky dynamického vytváření dotazu – prostřednictvím SQL nebo jinak. Přijetí názvu sloupce od uživatele mu může umožnit zvolit sloupec, který není indexovaný, takže dotaz běží velmi pomalu a přetíží vaši databázi; nebo jim může umožnit zvolit sloupec obsahující data, která nechcete vystavit. S výjimkou skutečně dynamických scénářů je obvykle lepší mít dva dotazy na názvy dvou sloupců, a ne pomocí parametrizace je sbalit do jednoho dotazu.

Pokud jste se rozhodli, že chcete dynamicky sestavit SQL, budete muset použít FromSqlRaw, což umožňuje interpolaci dat proměnných přímo do řetězce SQL místo použití parametru databáze:

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

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

Ve výše uvedeném kódu se název sloupce vloží přímo do SQL pomocí interpolace řetězců jazyka C#. Je vaší zodpovědností zajistit, aby tato řetězcová hodnota byla bezpečná, sanitizovat ji, pokud pochází z nebezpečného původu; to znamená, že zjistíte speciální znaky, jako jsou středníky, komentáře a další konstrukce SQL, a buď je správně zapouzdřují, nebo tyto vstupy odmítnou.

Na druhé straně je hodnota sloupce odeslána prostřednictvím DbParameter, a proto je v bezpečí v případě injektáže SQL.

Upozorňující

Buďte při použití FromSqlRawvelmi opatrní a vždy se ujistěte, že hodnoty jsou buď z bezpečného původu, nebo jsou správně sanitizovány. Útoky prostřednictvím injektáže SQL můžou mít katastrofální důsledky pro vaši aplikaci.

Psaní pomocí LINQ

Můžete vytvořit nad počátečním dotazem SQL pomocí operátorů LINQ; EF Core bude váš SQL považovat za poddotaz a vytvoří ho v databázi. Následující příklad používá dotaz SQL, který vybere funkci TVF (Table-Valued Function). Potom ho sestaví pomocí LINQ k filtrování a řazení.

var searchTerm = "Lorem ipsum";

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

Výše uvedený dotaz vygeneruje následující SQL:

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

Operátor Include se dá použít k načtení souvisejících dat stejně jako u jakéhokoli jiného dotazu LINQ:

var searchTerm = "Lorem ipsum";

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

Psaní pomocí LINQ vyžaduje, aby byl váš dotaz SQL kompozovatelný, protože EF Core bude zacházet se zadaným SQL jako poddotazem. Kompozovatelné dotazy SQL obvykle začínají klíčovým slovem SELECT a nemohou obsahovat funkce SQL, které nejsou platné v poddotadu, například:

Koncový středník
Na SQL Serveru je koncový tip na úrovni dotazu (například OPTION (HASH JOIN))
Na SQL Serveru se klauzule, ORDER BY která se v klauzuli SELECT OR TOP 100 PERCENT nepoužívá OFFSET 0

SQL Server neumožňuje vytvářet volání uložených procedur, takže jakýkoli pokus o použití dalších operátorů dotazu na takové volání způsobí neplatný SQL. Použijte AsEnumerable nebo AsAsyncEnumerable hned po FromSql nebo FromSqlRaw ujistěte se, že se EF Core nepokouší vytvořit přes uloženou proceduru.

Sledování změn

Dotazy, které používají FromSql nebo FromSqlRaw dodržují stejná pravidla sledování změn jako jakýkoli jiný dotaz LINQ v EF Core. Pokud jsou například typy entit projektů dotazu, výsledky se ve výchozím nastavení sledují.

Následující příklad používá dotaz SQL, který vybere funkci Table-Valued (TVF), a pak zakáže sledování změn pomocí volání AsNoTracking:

var searchTerm = "Lorem ipsum";

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

Dotazování skalárních typů (bez entity)

Poznámka:

Tato funkce byla představena v EF Core 7.0.

I když FromSql je užitečné pro dotazování entit definovaných v modelu, SqlQuery umožňuje snadno dotazovat se na skalární typy, které nejsou typy entit prostřednictvím SQL, aniž byste museli převést na rozhraní API pro přístup k datům nižší úrovně. Například následující dotaz načte všechna ID z Blogs tabulky:

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

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

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

Můžete také vytvářet operátory LINQ přes dotaz SQL. Vzhledem k tomu, že sql se stane poddotazem, na který musí odkazovat výstupní sloupec, přidá ef SQL, musíte pojmenovat výstupní sloupec Value. Následující dotaz například vrátí ID nad průměrem ID:

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

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

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

SqlQuery lze použít s libovolným skalárním typem podporovaným vaším poskytovatelem databáze. Pokud chcete použít typ, který váš poskytovatel databáze nepodporuje, můžete pro něj definovat převod hodnoty pomocí konfigurace před konvencí.

SqlQueryRaw umožňuje dynamické vytváření dotazů SQL, stejně jako FromSqlRaw u typů entit.

Provádění dotazů sql bez dotazování

V některých scénářích může být nutné spustit SQL, který nevrací žádná data, obvykle pro úpravu dat v databázi nebo volání uložené procedury, která nevrací žádné sady výsledků. To lze provést prostřednictvím ExecuteSql:

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

Tím se spustí zadaný sql a vrátí počet upravených řádků. ExecuteSql chrání před injektáží SQL pomocí bezpečné parametrizace, stejně jako FromSqla ExecuteSqlRaw umožňuje dynamické vytváření dotazů SQL, stejně jako FromSqlRaw u dotazů.

Poznámka:

Před EF Core 7.0 bylo někdy nutné použít ExecuteSql rozhraní API k provedení "hromadné aktualizace" v databázi, jak je uvedeno výše. To je výrazně efektivnější než dotazování na všechny odpovídající řádky a následné použití SaveChanges k jejich úpravě. EF Core 7.0 zavedl ExecuteUpdate a ExecuteDelete, což umožnilo vyjádřit efektivní operace hromadné aktualizace prostřednictvím LINQ. Tato rozhraní API doporučujeme používat, kdykoli je to možné, místo ExecuteSql.

Omezení

Při vracení typů entit z dotazů SQL je potřeba mít na paměti několik omezení:

Dotaz SQL musí vracet data pro všechny vlastnosti typu entity.
Názvy sloupců v sadě výsledků musí odpovídat názvům sloupců, na které jsou vlastnosti namapovány. Všimněte si, že toto chování se liší od EF6; EF6 ignorovalo mapování vlastností na sloupec pro dotazy SQL a názvy sloupců sady výsledků musely odpovídat těmto názvům vlastností.
Dotaz SQL nemůže obsahovat související data. V mnoha případech ale můžete vytvořit nad dotazem pomocí operátoru Include vrácení souvisejících dat (viz Zahrnutí souvisejících dat).

Sdílet prostřednictvím

Dotazy SQL.

Základní dotazy SQL

Předávání parametrů

Dynamické sql a parametry

Psaní pomocí LINQ

Sledování změn

Dotazování skalárních typů (bez entity)

Provádění dotazů sql bez dotazování

Omezení

Další materiály

Sdílet prostřednictvím

Dotazy SQL.

Základní dotazy SQL

Předávání parametrů

Dynamické sql a parametry

Psaní pomocí LINQ

Zahrnutí souvisejících dat

Sledování změn

Dotazování skalárních typů (bez entity)

Provádění dotazů sql bez dotazování

Omezení

Další materiály