Delen via

SQL-queries

  • Artikel

Met Entity Framework Core kunt u vervolgkeuzelijsten uitvoeren voor SQL-query's wanneer u met een relationele database werkt. SQL-query's zijn handig als de gewenste query niet kan worden uitgedrukt met LINQ of als een LINQ-query EF inefficiënte SQL genereert. SQL-query's kunnen reguliere entiteitstypen of sleutelloze entiteitstypen retourneren die deel uitmaken van uw model.

Eenvoudige SQL-query's

U kunt FromSql gebruiken om een LINQ-query te starten op basis van een SQL-query:

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

Notitie

FromSql werd geïntroduceerd in EF Core 7.0. Wanneer u oudere versies gebruikt, gebruikt u in plaats daarvan FromSqlInterpolated.

SQL-query's kunnen worden gebruikt om een opgeslagen procedure uit te voeren die entiteitsgegevens retourneert:

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

Notitie

FromSql kan alleen rechtstreeks op een DbSetworden gebruikt. Deze kan niet worden samengesteld via een willekeurige LINQ-query.

Parameters doorgeven

Waarschuwing

Let goed op parameters bij het gebruik van SQL-query's

Wanneer u door de gebruiker verstrekte waarden in een SQL-query introduceert, moet u ervoor zorgen dat sql-injectieaanvallen worden voorkomen. SQL-injectie treedt op wanneer een programma een door de gebruiker verstrekte tekenreekswaarde integreert in een SQL-query en de door de gebruiker opgegeven waarde is gemaakt om de tekenreeks te beëindigen en een andere schadelijke SQL-bewerking uit te voeren. Zie deze pagina voor meer informatie over SQL-injectie.

De methoden FromSql en FromSqlInterpolated zijn veilig tegen SQL-injectie en integreren altijd parametergegevens als een afzonderlijke SQL-parameter. De methode FromSqlRaw kan echter kwetsbaar zijn voor SQL-injectieaanvallen, indien onjuist gebruikt. Zie hieronder voor meer informatie.

In het volgende voorbeeld wordt één parameter doorgegeven aan een opgeslagen procedure door een tijdelijke aanduiding voor parameters op te slaan in de SQL-queryreeks en een extra argument op te geven:

var user = "johndoe";

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

Hoewel deze syntaxis eruit kan zien als reguliere C# tekenreeksinterpolatie, wordt de opgegeven waarde in een DbParameter verpakt en wordt de gegenereerde parameternaam ingevoegd waar de {0} tijdelijke aanduiding was gespecificeerd. Hierdoor is FromSql veilig tegen SQL-injectieaanvallen en wordt de waarde efficiënt en correct naar de database verzonden.

Bij het uitvoeren van opgeslagen procedures kan het handig zijn om benoemde parameters in de SQL-queryreeks te gebruiken, met name wanneer de opgeslagen procedure optionele parameters heeft:

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

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

Als u meer controle nodig hebt over de databaseparameter die wordt verzonden, kunt u ook een DbParameter maken en deze opgeven als parameterwaarde. Hiermee kunt u het exacte databasetype van de parameter of facetten instellen, zoals de grootte, precisie of lengte:

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

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

Notitie

De parameters die u doorgeeft, moeten exact overeenkomen met de definitie van de opgeslagen procedure. Let vooral op de volgorde van de parameters, waarbij u ervoor zorgt dat u geen van deze parameters mist of misplaatst - of overweeg het gebruik van benoemde parameter notatie. Zorg er ook voor dat de parametertypen overeenkomen en dat de facetten (grootte, precisie, schaal) zo nodig zijn ingesteld.

Dynamische SQL en parameters

FromSql en de parameterisatie moeten waar mogelijk worden gebruikt. Er zijn echter bepaalde scenario's waarin SQL dynamisch moet worden samengevoegd en databaseparameters niet kunnen worden gebruikt. Stel dat een C#-variabele de naam bevat van de eigenschap waarop moet worden gefilterd. Het kan verleidelijk zijn om een SQL-query te gebruiken, zoals de volgende:

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

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

