Dela via

SQL-frågor

  • Artikel

Med Entity Framework Core kan du gå ner till SQL-frågor när du arbetar med en relationsdatabas. SQL-frågor är användbara om den fråga du vill ha inte kan uttryckas med LINQ, eller om en LINQ-fråga gör att EF genererar ineffektiv SQL. SQL-frågor kan returnera vanliga entitetstyper eller nyckellösa entitetstyper som ingår i din modell.

Grundläggande SQL-frågor

Du kan använda FromSql för att starta en LINQ-fråga baserat på en SQL-fråga:

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

Not

FromSql introducerades i EF Core 7.0. När du använder äldre versioner använder du FromSqlInterpolated i stället.

SQL-frågor kan användas för att köra en lagrad procedur som returnerar entitetsdata:

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

Not

FromSql kan bara användas direkt på en DbSet. Den kan inte bestå över en godtycklig LINQ-fråga.

Överföra parametrar

Varning

Var uppmärksam på parameterisering när du använder SQL-frågor

När du introducerar några användaringivna värden i en SQL-fråga måste du vara noga med att undvika SQL-inmatningsattacker. SQL-inmatning inträffar när ett program integrerar ett strängvärde som tillhandahålls av användaren i en SQL-fråga, och det användardefinierade värdet skapas för att avsluta strängen och utföra en annan skadlig SQL-åtgärd. Om du vill veta mer om SQL-inmatning kan du se den här sidan.

Metoderna FromSql och FromSqlInterpolated är säkra mot SQL-inmatning och integrerar alltid parameterdata som en separat SQL-parameter. Men metoden FromSqlRaw kan vara sårbar för SQL-inmatningsattacker om den används felaktigt. Mer information finns nedan.

I följande exempel skickas en enskild parameter till en lagrad procedur genom att inkludera en parameterplatshållare i SQL-frågesträngen och ange ytterligare ett argument:

var user = "johndoe";

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

Även om den här syntaxen kan se ut som vanlig C# stränginterpolation, omsluts det angivna värdet i en DbParameter och det genererade parameternamnet infogas där platshållaren {0} angavs. Detta gör FromSql säkra från SQL-inmatningsattacker och skickar värdet effektivt och korrekt till databasen.

När du kör lagrade procedurer kan det vara användbart att använda namngivna parametrar i SQL-frågesträngen, särskilt när den lagrade proceduren har valfria parametrar:

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

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

Om du behöver mer kontroll över databasparametern som skickas kan du även skapa en DbParameter och ange den som ett parametervärde. På så sätt kan du ange den exakta databastypen för parametern, eller fasetter, till exempel dess storlek, precision eller längd:

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

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

Notera

Parametrarna som du skickar måste exakt matcha definitionen för lagrad procedur. Var särskilt uppmärksam på ordningen på parametrarna, var noga med att inte missa eller felplaceera någon av dem - eller överväg att använda namngiven parameter notation. Kontrollera också att parametertyperna motsvarar och att deras fasetter (storlek, precision, skala) anges efter behov.

Dynamisk SQL och parametrar

FromSql och dess parameterisering bör användas när det är möjligt. Det finns dock vissa scenarier där SQL måste delas dynamiskt och databasparametrar inte kan användas. Anta till exempel att en C#-variabel innehåller namnet på en egenskap som ska filtreras efter. Det kan vara frestande att använda en SQL-fråga, till exempel följande:

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

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

Den här koden fungerar inte eftersom databaser inte tillåter parameterisering av kolumnnamn (eller någon annan del av schemat).

För det första är det viktigt att överväga konsekvenserna av att dynamiskt konstruera en fråga – via SQL eller på annat sätt. Om du accepterar ett kolumnnamn från en användare kan de välja en kolumn som inte är indexerad, vilket gör att frågan körs extremt långsamt och överbelastar databasen. eller så kan de välja en kolumn som innehåller data som du inte vill ska exponeras. Förutom verkligt dynamiska scenarier är det vanligtvis bättre att ha två frågor för två kolumnnamn, i stället för att använda parameterisering för att komprimera dem till en enda fråga.

Om du har bestämt dig för att skapa SQL dynamiskt måste du använda FromSqlRaw, som gör det möjligt att interpolera variabeldata direkt i SQL-strängen i stället för att använda en databasparameter:

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

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

I koden ovan infogas kolumnnamnet direkt i SQL med hjälp av C#-stränginterpolation. Det är ditt ansvar att se till att strängvärdet är säkert och att rensa det om det kommer från ett osäkert ursprung. Det innebär att du identifierar specialtecken som semikolon, kommentarer och andra SQL-konstruktioner och antingen tar bort dem på rätt sätt eller avvisar sådana indata.

Å andra sidan skickas kolumnvärdet via en DbParameteroch är därför säker inför SQL-inmatningen.

Varning

Var mycket försiktig när du använder FromSqlRawoch se alltid till att värdena antingen kommer från ett säkert ursprung eller är korrekt sanerade. SQL-inmatningsattacker kan få katastrofala konsekvenser för ditt program.

