I am using Entity Framework Core 1.1.0 and EntityFrameworkCore.SqlServer 1.1.0 as a DataBase provider (added information from comments). And SQL Server 2014.
I have these models:
public class User { public int Id { get; set; } public virtual ICollection<IdentityUserRole> Roles { get; } } public class IdentityUserRole { public int RoleId { get; set; } public int UserId { get; set; } }
I am using this code:
IQueryable<User> query = context.Set<User>(); IQueryable<User> query2 = query.Include(u => u.Roles);
When I call query.ToList() , I have one SQL query:
SELECT [u].[Id], [u].[Email], [u].[UserName] FROM [User] AS [u]
And when I call query2.ToList() , I have two SQL queries:
1.
SELECT [u].[Id], [u].[Email], [u].[UserName] FROM [User] AS [u] ORDER BY [u].[Id]
2.
SELECT [i].[UserId], [i].[RoleId] FROM [IdentityUserRole<int>] AS [i] WHERE EXISTS ( SELECT 1 FROM [User] AS [u] WHERE [i].[UserId] = [u].[Id]) ORDER BY [i].[UserId]
Why does EF add ORDER BY [u].[Id] and ORDER BY [i].[UserId] to SQL queries? Everything is working fine without any ORDER BY statements.
I have this question because if I call query2.OrderByDescending(q => q.Id) , I have this invalid SQL:
SELECT [u].[Id], [u].[Email], [u].[UserName] FROM [User] AS [u] ORDER BY [u].[Id] DESC, [u].[Id]
And the SQL server returns an exception:
The column was specified more than once in the list order.