efcore: OrderBy Guid.NewGuid() does not work when using Include with Many to Many?

I am using Entity Framework Core RC2 and I have the following entities:

public class Organization {
    public Int32 Id { get; set; }
    public String Name { get; set; }
    public virtual ICollection<Connection> Connections { get; set; } = new List<Connection>();
}

public class Connection {
  public Int32 Id { get; set; }
  public Int32 OrganizationId { get; set; }
  public Int32 UserId { get; set; }    
  public virtual Organization Organization { get; set; }
  public virtual User User { get; set; }
}

And the following Mappers:

private static void ConnectionMapper(EntityTypeBuilder<Connection> b) {
  b.ToTable("Connections");
  b.HasKey(x => x.Id);
  b.Property(x => x.Id).UseSqlServerIdentityColumn();
  b.HasOne(x => x.Organization).WithMany(x => x.Connections).HasForeignKey(x => x.OrganizationId).IsRequired().OnDelete(DeleteBehavior.Cascade);
  b.HasOne(x => x.User).WithMany(x => x.Connections).HasForeignKey(x => x.UserId).IsRequired().OnDelete(DeleteBehavior.Cascade);
}

private static void OrganizationMapper(EntityTypeBuilder<Organization> b) {
  b.ToTable("Organizations");
  b.HasKey(x => x.Id);
  b.Property(x => x.Id).UseSqlServerIdentityColumn();
  b.Property(x => x.Name).IsRequired().HasMaxLength(100);
}

I tried the following query:

IQueryable<Organization> orgs = _context.Organizations.AsQueryable();
orgs = orgs.Include(x => x.Connections);
var random = await orgs.OrderBy(x => Guid.NewGuid()).ToListAsync();

And I get the following error: InnerException = Count = error CS0103: The name 'InnerExceptionCount' does not exist in the current context One or more errors occurred. (One or more errors occurred. (No column name was specified for column 1 of 'x0'.)) at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.<MoveNext>d__5.MoveNext() — End of stack trace from previous location where exception was thrown — at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__1291.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at ` However I do not get any error if I order by Id:

IQueryable<Organization> orgs = _context.Organizations.AsQueryable();
orgs = orgs.Include(x => x.Connections);
var random = await orgs.OrderBy(x => x.Id).ToListAsync();

Or if I order by Guid but not including Connections:

 IQueryable<Organization> orgs = _context.Organizations.AsQueryable();       
 var random = await orgs.OrderBy(x => Guid.NewGuid()).ToListAsync();

This is quite strange … Am I missing something?

Thank You, Miguel

About this issue

Original URL
State: closed
Created 8 years ago
Comments: 21 (11 by maintainers)

Most upvoted comments

Discussed at length and decided that this isn’t something we think we want to work on support for. Non-deterministic ordering would require a lot of changes in the query pipeline because it would no longer be possible to stream results from the database because we require the primary data and data from any related collections (fetched in separate query in EF Core) to come back in the same order, so that we can wire it up as we stream it from the database. To do random ordering, we’d have to buffer the whole data set on the client and wire things up once it’s all in-memory.

If buffering is ok, then you can change the query around to do the random ordering on the client, like this.

var random1 = (await _context.Organizations
        .Include(x => x.Connections)
        .ToListAsync())
        .OrderBy(x => Guid.NewGuid());

rowanmiller on Jul 6, 2016