Sdílet prostřednictvím

Dotazy SQL.

Entity Framework Core umožňuje přejít k SQL dotazům 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.

Základní dotazy SQL

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

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

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 = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToListAsync();

Poznámka:

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

Předávání parametrů

Varování

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 nutné být opatrný, aby se předešlo SQL injection útokům. 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 = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToListAsync();

I když tato syntaxe může vypadat jako běžná interpolace řetězců v jazyce C#, zadaná hodnota je zabalena do DbParameter, a vygenerovaný název parametru je vložen tam, kde byl určen zástupný symbol {0}. 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 = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToListAsync();

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 = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToListAsync();

Poznámka:

Parametry, které předáte, musí přesně odpovídat definici uložené procedury. Věnujte zvláštní pozornost řazení parametrů, dbejte na to, abyste žádný z nich nevynechali nebo nezaměnili - nebo zvažte použití pojmenované notace parametrů. Také se ujistěte, že typy parametrů odpovídají a že jejich aspekty (velikost, přesnost, měřítko) jsou nastaveny 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 = await context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToListAsync();

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 = await context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToListAsync();

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á, a to buď jejím sanitizováním, pokud pochází z nebezpečného zdroje; to znamená zjišťování speciálních znaků, jako jsou středníky, komentáře a další konstrukce SQL, a buď jejich správným escapováním, nebo odmítnutím takových vstupů.

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

Varování

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 sestavit nad počátečním dotazem SQL pomocí operátorů LINQ; EF Core bude váš SQL považovat za poddotaz a sestaví na něm další operace v databázi. Následující příklad používá dotaz SQL, který vybere funkci TVF (Table-Valued Function). Potom jej komponuje pomocí LINQ ke filtrování a řazení.

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

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 = await context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToListAsync();

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
  • V SQL Serveru se používá koncová nápověda na úrovni dotazu (například OPTION (HASH JOIN))
  • Na SQL Serveru se klauzule ORDER BY, která se nepoužívá se OFFSET 0 NEBO TOP 100 PERCENT v klauzuli SELECT, nachází.

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, aby se EF Core nepokoušel skládat nad uloženou procedurou.

Change Tracking

Dotazy, které používají FromSql nebo FromSqlRaw dodržují stejná pravidla sledování změn jako jakýkoli jiný dotaz LINQ v EF Core. Například pokud dotaz projektuje typy entit, 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 = await context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToListAsync();

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

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 = await context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToListAsync();

Můžete také vytvářet operátory LINQ přes dotaz SQL. Protože se váš SQL stává poddotazem, jehož výstupní sloupec musí být odkazován SQL, které přidává EF, musíte výstupní sloupec pojmenovat Value. Následující dotaz například vrátí ID, která jsou nad průměrnou hodnotou ID.

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

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í nedotazovacích SQL

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ů.

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 sestavit nad dotazem pomocí operátoru Include pro vrácení souvisejících dat (viz Zahrnutí souvisejících dat).
  • Last updated on