Condividi tramite

Query SQL

  • Articolo

Entity Framework Core consente di ricorrere a query SQL quando si lavora con 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

L'opzione FromSql è stata introdotta in EF Core 7.0. Quando si usano versioni precedenti, usare FromSqlInterpolated piuttosto.

Le query SQL possono essere utilizzate 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 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 maggiori informazioni sull'inserimento di SQL, consultare questa pagina.

I metodi FromSql e FromSqlInterpolated sono sicuri da SQL injection e integrano sempre i dati dei parametri come parametro SQL separato. Tuttavia, il metodo FromSqlRaw 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 apparentemente questa sintassi sembra un'interpolazione della stringa C# regolare, il valore fornito viene eseguito in un DbParameter e il parametro generato viene inserito nella posizione in cui viene specificato il segnaposto {0}. Ciò garantisce la sicurezza di FromSql 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 creare 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 malposizionare 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 provengano 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 dati correlati, come in 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 parola chiave SELECT e non possono contenere funzionalità SQL non valide in una sottoquery, ad esempio:

  • Un 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

Le query che usano FromSql o FromSqlRaw osservano le stesse regole di rilevamento modifiche di qualsiasi altra query LINQ in EF Core. Se ad esempio la query proietta tipi di entità, i risultati vengono rilevati per impostazione predefinita.

L'esempio seguente usa una query SQL non elaborata che effettua selezioni da una funzione con valori di tabella (TVF), quindi disabilita il rilevamento delle modifiche mediante 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 tabella Blogs:

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 di output Value. 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. A tale scopo, usare 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. Si consiglia di utilizzare tali API quando possibile, anziché ExecuteSql.

Limiti

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

  • La query SQL deve restituire i 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 in cui il mapping di proprietà/colonne viene ignorato per le query SQL non elaborate e i nomi di colonna del set di risultati devono corrispondere ai 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).