A column has been specified more than once in the order by list. Columns in the order by list must be unique. #3180
Assignees
Labels
closed-fixed
The issue has been fixed and is/will be included in the release indicated by the issue milestone.
type-bug
Milestone
Comments
|
Could you also share a model used to cause this issue? |
|
I've removed columns that had only public class InfrastructureReportingServiceEntities : DbContext
{
public virtual DbSet<Application> Applications { get; set; }
public virtual DbSet<Organisation> Organisations { get; set; }
public virtual DbSet<OrganisationSnapshot> OrganisationSnapshots { get; set; }
public virtual DbSet<OrganisationSnapshotSubscription> OrganisationSnapshotSubscriptions { get; set; }
public virtual DbSet<Snapshot> Snapshots { get; set; }
public virtual DbSet<Subscription> Subscriptions { get; set; }
public virtual DbSet<User> Users { get; set; }
public virtual DbSet<UserSnapshot> UserSnapshots { get; set; }
public virtual DbSet<UserSnapshotSubscription> UserSnapshotSubscriptions { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
optionsBuilder.UseSqlServer(new SqlConnection("xxx"));
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Snapshot>().ToTable("Snapshots");
modelBuilder.Entity<Snapshot>().Key(x => x.Id);
modelBuilder.Entity<Organisation>().ToTable("Organisations");
modelBuilder.Entity<Organisation>().Key(x => x.Id);
modelBuilder.Entity<Organisation>().AlternateKey(x => x.Checksum);
modelBuilder.Entity<Organisation>().Property(x => x.Checksum).ConcurrencyToken().MaxLength(32);
modelBuilder.Entity<User>().ToTable("Users");
modelBuilder.Entity<User>().Key(x => x.Id);
modelBuilder.Entity<User>().AlternateKey(x => x.Checksum);
modelBuilder.Entity<User>().Property(x => x.Checksum).ConcurrencyToken().MaxLength(32);
modelBuilder.Entity<User>().Property(x => x.CreatedOn).Required();
modelBuilder.Entity<Subscription>().ToTable("Subscriptions");
modelBuilder.Entity<Subscription>().Key(x => x.Id);
modelBuilder.Entity<Subscription>().AlternateKey(x => x.Checksum);
modelBuilder.Entity<Subscription>().Property(x => x.Checksum).ConcurrencyToken().MaxLength(32);
modelBuilder.Entity<Application>().ToTable("Applications");
modelBuilder.Entity<Application>().Key(x => x.Id);
modelBuilder.Entity<Application>().AlternateKey(x => x.Checksum);
modelBuilder.Entity<Application>().Property(x => x.Checksum).ConcurrencyToken().MaxLength(32);
modelBuilder.Entity<Application>().AlternateKey(x => new { x.Name, x.AppId });
modelBuilder.Entity<OrganisationSnapshot>().ToTable("OrganisationSnapshots");
modelBuilder.Entity<OrganisationSnapshot>().Key(x => x.Id);
modelBuilder.Entity<OrganisationSnapshot>().Reference(x => x.Snapshot).InverseReference(x => x.OrganisationSnapshot);
modelBuilder.Entity<OrganisationSnapshot>().Reference(x => x.Organisation).InverseCollection(x => x.OrganisationSnapshots).ForeignKey(x => x.OrganisationId).PrincipalKey(x => x.Id);
modelBuilder.Entity<OrganisationSnapshotSubscription>().ToTable("OrganisationSnapshotSubscriptions");
modelBuilder.Entity<OrganisationSnapshotSubscription>().Key(x => x.Id);
modelBuilder.Entity<OrganisationSnapshotSubscription>().Reference(x => x.OrganisationSnapshot).InverseCollection(x => x.OrganisationSnapshotSubscriptions).ForeignKey(x => x.OrganisationSnapshotId).PrincipalKey(x => x.Id);
modelBuilder.Entity<OrganisationSnapshotSubscription>().Reference(x => x.Subscription).InverseCollection(x => x.OrganisationSnapshotSubscriptions).ForeignKey(x => x.SubscriptionId).PrincipalKey(x => x.Id);
modelBuilder.Entity<UserSnapshot>().ToTable("UserSnapshots");
modelBuilder.Entity<UserSnapshot>().Key(x => x.Id);
modelBuilder.Entity<UserSnapshot>().Reference(x => x.User).InverseCollection(x => x.UserSnapshots).ForeignKey(x => x.UserId).PrincipalKey(x => x.Id);
modelBuilder.Entity<UserSnapshot>().Reference(x => x.Snapshot).InverseCollection(x => x.UserSnapshots).ForeignKey(x => x.SnapshotId).PrincipalKey(x => x.Id);
modelBuilder.Entity<UserSnapshotSubscription>().ToTable("UserSnapshotSubscriptions");
modelBuilder.Entity<UserSnapshotSubscription>().Key(x => x.Id);
modelBuilder.Entity<UserSnapshotSubscription>().Reference(x => x.UserSnapshot).InverseCollection(x => x.UserSnapshotSubscriptions).ForeignKey(x => x.UserSnapshotId).PrincipalKey(x => x.Id);
modelBuilder.Entity<UserSnapshotSubscription>().Reference(x => x.Subscription).InverseCollection(x => x.UserSnapshotSubscriptions).ForeignKey(x => x.SubscriptionId).PrincipalKey(x => x.Id);
}
} |
|
I feel like I have yet another instance of this bug hitting me. Fortunately this time I have a stacktrace too. This time, however, I have no SQL to offer for the crashed instance as it doesn't get that far. The following query works fine in EF6 but not in EF7: var finalCandidateSnapshots = context.Snapshots.Select(x => new { x.CorrelationId, x.GenerationTime })
.OrderByDescending(x => x.GenerationTime).Take(300)
.GroupBy(x => x.CorrelationId).Where(x => x.Count() > 75)
.SelectMany(x => x).Distinct();
return finalCandidateSnapshots.Single(x => x.GenerationTime == finalCandidateSnapshots.Max(cs => cs.GenerationTime)).CorrelationId;EF6 yields SQL: exec sp_executesql N'SELECT MAX([t5].[GenerationTime]) AS [value]
FROM (
SELECT DISTINCT [t4].[CorrelationId], [t4].[GenerationTime]
FROM (
SELECT COUNT(*) AS [value], [t1].[CorrelationId]
FROM (
SELECT TOP (300) [t0].[CorrelationId]
FROM [Snapshots] AS [t0]
ORDER BY [t0].[GenerationTime] DESC
) AS [t1]
GROUP BY [t1].[CorrelationId]
) AS [t2]
CROSS JOIN (
SELECT TOP (300) [t3].[CorrelationId], [t3].[GenerationTime]
FROM [Snapshots] AS [t3]
ORDER BY [t3].[GenerationTime] DESC
) AS [t4]
WHERE ([t2].[value] > @p0) AND ([t2].[CorrelationId] = [t4].[CorrelationId])
) AS [t5]',N'@p0 int',@p0=75
exec sp_executesql N'SELECT [t5].[CorrelationId], [t5].[GenerationTime]
FROM (
SELECT DISTINCT [t4].[CorrelationId], [t4].[GenerationTime]
FROM (
SELECT COUNT(*) AS [value], [t1].[CorrelationId]
FROM (
SELECT TOP (300) [t0].[CorrelationId]
FROM [Snapshots] AS [t0]
ORDER BY [t0].[GenerationTime] DESC
) AS [t1]
GROUP BY [t1].[CorrelationId]
) AS [t2]
CROSS JOIN (
SELECT TOP (300) [t3].[CorrelationId], [t3].[GenerationTime]
FROM [Snapshots] AS [t3]
ORDER BY [t3].[GenerationTime] DESC
) AS [t4]
WHERE ([t2].[value] > @p0) AND ([t2].[CorrelationId] = [t4].[CorrelationId])
) AS [t5]
WHERE [t5].[GenerationTime] = @p1',N'@p0 int,@p1 datetime',@p0=75,@p1='2015-09-24 13:00:04.800'
EF7 throws: An item with the same key has already been added. with StackTrace: at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource) PS: I have a better query that does the above in one step and is much faster than this version but then I don't manage to run into this bug either and I was trying to take a path that would let me run into OrderBy issue. |
|
Any feedback at all?! |
|
I am having the same issue with the multiple columns being included in the order by with the same column name. Essentially I am trying to grab information relating to a record that has many attachments to other sections of the data. It seems like all of the one-to-one relationships pick up the data properly and the first one-to-many relationship loads fine. The issue comes into play when the first one-to-many relationship in the model tries to pull in any one of its one-to-many relationship children. The query appears to work as long as I remove the erroneous extra columns in the order by part of the query. It seems that there is a missing guard or missing clean-up for the query generated. |
|
thanks @maumar. I'm going on holiday for a week so I won't be able to offer feedback for this quickly but I'll keep an eye in case you need more information. |
|
@venomed thanks, the repro you provided is very detailed, and should be more than enough for the purpose of investigation. |
|
@venomed which version of EF are you using? When I tried running the first query against current bits, I got the following SQL without the duplicate orderings: If you were using older version, please try running the code with the latest beta. If you saw this with beta7 or later I will investigate further. @emrs-jm could you share the model and query for the case in which you are seeing this issue? Also, which version of EF are you using? |
|
I was using EF7-beta6. I just switched to EF6-beta7 to see if the issue is fixed there. I do not know if it is because I get a different error before hand. The current error is "Cannot create a row of size 9982 which is greater than the allowable maximum row size of 8060" Since the code is a branch of a production system, I cannot put too much model information here, but is there a way that I can PM it to you or something like that? Below is what I can reduce to show the issue. The Sql query is much bigger version of In the Ideal world there would be only the following in the query join/from : two response.Person, one ClientFile.Transmission, one response.Payer, one Response.Provider, one ClientFile.Header, one ClientFile.Detail, one ClientFile.Inquiry, one ClientSetup.Payer, and one ClientSetup.Provider. The EF7 code that generated the above query is below The code that is commented out is the code that would cause the query to crash in both EF7 beta 6 and EF7 beta 7. I can work around this by loading the children in a query after the other query, but it would be much more convenient to have the ability to load it once. It may also make some sense to add some sort of API for an include where you don't have to restart an include chain if you want to get two different grandchildren. My understanding of the way the explicit includes work if I want two different grandchildren I would do something like Maybe you could instead have something like |
|
@maumar Sorry about the late reply. I was running off beta8 (originally beta7 but upgraded before raising issue to be sure it wasn't something fixed in later build). I just upgraded EntityFramework.Core / EntityFramework.Commands to RC1-15945 and EntityFramework.SqlServer to RC1-15886 to see if the problem can be reproduced and I'm getting a strange "Entry point was not found." error with a code as simple as this. I'm not sure what I'm doing wrong: // Linqpad 5 AnyCPU snippet
using(var context = new InfrastructureEntities())
context.Snapshots.Take(10).Dump();Stacktrace is: |
|
We recently renamed SQL provider from SqlServer to MicrosoftSqlServer, and what you see might be an artifact of that. Try deleting your packages cache. |
|
@maumar Thanks for the tip. Took me a few nuget removes & readds but it finally worked out (i had many nuget left overs from previous installs that were being referenced and it was just generally a mess). Unfortunately RC1-15945 offers me exact same error. Stacktrace at innermost exception (actual SqlException) is: at System.Data.SqlClient.SqlCommand.<>c__DisplayClass16.<ExecuteDbDataReaderAsync>b__17(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- 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.Data.Entity.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.<MoveNext>d__5.MoveNext() |
|
I got the address so you can remove the other comment. I'll send the failing code as soon as I can come up for air. You should have received an email from me a moment ago. I do have a work around that works, but I'm not sure if the query ends up as efficient as batching it all in one go would. |
|
@venomed are you using ASP.NET5 or regular .net? this is the code I came up with, based on your repro. Could you check if it works for you as well? Are there any significant differences between the one you have on your end? Alternatively, could you send me a complete repro project that fails for you, so I can do the investigation (rather than off-loading the diffing and whatnot to you) |
|
@maumar I think I've isolated the problem using your repro code. You're using nullable FKs and I'm not. Otherwise your model code is pretty much identical to mine. I'm targetting .net 4.6 full. I had to comment out your group by query as it was throwing "An item with the same key has already been added.". Here's a complete repro code using my own model entities and indeed reproduces the problem on RC1-15945: namespace Repro3180
{
using System;
using System.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;
using Microsoft.Data.Entity;
public class Application
{
public int Id { get; set; }
public string Name { get; set; }
public int AppId { get; set; }
public string Checksum { get; set; }
public virtual List<Subscription> Subscriptions { get; set; }
}
public class Organisation
{
public int Id { get; set; }
public string Checksum { get; set; }
public virtual List<OrganisationSnapshot> OrganisationSnapshots { get; set; }
}
public class OrganisationSnapshot
{
public int Id { get; set; }
public int OrganisationId { get; set; }
public virtual Organisation Organisation { get; set; }
public virtual Snapshot Snapshot { get; set; }
public virtual List<OrganisationSnapshotSubscription> OrganisationSnapshotSubscriptions { get; set; }
}
public class OrganisationSnapshotSubscription
{
public int Id { get; set; }
public int OrganisationSnapshotId { get; set; }
public int SubscriptionId { get; set; }
public virtual OrganisationSnapshot OrganisationSnapshot { get; set; }
public virtual Subscription Subscription { get; set; }
}
public class Snapshot
{
public int Id { get; set; }
public Guid CorrelationId { get; set; }
public DateTime GenerationTime { get; set; }
public virtual OrganisationSnapshot OrganisationSnapshot { get; set; }
public virtual List<UserSnapshot> UserSnapshots { get; set; }
}
public class Subscription
{
public int Id { get; set; }
public string Checksum { get; set; }
public virtual Application Application { get; set; }
public virtual List<OrganisationSnapshotSubscription> OrganisationSnapshotSubscriptions { get; set; }
public virtual List<UserSnapshotSubscription> UserSnapshotSubscriptions { get; set; }
}
public class User
{
public int Id { get; set; }
public DateTime CreatedOn { get; set; }
public string Checksum { get; set; }
public virtual List<UserSnapshot> UserSnapshots { get; set; }
}
public class UserSnapshot
{
public int Id { get; set; }
public int UserId { get; set; }
public int SnapshotId { get; set; }
public virtual Snapshot Snapshot { get; set; }
public virtual User User { get; set; }
public virtual List<UserSnapshotSubscription> UserSnapshotSubscriptions { get; set; }
}
public class UserSnapshotSubscription
{
public int Id { get; set; }
public int UserSnapshotId { get; set; }
public int SubscriptionId { get; set; }
public virtual Subscription Subscription { get; set; }
public virtual UserSnapshot UserSnapshot { get; set; }
}
public class MyContext : DbContext
{
public virtual DbSet<Application> Applications { get; set; }
public virtual DbSet<Organisation> Organisations { get; set; }
public virtual DbSet<OrganisationSnapshot> OrganisationSnapshots { get; set; }
public virtual DbSet<OrganisationSnapshotSubscription> OrganisationSnapshotSubscriptions { get; set; }
public virtual DbSet<Snapshot> Snapshots { get; set; }
public virtual DbSet<Subscription> Subscriptions { get; set; }
public virtual DbSet<User> Users { get; set; }
public virtual DbSet<UserSnapshot> UserSnapshots { get; set; }
public virtual DbSet<UserSnapshotSubscription> UserSnapshotSubscriptions { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(new SqlConnectionStringBuilder
{
DataSource = ".",
InitialCatalog = "Repro3180",
MultipleActiveResultSets = false,
IntegratedSecurity = true,
ApplicationIntent = ApplicationIntent.ReadOnly
}.ToString());
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Snapshot>().ToTable("Snapshots");
modelBuilder.Entity<Snapshot>().HasKey(x => x.Id);
modelBuilder.Entity<Organisation>().ToTable("Organisations");
modelBuilder.Entity<Organisation>().HasKey(x => x.Id);
modelBuilder.Entity<Organisation>().HasAlternateKey(x => x.Checksum);
modelBuilder.Entity<Organisation>().Property(x => x.Checksum).IsConcurrencyToken().HasMaxLength(32);
modelBuilder.Entity<User>().ToTable("Users");
modelBuilder.Entity<User>().HasKey(x => x.Id);
modelBuilder.Entity<User>().HasAlternateKey(x => x.Checksum);
modelBuilder.Entity<User>().Property(x => x.Checksum).IsConcurrencyToken().HasMaxLength(32);
modelBuilder.Entity<User>().Property(x => x.CreatedOn).IsRequired();
modelBuilder.Entity<Subscription>().ToTable("Subscriptions");
modelBuilder.Entity<Subscription>().HasKey(x => x.Id);
modelBuilder.Entity<Subscription>().HasAlternateKey(x => x.Checksum);
modelBuilder.Entity<Subscription>().Property(x => x.Checksum).IsConcurrencyToken().HasMaxLength(32);
modelBuilder.Entity<Application>().ToTable("Applications");
modelBuilder.Entity<Application>().HasKey(x => x.Id);
modelBuilder.Entity<Application>().HasAlternateKey(x => x.Checksum);
modelBuilder.Entity<Application>().Property(x => x.Checksum).IsConcurrencyToken().HasMaxLength(32);
modelBuilder.Entity<Application>().HasAlternateKey(x => new { x.Name, x.AppId });
modelBuilder.Entity<OrganisationSnapshot>().ToTable("OrganisationSnapshots");
modelBuilder.Entity<OrganisationSnapshot>().HasKey(x => x.Id);
modelBuilder.Entity<OrganisationSnapshot>().HasOne(x => x.Snapshot).WithOne(x => x.OrganisationSnapshot);
modelBuilder.Entity<OrganisationSnapshot>().HasOne(x => x.Organisation).WithMany(x => x.OrganisationSnapshots).HasForeignKey(x => x.OrganisationId).HasPrincipalKey(x => x.Id);
modelBuilder.Entity<OrganisationSnapshotSubscription>().ToTable("OrganisationSnapshotSubscriptions");
modelBuilder.Entity<OrganisationSnapshotSubscription>().HasKey(x => x.Id);
modelBuilder.Entity<OrganisationSnapshotSubscription>().HasOne(x => x.OrganisationSnapshot).WithMany(x => x.OrganisationSnapshotSubscriptions).HasForeignKey(x => x.OrganisationSnapshotId).HasPrincipalKey(x => x.Id);
modelBuilder.Entity<OrganisationSnapshotSubscription>().HasOne(x => x.Subscription).WithMany(x => x.OrganisationSnapshotSubscriptions).HasForeignKey(x => x.SubscriptionId).HasPrincipalKey(x => x.Id);
modelBuilder.Entity<UserSnapshot>().ToTable("UserSnapshots");
modelBuilder.Entity<UserSnapshot>().HasKey(x => x.Id);
modelBuilder.Entity<UserSnapshot>().HasOne(x => x.User).WithMany(x => x.UserSnapshots).HasForeignKey(x => x.UserId).HasPrincipalKey(x => x.Id);
modelBuilder.Entity<UserSnapshot>().HasOne(x => x.Snapshot).WithMany(x => x.UserSnapshots).HasForeignKey(x => x.SnapshotId).HasPrincipalKey(x => x.Id);
modelBuilder.Entity<UserSnapshotSubscription>().ToTable("UserSnapshotSubscriptions");
modelBuilder.Entity<UserSnapshotSubscription>().HasKey(x => x.Id);
modelBuilder.Entity<UserSnapshotSubscription>().HasOne(x => x.UserSnapshot).WithMany(x => x.UserSnapshotSubscriptions).HasForeignKey(x => x.UserSnapshotId).HasPrincipalKey(x => x.Id);
modelBuilder.Entity<UserSnapshotSubscription>().HasOne(x => x.Subscription).WithMany(x => x.UserSnapshotSubscriptions).HasForeignKey(x => x.SubscriptionId).HasPrincipalKey(x => x.Id);
}
}
class Program
{
static void Main(string[] args)
{
using (var ctx = new MyContext())
{
ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();
}
using (var ctx = new MyContext())
{
var correlationId = Guid.NewGuid();
var query = ctx.Snapshots.Where(x => x.CorrelationId == correlationId)
.Include(x => x.OrganisationSnapshot)
.ThenInclude(x => x.Organisation)
.Include(x => x.OrganisationSnapshot)
.ThenInclude(x => x.OrganisationSnapshotSubscriptions)
.ThenInclude(x => x.Subscription)
.ThenInclude(x => x.Application)
.Include(x => x.UserSnapshots)
.ThenInclude(x => x.User)
.Include(x => x.UserSnapshots)
.ThenInclude(x => x.UserSnapshotSubscriptions)
.ThenInclude(x => x.Subscription)
.ThenInclude(x => x.Application)
.Take(10);
var result = query.ToList();
//var finalCandidateSnapshots = ctx.Snapshots.Select(x => new { x.CorrelationId, x.GenerationTime })
// .OrderByDescending(x => x.GenerationTime).Take(300)
// .GroupBy(x => x.CorrelationId).Where(x => x.Count() > 75)
// .SelectMany(x => x).Distinct();
//var result2 = finalCandidateSnapshots.Single(x => x.GenerationTime == finalCandidateSnapshots.Max(cs => cs.GenerationTime)).CorrelationId;
}
}
}
} |
|
@venomed I'm seeing the issue with the repro you provided. Will work on a fix after I'm done with my current workitem (3103) |
|
@venomed quick update - the issue seems to be related to ordering of Includes (making FKs nullable changes the order for some reason). Using your repro (which normally fails) I was able to get the following query to pass on my environment (basically swapping order of includes for UserSnapshot and OrganisationSnapshot) |
maumar
added a commit
that referenced
this issue
Oct 20, 2015
…r by list. Columns in the order by list must be unique. Problem happens for specific combination of nested include statements. When we include collection, we inject ORDER by clauses. We choose them based on the QuerySource of the tables in the select expression. If select expression has multiple tables, added by previous include reference, both tables have the same QuerySource. This makes the choice of order by table ambiguous and may lead to us ordering by the same column twice. Fix is to add additional metadata to TableExpressionBase that represents the type of entity it is associated with. The fix doesn't remove the issue entirely. It can still appear in some cases with self reference includes (where Tables have the same type), or if Includes are done on a subquery (which doesn't have IEntityType associated with it)
maumar
added a commit
that referenced
this issue
Oct 21, 2015
…r by list. Columns in the order by list must be unique. Problem happens for specific combination of nested include statements. When we include reference we add a JOIN on the table that is being included. Issue is that QuerySource of the new table is copied from it's parent table. When include collection, we introduce a new query, but also add order by on the previous query. We determine which table to order based on the query source, but since query sources are the same, the choice is ambiguous. This may lead to queries that try to sort the same column twice, or trying to sort based on column that doesn't exist. Fix is to always apply includes in a deterministic order that would prevent this situation from happening - first include collections, and only then references.
maumar
added a commit
that referenced
this issue
Oct 22, 2015
…r by list. Columns in the order by list must be unique. Problem happens for specific combination of nested include statements. When we include reference we add a JOIN on the table that is being included. Issue is that QuerySource of the new table is copied from it's parent table. When include collection, we introduce a new query, but also add order by on the previous query. We determine which table to order based on the query source, but since query sources are the same, the choice is ambiguous. This may lead to queries that try to sort the same column twice, or trying to sort based on column that doesn't exist. Fix is to always apply includes in a deterministic order that would prevent this situation from happening - first include collections, and only then references. CR: Andrew
|
fixed in a9f0dc0 |
|
I just realised the Milestone for this was changed from RC1 to Release. Will this fix be available in myget feeds before that or do I need to switch to source to be able to pull this in before release? |
|
It's already fixed, so it will release in RC1 (I moved it back). |
|
Oh thank you @rowanmiller |
maumar
added a commit
that referenced
this issue
Oct 23, 2015
…r by list. Columns in the order by list must be unique. Problem happens for specific combination of nested include statements. When we include reference we add a JOIN on the table that is being included. Issue is that QuerySource of the new table is copied from it's parent table. When include collection, we introduce a new query, but also add order by on the previous query. We determine which table to order based on the query source, but since query sources are the same, the choice is ambiguous. This may lead to queries that try to sort the same column twice, or trying to sort based on column that doesn't exist. Fix is to always apply includes in a deterministic order that would prevent this situation from happening - first include collections, and only then references. CR: Andrew
|
correction on the commit - fix is in 0e0793b |
|
I'm using rc1, and I'm still having this problem. Gives me this error, but if I comment out |
|
@ShikiGami please provide full code listing of your model (classes as well as DBContext) so that we can reproduce the issue. I'm going to reopen it and investigate. |
|
My classes are And DbContext |
|
@ShikiGami thanks for the repro. It actually uncovered a potential bug in metadata (#4715). Once that issue is resolved I will investigate the query bit. |
|
blocked by another issue - #5338 |
|
Attempted to reproduce on Reproduction used: class Program
{
static void Main(string[] args)
{
using (var _context = new ApplicationDbContext())
{
_context.Database.EnsureDeleted();
_context.Database.EnsureCreated();
var user = new User { Id = "User" };
var character = new Character { Owner = user, Id = "User2" };
var content = new Content { Owner = character };
var comment = new Content { CommentOf = content };
var follower = new FollowUser { User = user, Following = character };
_context.AddRange(user, character, content, comment, follower);
_context.SaveChanges();
}
using (var _context = new ApplicationDbContext())
{
var u = new { UserId = "User" };
var currentUser = _context.Users
.OfType<User>()
.Include(x => ((User)x).Characters).ThenInclude(y => y.Content).ThenInclude(z => z.Comments)
.Include(x => ((User)x).Characters).ThenInclude(y => y.Followers)
.Where(x => x.Id == u.UserId)
.Single();
}
}
}
public class ApplicationDbContext : DbContext //: IdentityDbContext<ApplicationUser>
{
public DbSet<ApplicationUser> Users { get; set; }
public DbSet<FollowUser> FollowUser { get; set; }
public DbSet<Content> Content { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=MultipleInclude;Integrated Security=True");
}
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
// Users //
builder.Entity<ApplicationUser>()
.HasDiscriminator<string>("AccountType")
.HasValue<User>("User")
.HasValue<Character>("Character");
builder.Entity<User>()
.HasMany(x => x.Characters)
.WithOne(x => x.Owner)
.HasForeignKey(x => x.OwnerId)
.HasPrincipalKey(x => x.Id);
builder.Entity<FollowUser>()
.HasOne(x => x.User)
.WithMany(x => x.Following)
.OnDelete(DeleteBehavior.Cascade);
// Content //
builder.Entity<Content>()
.HasOne(x => x.Owner)
.WithMany(x => x.Content)
.HasForeignKey(x => x.OwnerId);
builder.Entity<Content>()
.HasMany(x => x.Comments)
.WithOne(x => x.CommentOf)
.OnDelete(DeleteBehavior.Restrict);
}
}
public class ApplicationUser // : IdentityUser
{
public string Id { get; set; }
public List<FollowUser> Followers { get; set; }
public List<Content> Content { get; set; }
}
public class User : ApplicationUser
{
public List<Character> Characters { get; set; }
public List<FollowUser> Following { get; set; }
}
public class Character : ApplicationUser
{
[Column(TypeName = "nvarchar(450)")]
public string OwnerId { get; set; }
public User Owner { get; set; }
}
public class FollowUser
{
public int Id { get; set; }
[Column(TypeName = "nvarchar(450)")]
public string UserId { get; set; }
public User User { get; set; }
[Column(TypeName = "nvarchar(450)")]
public string FollowingId { get; set; }
public ApplicationUser Following { get; set; }
}
public class Content
{
public Guid Id { get; set; }
public string ContentType { get; set; }
public DateTime Timestamp { get; set; }
public List<Content> Comments { get; set; }
public Guid? CommentOfId { get; set; }
public Content CommentOf { get; set; }
[Column(TypeName = "nvarchar(450)")]
public string OwnerId { get; set; }
public ApplicationUser Owner { get; set; }
} |
ajcvickers
added
the
closed-fixed
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I'm running the following query to test out relation configuration in context and get the above error:
if I break it down to:
or
then it works fine.
Profiler shows that SQL is indeed messed up:
The text was updated successfully, but these errors were encountered: