Dotazy SQL.

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.

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

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

Předávání parametrů

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

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.

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

Zahrnutí souvisejících dat

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)

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

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

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