Entity Framework Core; utilizzando ORDER BY nella query contro un (MS) SQL Server
c# entity-framework entity-framework-core sql-order-by sql-server
Domanda
Sto cercando di utilizzare la seguente query in combinazione con Entity Framework Core rispetto a Microsoft SQL Server 2016:
SELECT [a], [b], [c]
FROM [x]
WHERE [a] = {0}
ORDER BY [b]
Io uso questa query in questo modo:
context.MySet.AsNoTracking()
.FromSql(MyQuery, aValue)
.Skip(pageSize * page)
.Take(pageSize)
.Select(x => x.ToJsonDictionary())
.ToList()
Lo uso in una API REST di .NET Core con impaginazione e mi piacerebbe avere i record ordinati (in ordine alfabetico) per rendere l'impaginazione più utilizzabile. Ottengo il seguente errore durante l'esecuzione della dichiarazione di cui sopra:
La clausola ORDER BY non è valida per le viste, le funzioni inline, le tabelle derivate, le sottoquery e le espressioni di tabella comuni, a meno che non sia specificato TOP, OFFSET o FOR XML. Uso non valido dell'opzione NEXT nell'istruzione FETCH. La clausola ORDER BY non è valida per le viste, le funzioni inline, le tabelle derivate, le sottoquery e le espressioni di tabella comuni, a meno che non sia specificato TOP, OFFSET o FOR XML. Uso non valido dell'opzione NEXT nell'istruzione FETCH.
Alla ricerca di problemi simili Ho trovato questi altri post ( 1 , 2 , 3 ) ma nessuno dei quali usato in combinazione con EF Core e / o lo stavano usando in un contesto diverso che non si applica nel mio caso (ad es. Sottoquery) .
Ho cercato di utilizzare la .OrderBy(..) di EF anziché di ORDER BY nella query, ma questo non risolve il problema. Ho anche provato ad aggiungere TOP 100 PERCENT dopo SELECT nella query in combinazione con ORDRE BY ; questo ha funzionato ma non ha ordinato la colonna. E 'appena stato ignorato. Questa limitazione è descritta sotto Limitazioni EF . Ho anche trovato questo post che sostituisce il TOP 100 PERCENT... con TOP 99.99 PERCENT... o TOP 9999999... `. Sembra che dovrebbe funzionare ma non 'sentire' giusto. Questo problema in generale è ulteriormente spiegato qui .
Riepilogo: non è consigliabile utilizzare ORDER BY in Views. Usa ORDINARE al di fuori delle viste. In effetti, il design corretto implicherà lo stesso. Se usi TOP insieme a Views, ci sono buone probabilità che View non restituisca tutte le righe della tabella o ignori completamente ORDER BY.
Inoltre sono confuso dalla parola "vista". Per me, il termine viste si riferisce all'uso di quelli creati dalla sintassi CREATE VIEW .. . Una semplice query SQL "normale" considera anche una vista? O è EF Core che avvolge la richiesta in una sorta di vista e questo è il vero problema che causa questo errore?
Non ne sono sicuro, ma finora tutte le "soluzioni" che ho trovato sembrano una specie di "hacky". Pensieri?
Risposta accettata
Semplifichiamo un po 'le cose. Ecco cosa mi è venuto in mente per i test. Ho anche aggiunto del codice per stampare il sql generato dalle query EF .
class Program
{
static void Main(string[] args)
{
DbClient context = new DbClient();
var rawSql = "select [Id], [Title] from Post order by [Title]";
var query = context.Posts.AsNoTracking()
.FromSql(rawSql)
.Skip(1)
.Take(4)
.OrderBy(x => x.Title);
var generated = query.ToSql();
var results = query.ToList();
}
}
class DbClient : DbContext
{
public DbSet<Post> Posts { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("conn_string");
}
}
class Post
{
public int Id { get; set; }
public string Title { get; set; }
public override string ToString() => $"{Id} | {Title}";
}
Quando osserviamo il valore di generated , vediamo qual è il sql della query :
SELECT [t].[Id], [t].[Title]
FROM (
SELECT [p].[Id], [p].[Title]
FROM (
select [Id], [Title] from Post order by [Title]
) AS [p]
ORDER BY (SELECT 1)
OFFSET 1 ROWS FETCH NEXT 4 ROWS ONLY
) AS [t]
ORDER BY [t].[Title]
Si noti che esistono tre clausole order by , quella più interna è quella di rawSql .
Possiamo guardare il messaggio di errore per capire perché non è legale:
La clausola ORDER BY non è valida nelle [...] sottoquery [...] a meno che non sia specificato anche OFFSET.
L'ordine di centro da include compensato, in modo che è valida anche se è all'interno di una sottoquery.
Per risolvere questo problema, è sufficiente rimuovere l'ordine da rawSql e continuare a utilizzare il metodo linq OrderBy() .
var rawSql = "select [Id], [Title] from Post";
var query = context.Posts.AsNoTracking()
.FromSql(rawSql)
.Skip(1)
.Take(4)
.OrderBy(x => x.Title);
Questo genera:
SELECT [t].[Id], [t].[Title]
FROM (
SELECT [p].[Id], [p].[Title]
FROM (
select [Id], [Title] from Post
) AS [p]
ORDER BY (SELECT 1)
OFFSET 1 ROWS FETCH NEXT 4 ROWS ONLY
) AS [t]
ORDER BY [t].[Title]
Ora, tutte le clausole order by non sono nelle sottoquery o hanno una clausola offset.
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow