Query SQL

  • Articolo

Entity Framework Core consente di eseguire l'elenco a discesa delle query SQL quando si usa un database relazionale. Le query SQL sono utili se la query desiderata non può essere espressa tramite LINQ o se una query LINQ causa la generazione inefficiente di SQL da parte di EF. Le query SQL possono restituire tipi di entità regolari o tipi di entità senza chiave che fanno parte del modello.

Suggerimento

È possibile visualizzare l'esempio di questo articolo in GitHub.

Query SQL di base

È possibile usare FromSql per avviare una query LINQ basata su una query SQL:

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

Nota

FromSql è stato introdotto in EF Core 7.0. Quando si usano versioni precedenti, usare FromSqlInterpolated invece .

Le query SQL possono essere usate per eseguire una stored procedure che restituisce i dati dell'entità:

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

Nota

FromSql può essere usato solo direttamente in un oggetto DbSet. Non può essere composto su una query LINQ arbitraria.

Passaggio dei parametri

Avviso

Prestare particolare attenzione alla parametrizzazione quando si usano query SQL

Quando si introducono valori forniti dall'utente in una query SQL, è necessario prestare attenzione a evitare attacchi SQL injection. SQL injection si verifica quando un programma integra un valore stringa fornito dall'utente in una query SQL e il valore fornito dall'utente viene creato per terminare la stringa ed eseguire un'altra operazione SQL dannosa. Per altre informazioni sull'inserimento di SQL, vedere questa pagina.

I FromSql metodi e FromSqlInterpolated sono sicuri da SQL injection e integrano sempre i dati dei parametri come parametro SQL separato. Tuttavia, il FromSqlRaw metodo può essere vulnerabile agli attacchi SQL injection, se usati in modo non corretto. Per ulteriori dettagli, vedi la sezione seguente.

Nell'esempio seguente viene passato un singolo parametro a una stored procedure includendo un segnaposto di parametro nella stringa di query SQL e fornendo un argomento aggiuntivo:

var user = "johndoe";

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

Anche se questa sintassi può essere simile all'interpolazione regolare di stringhe C#, il valore fornito viene incluso in un DbParameter e il nome del parametro generato inserito dove è stato specificato il {0} segnaposto. Ciò garantisce FromSql la sicurezza dagli attacchi SQL injection e invia il valore in modo efficiente e corretto al database.

Quando si eseguono stored procedure, può essere utile usare parametri denominati nella stringa di query SQL, soprattutto quando la stored procedure include parametri facoltativi:

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

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

Se è necessario un maggiore controllo sul parametro di database inviato, è anche possibile crearne un DbParameter e specificarlo come valore di parametro. In questo modo è possibile impostare il tipo di database preciso del parametro o i facet, ad esempio dimensioni, precisione o lunghezza:

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

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

Nota

I parametri passati devono corrispondere esattamente alla definizione della stored procedure. Prestare particolare attenzione all'ordinamento dei parametri, prestando attenzione a non perdere o insostituire nessuno di essi, o prendere in considerazione l'uso della notazione dei parametri denominati. Assicurarsi inoltre che i tipi di parametro corrispondano e che i relativi facet (dimensioni, precisione, scala) siano impostati in base alle esigenze.

Parametri e SQL dinamici

FromSql e la relativa parametrizzazione devono essere usate laddove possibile. Esistono tuttavia alcuni scenari in cui SQL deve essere raggruppato in modo dinamico e i parametri del database non possono essere usati. Si supponga, ad esempio, che una variabile C# contenga il nome della proprietà da filtrare. Potrebbe essere consigliabile usare una query SQL, ad esempio quanto segue:

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

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

Questo codice non funziona, perché i database non consentono di parametrizzare i nomi di colonna (o qualsiasi altra parte dello schema).

Prima di tutto, è importante considerare le implicazioni della creazione dinamica di una query tramite SQL o in caso contrario. L'accettazione di un nome di colonna da un utente può consentire loro di scegliere una colonna non indicizzata, rendendo la query eseguita estremamente lentamente ed eseguire l'overload del database. oppure può consentire loro di scegliere una colonna contenente dati che non si desidera esporre. Ad eccezione di scenari realmente dinamici, in genere è preferibile avere due query per due nomi di colonna, anziché usare la parametrizzazione per comprimerli in una singola query.

Se si è deciso di costruire dinamicamente sql, è necessario usare FromSqlRaw, che consente di interpolare i dati delle variabili direttamente nella stringa SQL, anziché usare un parametro di database:

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

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

Nel codice precedente il nome della colonna viene inserito direttamente in SQL usando l'interpolazione di stringhe C#. È responsabilità dell'utente assicurarsi che questo valore stringa sia sicuro, purificandolo se proviene da un'origine non sicura; ciò significa rilevare caratteri speciali, ad esempio punti e virgola, commenti e altri costrutti SQL, e di eseguirne l'escape in modo corretto o rifiutare tali input.

D'altra parte, il valore della colonna viene inviato tramite un DbParametere pertanto è sicuro in caso di inserimento SQL.

Avviso

Prestare molta attenzione quando si usa FromSqlRawe assicurarsi sempre che i valori provenano da un'origine sicura o siano correttamente sanificati. Gli attacchi SQL injection possono avere conseguenze disastrose per l'applicazione.