Deze code werkt niet, omdat databases het parameteriseren van kolomnamen (of een ander deel van het schema) niet toestaan.

Ten eerste is het belangrijk om rekening te houden met de gevolgen van het dynamisch samenstellen van een query, via SQL of anderszins. Door een kolomnaam van een gebruiker te accepteren, kunnen ze een kolom kiezen die niet is geïndexeerd, waardoor de query extreem langzaam wordt uitgevoerd en uw database overbelast raakt; of hiermee kunnen ze een kolom kiezen die gegevens bevat die u niet wilt weergeven. Behalve voor werkelijk dynamische scenario's is het meestal beter om twee query's voor twee kolomnamen te hebben in plaats van parameterisatie te gebruiken om ze samen te vouwen in één query.

Als u hebt besloten dat u uw SQL dynamisch wilt samenstellen, moet u FromSqlRawgebruiken, waardoor variabele gegevens rechtstreeks in de SQL-tekenreeks kunnen worden geïnterpoleerd in plaats van een databaseparameter te gebruiken:

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

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

In de bovenstaande code wordt de kolomnaam rechtstreeks in de SQL ingevoegd met behulp van C#-tekenreeksinterpolatie. Het is uw verantwoordelijkheid om ervoor te zorgen dat deze waarde veilig is en om de waarde op te schonen als deze afkomstig is van een onveilige oorsprong. Dit betekent dat u speciale tekens moet detecteren, zoals puntkomma's, opmerkingen en andere SQL-constructies, en deze correct moet ontsnappen of dergelijke invoer moet weigeren.

Aan de andere kant wordt de kolomwaarde verzonden via een DbParameteren is daarom veilig tegen SQL-injectie.

Waarschuwing

Wees zeer voorzichtig bij het gebruik van FromSqlRawen zorg er altijd voor dat waarden afkomstig zijn van een veilige oorsprong of goed zijn opgeschoond. SQL-injectieaanvallen kunnen rampzalige gevolgen hebben voor uw toepassing.

Opstellen met LINQ

U kunt boven op de eerste SQL-query opstellen met behulp van LINQ-operators; EF Core behandelt uw SQL als subquery en stelt deze samen in de database. In het volgende voorbeeld wordt een SQL-query gebruikt die een Table-Valued-functie (TVF) selecteert. En vervolgens maakt u er een op met LINQ om filters en sorteringen uit te voeren.

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

Met de bovenstaande query wordt de volgende SQL gegenereerd:

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

De operator Include kan worden gebruikt om gerelateerde gegevens te laden, net als bij elke andere LINQ-query:

var searchTerm = "Lorem ipsum";

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

Voor het opstellen met LINQ moet uw SQL-query samenstelbaar zijn, omdat EF Core de opgegeven SQL als subquery behandelt. Samenstelbare SQL-query's beginnen meestal met het trefwoord SELECT en kunnen geen SQL-functies bevatten die niet geldig zijn in een subquery, zoals:

  • Een volgkomma
  • Op SQL Server, een achteraanstaande hint op queryniveau (bijvoorbeeld OPTION (HASH JOIN))
  • In SQL Server, een ORDER BY-clausule die niet wordt gebruikt met OFFSET 0 OF TOP 100 PERCENT in de SELECT-clausule

SQL Server staat het opstellen van opgeslagen procedure-aanroepen niet toe, dus elke poging om extra queryoperators toe te passen op een dergelijke aanroep, resulteert in ongeldige SQL. Gebruik AsEnumerable of AsAsyncEnumerable direct na FromSql of FromSqlRaw om ervoor te zorgen dat EF Core niet probeert op te stellen via een opgeslagen procedure.

Wijzigingen bijhouden

Query's die gebruikmaken van FromSql of FromSqlRaw voldoen aan exact dezelfde regels voor het bijhouden van wijzigingen als andere LINQ-query's in EF Core. Als het zoekopdrachten projecteert op entiteitstypen, worden de resultaten standaard bijgehouden.

In het volgende voorbeeld wordt een SQL-query gebruikt die uit een Table-Valued Functie (TVF) wordt geselecteerd en vervolgens het bijhouden van wijzigingen wordt uitgeschakeld met de aanroep naar AsNoTracking:

var searchTerm = "Lorem ipsum";

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

Scalaire typen (niet-entiteitstypen) opvragen

Notitie

Deze functie is geïntroduceerd in EF Core 7.0.

Hoewel FromSql handig is voor het uitvoeren van query's op entiteiten die in uw model zijn gedefinieerd, kunt u met SqlQuery eenvoudig query's uitvoeren op scalaire, niet-entiteitstypen via SQL, zonder dat u hoeft af te vervolgkeuzelijsten naar API's voor gegevenstoegang op lager niveau. Met de volgende query worden bijvoorbeeld alle id's uit de Blogs tabel opgehaald:

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

U kunt ook LINQ-operators opstellen voor uw SQL-query. Omdat uw SQL echter een subquery wordt waarvan naar de uitvoerkolom moet worden verwezen door de SQL EF-adds, moet u de uitvoerkolom een naam geven Value. Met de volgende query worden bijvoorbeeld de id's geretourneerd die boven het id-gemiddelde liggen:

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 worden gebruikt met elk scalair type dat wordt ondersteund door uw databaseprovider. Als u een type wilt gebruiken dat niet wordt ondersteund door uw databaseprovider, kunt u vooraf conventieconfiguratie gebruiken om een waardeconversie te definiëren.

SqlQueryRaw maakt dynamische constructie van SQL-query's mogelijk, net zoals FromSqlRaw doet voor entiteitstypen.

Niet-query SQL uitvoeren

In sommige scenario's kan het nodig zijn om SQL uit te voeren die geen gegevens retourneert, meestal voor het wijzigen van gegevens in de database of het aanroepen van een opgeslagen procedure die geen resultatensets retourneert. Dit kan gedaan worden via ExecuteSql:

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

Hiermee wordt de opgegeven SQL uitgevoerd en wordt het aantal gewijzigde rijen geretourneerd. ExecuteSql beschermt tegen SQL-injectie door gebruik te maken van veilige parameters, net als FromSql, en ExecuteSqlRaw maakt dynamische constructie van SQL-query's mogelijk, net zoals FromSqlRaw doet voor query's.

Notitie

Vóór EF Core 7.0 was het soms nodig om de ExecuteSql API's te gebruiken om een 'bulkupdate' uit te voeren op de database, zoals hierboven; dit is aanzienlijk efficiënter dan het uitvoeren van query's voor alle overeenkomende rijen en vervolgens het gebruik van SaveChanges om ze te wijzigen. EF Core 7.0 heeft ExecuteUpdate en ExecuteDeletegeïntroduceerd, waardoor efficiënte bulkupdatebewerkingen via LINQ kunnen worden uitgedrukt. Het is raadzaam om deze API's waar mogelijk te gebruiken in plaats van ExecuteSql.

Beperkingen

Er zijn enkele beperkingen waar u rekening mee moet houden bij het retourneren van entiteitstypen uit SQL-query's:

  • De SQL-query moet gegevens retourneren voor alle eigenschappen van het entiteitstype.
  • De kolomnamen in de resultatenset moeten overeenkomen met de kolomnamen waaraan eigenschappen zijn toegewezen. Houd er rekening mee dat dit gedrag verschilt van EF6; EF6 negeerde eigenschap-naar-kolomtoewijzing voor SQL-query's en kolomnamen van resultatensets moesten overeenkomen met die eigenschapsnamen.
  • De SQL-query kan geen gerelateerde gegevens bevatten. In veel gevallen kunt u echter boven op de query opstellen met behulp van de operator Include om gerelateerde gegevens te retourneren (zie Inclusief gerelateerde gegevens).