Efcore: Unwanted ORDER BY when using ThenInclude
Whenever "ThenInclude" method is used EF will generate query with ORDER BY.
E.g. this:
context.PersonName.Include(q => q.PersonConns).ThenInclude(q => q.PersonSurname);
Where person_conn is relationship table between table person_name and table person_surname.
Will generate something like this:
SELECT "q.PersonConns".name_id, "q.PersonConns".surname_id, "p.PersonSurname".id, "p.PersonSurname".surname
FROM person_conn AS "q.PersonConns"
INNER JOIN person_surname AS "p.PersonSurname" ON "q.PersonConns".surname_id = "p.PersonSurname".id
INNER JOIN (
SELECT q0.id
FROM person_name AS q0
) AS t ON "q.PersonConns".name_id = t.id
ORDER BY t.id
I think it is an issue and ORDER BY shouldn't be here. It is especially problematic with my other queries where I use ordering myself.
Further technical details:
Npgsql.EntityFrameworkCore.PostgreSQL 2.2.4
W10, Visual Studio 2017 15.9.9
janRucka
All 11 comments
@janRucka Can you provide some more details as to why the ORDER BY is problematic? If you're seeing a specific case that does not work, then please post a small, runnable project/solution or complete code listing that reproduces the behavior you are seeing.
ajcvickers
on 15 Jul 2019
The ORDER BY seems completely useless (or I am missing something?). Hence to me it seems to be an issue by itself (although a very minor one). My case is sorting by two keys so I would like query similar to this:
…
ORDER BY t.one, t.two
But I actually got something like this (yes with unwanted ORDER BY in the middle):
…
ORDER BY t.one, **t.id**, t.two
So it basically means I cannot use ThenInclude and sort by two keys.
Unfortunately I don’t have standalone example. I can create one if it is really necessary.(?) For me the issue seems pretty clear but if it isn't clear for others I will create it.
janRucka
on 15 Jul 2019
@ajcvickers Ok, I had a little bit of time so I created example:
EF-Issue
Few notes:
-You need to already have db running and change connection string in appsettings.json.
-You need to have proper tables already in db (script in Test.sql file)
-Queries are in main and this the problematic one:
query = context.PersonName
.OrderBy(q => q.Name)
.OrderBy(q => q.City)
.Include(q => q.PersonConns)
.ThenInclude(q => q.PersonSurname);
var sortedData = query.ToArray();
// There is ORDER BY id in the middle
// result (wrong '2 London b' and '3 London a' should be switched):
// id city name
// 2 London b
// 3 London a
// 1 Prague c
@ajcvickers - Test this out on nightly please.
Notes: We decided that in 3.0, if you write query like cs.OrderBy(...).OrderBy(...), second order by will overwrite the first order by term. Consecutive order by is not the correct way to write secondary ordering. To have secondary ordering, use ThenBy.
smitpatel
on 16 Jul 2019
@smitpatel - Do I understand you correctly that from next version it won't be possible to use cs.OrderBy(...).OrderBy(...) for sorting by multiple keys at all? That's a breaking change but I guess you know that already...
Originally I had method similar to this one (little more complicated with asc/desc etc.):
private static IOrderedQueryable<Resource> Order<T>(Expression<Func<Resource, T>> keySelector, IQueryable<Resource> query)
{
if (query is IOrderedQueryable<Resource> orderedQuery)
return orderedQuery.ThenBy(keySelector);
return query.OrderBy(keySelector);
}
As you may guessed it doesn't work query is always IOrderedQueryable even before first OrderBy is called (and if ThenBy is called before OrderBy first it throws an exception). So as far as I know there is no way to find out whether to use OrderBy or ThenBy unless you keep track of that somewhere else.
janRucka
on 16 Jul 2019
cc: @divega for breaking change part if we want to announce somewhere.
As you may guessed it doesn't work query is always IOrderedQueryable even before first OrderBy is called
I did not guess that. From my knowledge unless you call OrderBy/ThenBy immediately before you will always have IQueryable. I am not sure why you are seeing IOrderedQueryable. Please provide a full repro which demonstrate that. What you have written is how people apply OrderBy/ThenBy.
smitpatel
on 16 Jul 2019
@smitpatel - I was wrong query is NOT always IOrderedQueryable but if you use Where then it is:
var orderedQuery = context.PersonName.Where(p => p.Name.Contains("a"));
if (orderedQuery is IOrderedQueryable<PersonName> orderedQ)
orderedQuery = orderedQ.ThenBy(p => p.Name); // code goes here because of Where and throws an exception
else
orderedQuery = orderedQuery.OrderBy(p => p.Name);
I added it to my example. It should be possible to sort query first before filtering but you need to expect this kind of behavior.
janRucka
on 17 Jul 2019
@smitpatel I don't have Npgsql setup for the nighltlies, but I ran on 2.2 and current with SQL Server.
2.2:
dbug: Microsoft.EntityFrameworkCore.Query[10101]
Compiling query model:
'(from PersonName q in DbSet<PersonName>
select [q]).Include("PersonConns.PersonSurname")'
dbug: Microsoft.EntityFrameworkCore.Query[10105]
Including navigation: '[q].PersonConns.PersonSurname'
dbug: Microsoft.EntityFrameworkCore.Query[10104]
Optimized query model:
'from PersonName q in DbSet<PersonName>
order by EF.Property(?[q]?, "Id") asc
select PersonName _Include(
queryContext: queryContext,
entity: [q],
included: new object[]{ },
fixup: (QueryContext queryContext | PersonName entity | object[] included) =>
{
void queryContext.QueryBuffer.StartTracking(
entity: entity,
entityType: EntityType: PersonName)
return void queryContext.QueryBuffer.IncludeCollection(
includeId: 0,
navigation: PersonName.PersonConns,
inverseNavigation: PersonConn.PersonName,
targetEntityType: EntityType: PersonConn,
clrCollectionAccessor: ClrICollectionAccessor<PersonName, ICollection<PersonConn>, PersonConn>,
inverseClrPropertySetter: ClrPropertySetter<PersonConn, PersonName>,
tracking: True,
instance: entity,
valuesFactory: () =>
from PersonConn q.PersonConns in DbSet<PersonConn>
join PersonSurname p.PersonSurname in DbSet<PersonSurname>
on Property([q.PersonConns], "PersonSurnameId") equals Property([p.PersonSurname], "Id")
join AnonymousObject _q in
from PersonName q in DbSet<PersonName>
select new AnonymousObject(new object[]{ (object)EF.Property(?[q]?, "Id") })
on Property([q.PersonConns], "PersonNameId") equals (Nullable<long>)object [_q].GetValue(0)
order by object [_q].GetValue(0) asc
select PersonConn _Include(
queryContext: queryContext,
entity: [q.PersonConns],
included: new object[]{ [p.PersonSurname] },
fixup: (QueryContext queryContext | PersonConn entity | object[] included) =>
{
void queryContext.QueryBuffer.StartTracking(
entity: entity,
entityType: EntityType: PersonConn)
return !(bool ReferenceEquals(included[0], null)) ?
{
void queryContext.QueryBuffer.StartTracking(
entity: included[0],
entityType: EntityType: PersonSurname)
void SetRelationshipSnapshotValue(
stateManager: queryContext.StateManager,
navigation: PersonConn.PersonSurname,
entity: entity,
value: included[0])
return void AddToCollectionSnapshot(
stateManager: queryContext.StateManager,
navigation: PersonSurname.PersonConns,
entity: included[0],
value: entity)
} :
{
void SetRelationshipIsLoaded(
stateManager: queryContext.StateManager,
navigation: PersonConn.PersonSurname,
entity: entity)
return default(void)
}
}),
joinPredicate: (PersonName p | PersonConn d) => p.Id == d.PersonNameId)
})'
dbug: Microsoft.EntityFrameworkCore.Query[10107]
(QueryContext queryContext) => IEnumerable<PersonName> _InterceptExceptions(
|__ source: IEnumerable<PersonName> _TrackEntities(
| |__ results: IEnumerable<PersonName> _Select(
| | |__ source: IEnumerable<PersonName> _ShapedQuery(
| | | |__ queryContext: queryContext,
| | | |__ shaperCommandContext: SelectExpression:
| | | | SELECT [q].[id], [q].[city], [q].[name]
| | | | FROM [person_name] AS [q]
| | | | ORDER BY [q].[id],
| | | |__ shaper: BufferedEntityShaper<PersonName>),
| | |__ selector: (PersonName q) => PersonName _Include(
| | |__ queryContext: queryContext,
| | |__ entity: q,
| | |__ included: new object[]{ },
| | |__ fixup: (QueryContext queryContext | PersonName entity | object[] included) =>
| | {
| | void queryContext.QueryBuffer.StartTracking(
| | entity: entity,
| | entityType: EntityType: PersonName)
| | return void queryContext.QueryBuffer.IncludeCollection(
| | |__ includeId: 0,
| | |__ navigation: PersonName.PersonConns,
| | |__ inverseNavigation: PersonConn.PersonName,
| | |__ targetEntityType: EntityType: PersonConn,
| | |__ clrCollectionAccessor: ClrICollectionAccessor<PersonName, ICollection<PersonConn>, PersonConn>,
| | |__ inverseClrPropertySetter: ClrPropertySetter<PersonConn, PersonName>,
| | |__ tracking: True,
| | |__ instance: entity,
| | |__ valuesFactory: () => IEnumerable<PersonConn> _ShapedQuery(
| | | |__ queryContext: queryContext,
| | | |__ shaperCommandContext: SelectExpression:
| | | | SELECT [q.PersonConns].[name_id], [q.PersonConns].[surname_id], [p.PersonSurname].[id], [p.PersonSurname].[surname]
| | | | FROM [person_conn] AS [q.PersonConns]
| | | | INNER JOIN [person_surname] AS [p.PersonSurname] ON [q.PersonConns].[surname_id] = [p.PersonSurname].[id]
| | | | INNER JOIN (
| | | | SELECT [q0].[id]
| | | | FROM [person_name] AS [q0]
| | | | ) AS [t] ON [q.PersonConns].[name_id] = [t].[id]
| | | | ORDER BY [t].[id],
| | | |__ shaper: (QueryContext queryContext | TransparentIdentifier<PersonConn, PersonSurname> t0) => PersonConn _Include(
| | | |__ queryContext: queryContext,
| | | |__ entity: t0.Outer,
| | | |__ included: new object[]{ t0.Inner },
| | | |__ fixup: (QueryContext queryContext | PersonConn entity | object[] included) =>
| | | {
| | | void queryContext.QueryBuffer.StartTracking(
| | | entity: entity,
| | | entityType: EntityType: PersonConn)
| | | return !(bool ReferenceEquals(included[0], null)) ?
| | | {
| | | void queryContext.QueryBuffer.StartTracking(
| | | entity: included[0],
| | | entityType: EntityType: PersonSurname)
| | | void SetRelationshipSnapshotValue(
| | | stateManager: queryContext.StateManager,
| | | navigation: PersonConn.PersonSurname,
| | | entity: entity,
| | | value: included[0])
| | | return void AddToCollectionSnapshot(
| | | |__ stateManager: queryContext.StateManager,
| | | |__ navigation: PersonSurname.PersonConns,
| | | |__ entity: included[0],
| | | |__ value: entity)
| | | } :
| | | {
| | | void SetRelationshipIsLoaded(
| | | stateManager: queryContext.StateManager,
| | | navigation: PersonConn.PersonSurname,
| | | entity: entity)
| | | return default(void)
| | | }
| | | })),
| | |__ joinPredicate: (PersonName p | PersonConn d) => p.Id == d.PersonNameId)
| | })),
| |__ queryContext: Unhandled parameter: queryContext,
| |__ entityTrackingInfos: { itemType: PersonName },
| |__ entityAccessors: List<Func<PersonName, object>>
| {
| Func<PersonName, PersonName>,
| }),
|__ contextType: EFTest.Data.MyDbContext,
|__ logger: DiagnosticsLogger<Query>,
|__ queryContext: Unhandled parameter: queryContext)
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [q].[id], [q].[city], [q].[name]
FROM [person_name] AS [q]
ORDER BY [q].[id]
dbug: Microsoft.EntityFrameworkCore.Query[10101]
Compiling query model:
'(from PersonName q in DbSet<PersonName>
order by [q].Name asc
order by [q].City asc
select [q]).Include("PersonConns.PersonSurname")'
dbug: Microsoft.EntityFrameworkCore.Query[10105]
Including navigation: '[q].PersonConns.PersonSurname'
dbug: Microsoft.EntityFrameworkCore.Query[10104]
Optimized query model:
'from PersonName q in DbSet<PersonName>
order by [q].Name asc
order by [q].City asc, EF.Property(?[q]?, "Id") asc
select PersonName _Include(
queryContext: queryContext,
entity: [q],
included: new object[]{ },
fixup: (QueryContext queryContext | PersonName entity | object[] included) =>
{
void queryContext.QueryBuffer.StartTracking(
entity: entity,
entityType: EntityType: PersonName)
return void queryContext.QueryBuffer.IncludeCollection(
includeId: 0,
navigation: PersonName.PersonConns,
inverseNavigation: PersonConn.PersonName,
targetEntityType: EntityType: PersonConn,
clrCollectionAccessor: ClrICollectionAccessor<PersonName, ICollection<PersonConn>, PersonConn>,
inverseClrPropertySetter: ClrPropertySetter<PersonConn, PersonName>,
tracking: True,
instance: entity,
valuesFactory: () =>
from PersonConn q.PersonConns in DbSet<PersonConn>
join PersonSurname p.PersonSurname in DbSet<PersonSurname>
on Property([q.PersonConns], "PersonSurnameId") equals Property([p.PersonSurname], "Id")
join AnonymousObject _q in
from PersonName q in DbSet<PersonName>
select new AnonymousObject(new object[]
{
(object)EF.Property(?[q]?, "Id"),
(object)[q].Name,
(object)[q].City
})
on Property([q.PersonConns], "PersonNameId") equals (Nullable<long>)object [_q].GetValue(0)
order by object [_q].GetValue(1) asc
order by object [_q].GetValue(2) asc, object [_q].GetValue(0) asc
select PersonConn _Include(
queryContext: queryContext,
entity: [q.PersonConns],
included: new object[]{ [p.PersonSurname] },
fixup: (QueryContext queryContext | PersonConn entity | object[] included) =>
{
void queryContext.QueryBuffer.StartTracking(
entity: entity,
entityType: EntityType: PersonConn)
return !(bool ReferenceEquals(included[0], null)) ?
{
void queryContext.QueryBuffer.StartTracking(
entity: included[0],
entityType: EntityType: PersonSurname)
void SetRelationshipSnapshotValue(
stateManager: queryContext.StateManager,
navigation: PersonConn.PersonSurname,
entity: entity,
value: included[0])
return void AddToCollectionSnapshot(
stateManager: queryContext.StateManager,
navigation: PersonSurname.PersonConns,
entity: included[0],
value: entity)
} :
{
void SetRelationshipIsLoaded(
stateManager: queryContext.StateManager,
navigation: PersonConn.PersonSurname,
entity: entity)
return default(void)
}
}),
joinPredicate: (PersonName p | PersonConn d) => p.Id == d.PersonNameId)
})'
dbug: Microsoft.EntityFrameworkCore.Query[10107]
(QueryContext queryContext) => IEnumerable<PersonName> _InterceptExceptions(
|__ source: IEnumerable<PersonName> _TrackEntities(
| |__ results: IEnumerable<PersonName> _Select(
| | |__ source: IEnumerable<PersonName> _ShapedQuery(
| | | |__ queryContext: queryContext,
| | | |__ shaperCommandContext: SelectExpression:
| | | | SELECT [q].[id], [q].[city], [q].[name]
| | | | FROM [person_name] AS [q]
| | | | ORDER BY [q].[city], [q].[id], [q].[name],
| | | |__ shaper: BufferedEntityShaper<PersonName>),
| | |__ selector: (PersonName q) => PersonName _Include(
| | |__ queryContext: queryContext,
| | |__ entity: q,
| | |__ included: new object[]{ },
| | |__ fixup: (QueryContext queryContext | PersonName entity | object[] included) =>
| | {
| | void queryContext.QueryBuffer.StartTracking(
| | entity: entity,
| | entityType: EntityType: PersonName)
| | return void queryContext.QueryBuffer.IncludeCollection(
| | |__ includeId: 0,
| | |__ navigation: PersonName.PersonConns,
| | |__ inverseNavigation: PersonConn.PersonName,
| | |__ targetEntityType: EntityType: PersonConn,
| | |__ clrCollectionAccessor: ClrICollectionAccessor<PersonName, ICollection<PersonConn>, PersonConn>,
| | |__ inverseClrPropertySetter: ClrPropertySetter<PersonConn, PersonName>,
| | |__ tracking: True,
| | |__ instance: entity,
| | |__ valuesFactory: () => IEnumerable<PersonConn> _ShapedQuery(
| | | |__ queryContext: queryContext,
| | | |__ shaperCommandContext: SelectExpression:
| | | | SELECT [q.PersonConns].[name_id], [q.PersonConns].[surname_id], [p.PersonSurname].[id], [p.PersonSurname].[surname]
| | | | FROM [person_conn] AS [q.PersonConns]
| | | | INNER JOIN [person_surname] AS [p.PersonSurname] ON [q.PersonConns].[surname_id] = [p.PersonSurname].[id]
| | | | INNER JOIN (
| | | | SELECT [q0].[id], [q0].[name], [q0].[city]
| | | | FROM [person_name] AS [q0]
| | | | ) AS [t] ON [q.PersonConns].[name_id] = [t].[id]
| | | | ORDER BY [t].[city], [t].[id], [t].[name],
| | | |__ shaper: (QueryContext queryContext | TransparentIdentifier<PersonConn, PersonSurname> t0) => PersonConn _Include(
| | | |__ queryContext: queryContext,
| | | |__ entity: t0.Outer,
| | | |__ included: new object[]{ t0.Inner },
| | | |__ fixup: (QueryContext queryContext | PersonConn entity | object[] included) =>
| | | {
| | | void queryContext.QueryBuffer.StartTracking(
| | | entity: entity,
| | | entityType: EntityType: PersonConn)
| | | return !(bool ReferenceEquals(included[0], null)) ?
| | | {
| | | void queryContext.QueryBuffer.StartTracking(
| | | entity: included[0],
| | | entityType: EntityType: PersonSurname)
| | | void SetRelationshipSnapshotValue(
| | | stateManager: queryContext.StateManager,
| | | navigation: PersonConn.PersonSurname,
| | | entity: entity,
| | | value: included[0])
| | | return void AddToCollectionSnapshot(
| | | |__ stateManager: queryContext.StateManager,
| | | |__ navigation: PersonSurname.PersonConns,
| | | |__ entity: included[0],
| | | |__ value: entity)
| | | } :
| | | {
| | | void SetRelationshipIsLoaded(
| | | stateManager: queryContext.StateManager,
| | | navigation: PersonConn.PersonSurname,
| | | entity: entity)
| | | return default(void)
| | | }
| | | })),
| | |__ joinPredicate: (PersonName p | PersonConn d) => p.Id == d.PersonNameId)
| | })),
| |__ queryContext: Unhandled parameter: queryContext,
| |__ entityTrackingInfos: { itemType: PersonName },
| |__ entityAccessors: List<Func<PersonName, object>>
| {
| Func<PersonName, PersonName>,
| }),
|__ contextType: EFTest.Data.MyDbContext,
|__ logger: DiagnosticsLogger<Query>,
|__ queryContext: Unhandled parameter: queryContext)
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [q].[id], [q].[city], [q].[name]
FROM [person_name] AS [q]
ORDER BY [q].[city], [q].[id], [q].[name]
dbug: Microsoft.EntityFrameworkCore.Query[10101]
Compiling query model:
'(from PersonName q in DbSet<PersonName>
order by [q].City asc, [q].Name asc
select [q]).Include("PersonConns.PersonSurname")'
dbug: Microsoft.EntityFrameworkCore.Query[10105]
Including navigation: '[q].PersonConns.PersonSurname'
dbug: Microsoft.EntityFrameworkCore.Query[10104]
Optimized query model:
'from PersonName q in DbSet<PersonName>
order by [q].City asc, [q].Name asc, EF.Property(?[q]?, "Id") asc
select PersonName _Include(
queryContext: queryContext,
entity: [q],
included: new object[]{ },
fixup: (QueryContext queryContext | PersonName entity | object[] included) =>
{
void queryContext.QueryBuffer.StartTracking(
entity: entity,
entityType: EntityType: PersonName)
return void queryContext.QueryBuffer.IncludeCollection(
includeId: 0,
navigation: PersonName.PersonConns,
inverseNavigation: PersonConn.PersonName,
targetEntityType: EntityType: PersonConn,
clrCollectionAccessor: ClrICollectionAccessor<PersonName, ICollection<PersonConn>, PersonConn>,
inverseClrPropertySetter: ClrPropertySetter<PersonConn, PersonName>,
tracking: True,
instance: entity,
valuesFactory: () =>
from PersonConn q.PersonConns in DbSet<PersonConn>
join PersonSurname p.PersonSurname in DbSet<PersonSurname>
on Property([q.PersonConns], "PersonSurnameId") equals Property([p.PersonSurname], "Id")
join AnonymousObject _q in
from PersonName q in DbSet<PersonName>
select new AnonymousObject(new object[]
{
(object)EF.Property(?[q]?, "Id"),
(object)[q].City,
(object)[q].Name
})
on Property([q.PersonConns], "PersonNameId") equals (Nullable<long>)object [_q].GetValue(0)
order by object [_q].GetValue(1) asc, object [_q].GetValue(2) asc, object [_q].GetValue(0) asc
select PersonConn _Include(
queryContext: queryContext,
entity: [q.PersonConns],
included: new object[]{ [p.PersonSurname] },
fixup: (QueryContext queryContext | PersonConn entity | object[] included) =>
{
void queryContext.QueryBuffer.StartTracking(
entity: entity,
entityType: EntityType: PersonConn)
return !(bool ReferenceEquals(included[0], null)) ?
{
void queryContext.QueryBuffer.StartTracking(
entity: included[0],
entityType: EntityType: PersonSurname)
void SetRelationshipSnapshotValue(
stateManager: queryContext.StateManager,
navigation: PersonConn.PersonSurname,
entity: entity,
value: included[0])
return void AddToCollectionSnapshot(
stateManager: queryContext.StateManager,
navigation: PersonSurname.PersonConns,
entity: included[0],
value: entity)
} :
{
void SetRelationshipIsLoaded(
stateManager: queryContext.StateManager,
navigation: PersonConn.PersonSurname,
entity: entity)
return default(void)
}
}),
joinPredicate: (PersonName p | PersonConn d) => p.Id == d.PersonNameId)
})'
dbug: Microsoft.EntityFrameworkCore.Query[10107]
(QueryContext queryContext) => IEnumerable<PersonName> _InterceptExceptions(
|__ source: IEnumerable<PersonName> _TrackEntities(
| |__ results: IEnumerable<PersonName> _Select(
| | |__ source: IEnumerable<PersonName> _ShapedQuery(
| | | |__ queryContext: queryContext,
| | | |__ shaperCommandContext: SelectExpression:
| | | | SELECT [q].[id], [q].[city], [q].[name]
| | | | FROM [person_name] AS [q]
| | | | ORDER BY [q].[city], [q].[name], [q].[id],
| | | |__ shaper: BufferedEntityShaper<PersonName>),
| | |__ selector: (PersonName q) => PersonName _Include(
| | |__ queryContext: queryContext,
| | |__ entity: q,
| | |__ included: new object[]{ },
| | |__ fixup: (QueryContext queryContext | PersonName entity | object[] included) =>
| | {
| | void queryContext.QueryBuffer.StartTracking(
| | entity: entity,
| | entityType: EntityType: PersonName)
| | return void queryContext.QueryBuffer.IncludeCollection(
| | |__ includeId: 0,
| | |__ navigation: PersonName.PersonConns,
| | |__ inverseNavigation: PersonConn.PersonName,
| | |__ targetEntityType: EntityType: PersonConn,
| | |__ clrCollectionAccessor: ClrICollectionAccessor<PersonName, ICollection<PersonConn>, PersonConn>,
| | |__ inverseClrPropertySetter: ClrPropertySetter<PersonConn, PersonName>,
| | |__ tracking: True,
| | |__ instance: entity,
| | |__ valuesFactory: () => IEnumerable<PersonConn> _ShapedQuery(
| | | |__ queryContext: queryContext,
| | | |__ shaperCommandContext: SelectExpression:
| | | | SELECT [q.PersonConns].[name_id], [q.PersonConns].[surname_id], [p.PersonSurname].[id], [p.PersonSurname].[surname]
| | | | FROM [person_conn] AS [q.PersonConns]
| | | | INNER JOIN [person_surname] AS [p.PersonSurname] ON [q.PersonConns].[surname_id] = [p.PersonSurname].[id]
| | | | INNER JOIN (
| | | | SELECT [q0].[id], [q0].[city], [q0].[name]
| | | | FROM [person_name] AS [q0]
| | | | ) AS [t] ON [q.PersonConns].[name_id] = [t].[id]
| | | | ORDER BY [t].[city], [t].[name], [t].[id],
| | | |__ shaper: (QueryContext queryContext | TransparentIdentifier<PersonConn, PersonSurname> t0) => PersonConn _Include(
| | | |__ queryContext: queryContext,
| | | |__ entity: t0.Outer,
| | | |__ included: new object[]{ t0.Inner },
| | | |__ fixup: (QueryContext queryContext | PersonConn entity | object[] included) =>
| | | {
| | | void queryContext.QueryBuffer.StartTracking(
| | | entity: entity,
| | | entityType: EntityType: PersonConn)
| | | return !(bool ReferenceEquals(included[0], null)) ?
| | | {
| | | void queryContext.QueryBuffer.StartTracking(
| | | entity: included[0],
| | | entityType: EntityType: PersonSurname)
| | | void SetRelationshipSnapshotValue(
| | | stateManager: queryContext.StateManager,
| | | navigation: PersonConn.PersonSurname,
| | | entity: entity,
| | | value: included[0])
| | | return void AddToCollectionSnapshot(
| | | |__ stateManager: queryContext.StateManager,
| | | |__ navigation: PersonSurname.PersonConns,
| | | |__ entity: included[0],
| | | |__ value: entity)
| | | } :
| | | {
| | | void SetRelationshipIsLoaded(
| | | stateManager: queryContext.StateManager,
| | | navigation: PersonConn.PersonSurname,
| | | entity: entity)
| | | return default(void)
| | | }
| | | })),
| | |__ joinPredicate: (PersonName p | PersonConn d) => p.Id == d.PersonNameId)
| | })),
| |__ queryContext: Unhandled parameter: queryContext,
| |__ entityTrackingInfos: { itemType: PersonName },
| |__ entityAccessors: List<Func<PersonName, object>>
| {
| Func<PersonName, PersonName>,
| }),
|__ contextType: EFTest.Data.MyDbContext,
|__ logger: DiagnosticsLogger<Query>,
|__ queryContext: Unhandled parameter: queryContext)
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [q].[id], [q].[city], [q].[name]
FROM [person_name] AS [q]
ORDER BY [q].[city], [q].[name], [q].[id]
Closed connection to database 'Test' on server '(localdb)\mssqllocaldb'.
fail: EFTest.Program[0]
An error occurred while seeding the database.
System.ArgumentException: Expression of type 'System.Linq.IQueryable`1[EFTest.Models.PersonName]' cannot be used for parameter of type 'System.Linq.IOrderedQueryable`1[EFTest.Models.PersonName]' of method 'System.Linq.IOrderedQueryable`1[EFTest.Models.PersonName] ThenBy[PersonName,String](System.Linq.IOrderedQueryable`1[EFTest.Models.PersonName], System.Linq.Expressions.Expression`1[System.Func`2[EFTest.Models.PersonName,System.String]])'
Parameter name: arg0
at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)
at System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, Expression arg0, Expression arg1)
at System.Linq.Queryable.ThenBy[TSource,TKey](IOrderedQueryable`1 source, Expression`1 keySelector)
at EFTest.Program.Main(String[] args) in C:\Stuff\EF-Issue-master\EF-Issue-master\EFTest\Program.cs:line 66
dbug: Microsoft.EntityFrameworkCore.Infrastructure[10407]
'MyDbContext' disposed.
Hosting environment: Development
Content root path: C:\Stuff\EF-Issue-master\EF-Issue-master\EFTest
Now listening on: http://localhost:5000
Application started. Press Ctrl+C to shut down.
3.0:
dbug: Microsoft.EntityFrameworkCore.Query[10107]
(QueryContext queryContext) => new QueryingEnumerable<PersonName>(
(RelationalQueryContext)queryContext,
SqlServerQuerySqlGeneratorFactory,
SqlExpressionFactory,
ParameterNameGeneratorFactory,
Projection Mapping:
SELECT p.id, p.city, p.name, t.name_id, t.surname_id, t.id, t.surname
FROM person_name AS p
LEFT JOIN Projection Mapping:
(
SELECT p0.name_id, p0.surname_id, p1.id, p1.surname
FROM person_conn AS p0
INNER JOIN person_surname AS p1 ON p0.surname_id == p1.id
) AS t ON p.id == t.name_id
ORDER BY p.id ASC, t.name_id ASC, t.surname_id ASC, t.id ASC,
Func<QueryContext, DbDataReader, PersonName, int[], ResultCoordinator, PersonName>,
EFTest.Data.MyDbContext,
DiagnosticsLogger<Query>
)
dbug: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [p].[id], [p].[city], [p].[name], [t].[name_id], [t].[surname_id], [t].[id], [t].[surname]
FROM [person_name] AS [p]
LEFT JOIN (
SELECT [p0].[name_id], [p0].[surname_id], [p1].[id], [p1].[surname]
FROM [person_conn] AS [p0]
INNER JOIN [person_surname] AS [p1] ON [p0].[surname_id] = [p1].[id]
) AS [t] ON [p].[id] = [t].[name_id]
ORDER BY [p].[id], [t].[name_id], [t].[surname_id], [t].[id]
dbug: Microsoft.EntityFrameworkCore.Query[10107]
(QueryContext queryContext) => new QueryingEnumerable<PersonName>(
(RelationalQueryContext)queryContext,
SqlServerQuerySqlGeneratorFactory,
SqlExpressionFactory,
ParameterNameGeneratorFactory,
Projection Mapping:
SELECT p.id, p.city, p.name, t.name_id, t.surname_id, t.id, t.surname
FROM person_name AS p
LEFT JOIN Projection Mapping:
(
SELECT p0.name_id, p0.surname_id, p1.id, p1.surname
FROM person_conn AS p0
INNER JOIN person_surname AS p1 ON p0.surname_id == p1.id
) AS t ON p.id == t.name_id
ORDER BY p.city ASC, p.id ASC, t.name_id ASC, t.surname_id ASC, t.id ASC,
Func<QueryContext, DbDataReader, PersonName, int[], ResultCoordinator, PersonName>,
EFTest.Data.MyDbContext,
DiagnosticsLogger<Query>
)
dbug: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [p].[id], [p].[city], [p].[name], [t].[name_id], [t].[surname_id], [t].[id], [t].[surname]
FROM [person_name] AS [p]
LEFT JOIN (
SELECT [p0].[name_id], [p0].[surname_id], [p1].[id], [p1].[surname]
FROM [person_conn] AS [p0]
INNER JOIN [person_surname] AS [p1] ON [p0].[surname_id] = [p1].[id]
) AS [t] ON [p].[id] = [t].[name_id]
ORDER BY [p].[city], [p].[id], [t].[name_id], [t].[surname_id], [t].[id]
dbug: Microsoft.EntityFrameworkCore.Query[10107]
(QueryContext queryContext) => new QueryingEnumerable<PersonName>(
(RelationalQueryContext)queryContext,
SqlServerQuerySqlGeneratorFactory,
SqlExpressionFactory,
ParameterNameGeneratorFactory,
Projection Mapping:
SELECT p.id, p.city, p.name, t.name_id, t.surname_id, t.id, t.surname
FROM person_name AS p
LEFT JOIN Projection Mapping:
(
SELECT p0.name_id, p0.surname_id, p1.id, p1.surname
FROM person_conn AS p0
INNER JOIN person_surname AS p1 ON p0.surname_id == p1.id
) AS t ON p.id == t.name_id
ORDER BY p.city ASC, p.name ASC, p.id ASC, t.name_id ASC, t.surname_id ASC, t.id ASC,
Func<QueryContext, DbDataReader, PersonName, int[], ResultCoordinator, PersonName>,
EFTest.Data.MyDbContext,
DiagnosticsLogger<Query>
)
dbug: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [p].[id], [p].[city], [p].[name], [t].[name_id], [t].[surname_id], [t].[id], [t].[surname]
FROM [person_name] AS [p]
LEFT JOIN (
SELECT [p0].[name_id], [p0].[surname_id], [p1].[id], [p1].[surname]
FROM [person_conn] AS [p0]
INNER JOIN [person_surname] AS [p1] ON [p0].[surname_id] = [p1].[id]
) AS [t] ON [p].[id] = [t].[name_id]
ORDER BY [p].[city], [p].[name], [p].[id], [t].[name_id], [t].[surname_id], [t].[id]
fail: EFTest.Program[0]
An error occurred while seeding the database.
System.ArgumentException: Expression of type 'System.Linq.IQueryable`1[EFTest.Models.PersonName]' cannot be used for parameter of type 'System.Linq.IOrderedQueryable`1[EFTest.Models.PersonName]' of method 'System.Linq.IOrderedQueryable`1[EFTest.Models.PersonName] ThenBy[PersonName,String](System.Linq.IOrderedQueryable`1[EFTest.Models.PersonName], System.Linq.Expressions.Expression`1[System.Func`2[EFTest.Models.PersonName,System.String]])' (Parameter 'arg0')
at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)
at System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, Expression arg0, Expression arg1)
at System.Linq.Queryable.ThenBy[TSource,TKey](IOrderedQueryable`1 source, Expression`1 keySelector)
at EFTest.Program.Main(String[] args) in C:\Stuff\EF-Issue-master\EF-Issue-master\EFTest\Program.cs:line 66
dbug: Microsoft.EntityFrameworkCore.Infrastructure[10407]
'MyDbContext' disposed.
Hosting environment: Development
Content root path: C:\Stuff\EF-Issue-master\EF-Issue-master\EFTest
Now listening on: http://localhost:5000
Application started. Press Ctrl+C to shut down.
@ajcvickers - Thanks. This is working as expected now.
In 2.2 the bug was, we added additional ordering to last OrderBy clause but we still had earlier ordering so it ended up messing up the order.
In 3.0 we preserve the last one win ordering and we ignore multiple order by so it is working as expected.
smitpatel
on 18 Jul 2019
👍1
@janRucka DbSet implements IQueryable only but the moment you start composing over, it changes shape to EntityQueryable (which is EFCore's internal Queryable implementation). EntityQueryable implements IOrderedQueryable. Regardless of that being there, it is likely that implementation of IQueryable also implements IOrderedQueryable (else need a different implementation for it). So checking type at runtime is not right thing to do. You need to decompose the method and identify if last method call was OrderBy in the tree or not and make decision based on that.
To elaborate more on runtime type:
```C#
var name = "JanRucka";
//name is of type string so following can be written
Console.WriteLine(name.Substring(0));
var obj = (object)name;
// Following is compile type error
Console.WriteLine(obj.Substring(0));
// But if you check obj.GetType at runtime it will be still string.
smitpatel
on 18 Jul 2019
👍1
Fixed via overwriting the order by
smitpatel
on 18 Jul 2019
Was this page helpful?
0 / 5 - 0 ratings
Related issues
ra0o0f
·
3Comments
julienshepherd
·
3Comments
ericvb
·
3Comments
ghost
·
3Comments
leak
·
3Comments