Composizione con LINQ

È possibile comporre nella parte superiore della query SQL iniziale usando gli operatori LINQ; EF Core considererà SQL come sottoquery e lo comporrà nel database. Nell'esempio seguente viene usata una query SQL che seleziona da una funzione con valori di tabella (TVF). E quindi compone su di esso usando LINQ per filtrare e ordinare.

var searchTerm = "Lorem ipsum";

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

La query precedente genera il codice SQL seguente:

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

L'operatore Include può essere usato per caricare i dati correlati, esattamente come per qualsiasi altra query LINQ:

var searchTerm = "Lorem ipsum";

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

La composizione con LINQ richiede che la query SQL sia componibile, poiché EF Core considererà SQL fornito come sottoquery. Le query SQL componibili iniziano in genere con la SELECT parola chiave e non possono contenere funzionalità SQL non valide in una sottoquery, ad esempio:

  • Punto e virgola finale
  • In SQL Server, un hint a livello di query finale, ad esempio OPTION (HASH JOIN)
  • In SQL Server, una ORDER BY clausola che non viene usata con OFFSET 0 OR TOP 100 PERCENT nella SELECT clausola

SQL Server non consente la composizione delle chiamate di stored procedure, pertanto qualsiasi tentativo di applicare operatori di query aggiuntivi a una chiamata di questo tipo comporterà l'errore SQL non valido. Usare AsEnumerable o AsAsyncEnumerable subito dopo FromSql o FromSqlRaw per assicurarsi che EF Core non tenti di comporre in una stored procedure.

Registrazione modifiche

Query che usano FromSql o FromSqlRaw seguono le stesse regole di rilevamento delle modifiche di qualsiasi altra query LINQ in EF Core. Ad esempio, se i tipi di entità dei progetti di query vengono rilevati per impostazione predefinita.

Nell'esempio seguente viene usata una query SQL che seleziona da una funzione con valori di tabella (TVF), quindi disabilita il rilevamento delle modifiche con la chiamata a AsNoTracking:

var searchTerm = "Lorem ipsum";

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

Esecuzione di query sui tipi scalari (non entità)

Nota

Questa funzionalità è stata introdotta in EF Core 7.0.

Sebbene FromSql sia utile per l'esecuzione di query sulle entità definite nel modello, SqlQuery consente di eseguire facilmente query per tipi scalari, non di entità tramite SQL, senza dover eseguire l'elenco a discesa fino alle API di accesso ai dati di livello inferiore. Ad esempio, la query seguente recupera tutti gli ID dalla Blogs tabella:

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

È anche possibile comporre operatori LINQ sulla query SQL. Tuttavia, poiché SQL diventa una sottoquery alla cui colonna di output deve essere fatto riferimento da SQL EF, è necessario denominare la colonna Valuedi output . Ad esempio, la query seguente restituisce gli ID superiori alla media ID:

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

FromSql può essere usato con qualsiasi tipo scalare supportato dal provider di database. Se si vuole usare un tipo non supportato dal provider di database, è possibile usare la configurazione pre-convenzione per definire una conversione di valori per tale tipo.

SqlQueryRaw consente la costruzione dinamica di query SQL, proprio come FromSqlRaw per i tipi di entità.

Esecuzione di sql senza query

In alcuni scenari potrebbe essere necessario eseguire SQL che non restituisce dati, in genere per modificare i dati nel database o chiamare una stored procedure che non restituisce alcun set di risultati. Questa operazione può essere eseguita tramite ExecuteSql:

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

Viene eseguito il codice SQL fornito e viene restituito il numero di righe modificate. ExecuteSql protegge da SQL injection usando parametrizzazione sicura, proprio come FromSqle ExecuteSqlRaw consente la costruzione dinamica di query SQL, proprio come FromSqlRaw per le query.

Nota

Prima di EF Core 7.0, a volte era necessario usare le ExecuteSql API per eseguire un "aggiornamento in blocco" nel database, come illustrato in precedenza. Questa operazione risulta notevolmente più efficiente rispetto all'esecuzione di query per tutte le righe corrispondenti e quindi all'uso SaveChanges per modificarle. EF Core 7.0 ha introdotto ExecuteUpdate e ExecuteDelete, che ha reso possibile esprimere operazioni di aggiornamento bulk efficienti tramite LINQ. È consigliabile usare queste API quando possibile, anziché ExecuteSql.

Limiti

Esistono alcune limitazioni da tenere presenti quando si restituiscono tipi di entità dalle query SQL:

  • La query SQL deve restituire dati per tutte le proprietà del tipo di entità.
  • I nomi delle colonne nel set di risultati devono corrispondere ai nomi di colonna a cui sono mappate le proprietà. Si noti che questo comportamento è diverso da EF6; EF6 ha ignorato il mapping delle proprietà a colonna per le query SQL e i nomi delle colonne del set di risultati hanno dovuto corrispondere a tali nomi di proprietà.
  • La query SQL non può contenere dati correlati. Tuttavia, in molti casi è possibile estendere la query usando l'operatore Include per restituire i dati correlati (vedere Inclusione di dati correlati).