Skapa med LINQ

Du kan skriva ovanpå den första SQL-frågan med linq-operatorer. EF Core behandlar din SQL som en underfråga och skriver över den i databasen. I följande exempel används en SQL-fråga som väljer från en Table-Valued Funktion (TVF). Och sedan bygger vidare på det med LINQ för filtrering och sortering.

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

Ovanstående fråga genererar följande 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

Operatorn Include kan användas för att läsa in relaterade data, precis som med andra LINQ-frågor:

var searchTerm = "Lorem ipsum";

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

För att skapa med LINQ måste SQL-frågan vara skrivbar, eftersom EF Core behandlar den angivna SQL som en underfråga. Sammansättningsbara SQL-frågor börjar vanligtvis med nyckelordet SELECT och får inte innehålla SQL-funktioner som inte är giltiga i en underfråga, till exempel:

  • Ett avslutande semikolon
  • På SQL Server, ett avslutande tips på frågenivå (till exempel OPTION (HASH JOIN))
  • På SQL Server används en ORDER BY-sats som inte används med OFFSET 0 ELLER TOP 100 PERCENT i SELECT-satsen

SQL Server tillåter inte sammansättning över lagrade proceduranrop, så alla försök att tillämpa ytterligare frågeoperatorer på ett sådant anrop resulterar i ogiltig SQL. Använd AsEnumerable eller AsAsyncEnumerable direkt efter FromSql eller FromSqlRaw för att se till att EF Core inte försöker skriva över en lagrad procedur.

Ändringsspårning

Frågor som använder FromSql eller FromSqlRaw följa exakt samma regler för ändringsspårning som andra LINQ-frågor i EF Core. Om frågan handlar om entitetstyper, spåras resultaten som standard.

I följande exempel används en SQL-fråga som väljer från en Table-Valued Function (TVF) och inaktiverar sedan ändringsspårning med anropet till AsNoTracking:

var searchTerm = "Lorem ipsum";

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

Fråga efter skalära typer (icke-entiteter)

Not

Den här funktionen introducerades i EF Core 7.0.

Även om FromSql är användbart för att köra frågor mot entiteter som definierats i din modell kan du SqlQuery enkelt fråga efter skalära typer som inte är entitetstyper via SQL, utan att behöva gå ned till API:er för dataåtkomst på lägre nivå. Följande fråga hämtar till exempel alla ID:t från tabellen Blogs:

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

Du kan också skapa LINQ-operatorer i SQL-frågan. Men eftersom din SQL blir en underfråga vars utdatakolumn måste refereras av den SQL som EF lägger till, måste du namnge utdatakolumnen Value. Följande fråga returnerar till exempel de ID:ar som ligger över ID-genomsnittet:

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

SqlQuery kan användas med alla skalära typer som stöds av databasprovidern. Om du vill använda en typ som inte stöds av databasprovidern kan du använda konfiguration före konventionen för att definiera en värdekonvertering för den.

SqlQueryRaw möjliggör dynamisk konstruktion av SQL-frågor, precis som FromSqlRaw gör för entitetstyper.

Kör icke-frågande SQL-kommandon

I vissa scenarier kan det vara nödvändigt att köra SQL som inte returnerar några data, vanligtvis för att ändra data i databasen eller anropa en lagrad procedur som inte returnerar några resultatuppsättningar. Detta kan göras via ExecuteSql:

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

Detta kör den angivna SQL-filen och returnerar antalet rader som ändrats. ExecuteSql skyddar mot SQL-inmatning med hjälp av säker parameterisering, precis som FromSql, och ExecuteSqlRaw möjliggör dynamisk konstruktion av SQL-frågor, precis som FromSqlRaw gör för frågor.

Not

Före EF Core 7.0 var det ibland nödvändigt att använda ExecuteSql API:er för att utföra en "massuppdatering" på databasen, enligt ovan. Detta är betydligt effektivare än att fråga efter alla matchande rader och sedan använda SaveChanges för att ändra dem. EF Core 7.0 introducerade ExecuteUpdate och ExecuteDelete, vilket gjorde det möjligt att uttrycka effektiva massuppdateringsåtgärder via LINQ. Vi rekommenderar att du använder dessa API:er när det är möjligt i stället för ExecuteSql.

Begränsningar

Det finns några begränsningar att känna till när du returnerar entitetstyper från SQL-frågor:

  • SQL-frågan måste returnera data för alla egenskaper av entitetstypen.
  • Kolumnnamnen i resultatuppsättningen måste matcha kolumnnamnen som egenskaperna mappas till. Observera att det här beteendet skiljer sig från EF6. EF6 ignorerade egenskaps-till-kolumn-mappning för SQL-frågor, och kolumnnamn för resultatuppsättningar måste matcha dessa egenskapsnamn.
  • SQL-frågan får inte innehålla relaterade data. I många fall kan du dock skriva ovanpå frågan med operatorn Include för att returnera relaterade data (se Inklusive relaterade data).