No way to .Include() multiple properties with AsNoTracking() Enabled

[vitalybibikov]

I have a query, where I need to Include Multiple Navigation properties, that exist on the same level.
E.g. it can be done like so:

(the query is simplified to reflect only the problem)

              var targetQuestion = await context.TemplateQuestions
                    .Include(q => q.Category)
                        .ThenInclude(c => c.Template)
                            .ThenInclude(t => t.Categories)
                    .Include(q => q.Category)
                        .ThenInclude(c => c.Questions)
                    .AsNoTracking()
                    .SingleAsync(q => q.Id == question.Id);

Or rewritten like so:

                var targetQuestion = await context.TemplateQuestions
                    .Include(q => q.Category.Questions)
                    .Include(c => c.Category.Template.Categories)
                    .AsNoTracking()
                    .SingleAsync(q => q.Id == question.Id);

Details:

• My Context is set up as Scoped/Per-Request, not Transient.
• As the Context is Scoped, I do need AsNoTracking() here in order to get the updates, that were performed on that instance.

In the current , 3.0 version of the Ef Core I started to get the following exception:

System.InvalidOperationException : The Include path 'Category->Questions' results in a cycle. Cycles are not allowed in no-tracking queries. Either use a tracking query or remove the cycle.

Is tracking really required, when handling multiple same-level properties of the Entity?

[smitpatel]

Your include path contains cycle. TemplateQuestions -> Category -> Questions. In No tracking query we will materialize the Question entity again when loading that navigation, which will generate a different instance than the root instance. Hence generating incorrect results.

• Why are you including cycles? If your purpose is to have Questions populated then EF Core does that already. When you load Category navigation, EF Core also populate inverse navigation as a part of fix-up.
• The behavior is same as EF6.

[ajcvickers]

@smitpatel

• Are you sure? My interpretation of this is that not all questions for the category will be loaded without the additional Include.
• Have you tried it on EF6? I would like to ensure that the behavior here is the same, and if not, look what EF6 does.

(I was leaving this to discuss in triage. Regardless of the answers to the questions, I think we need to provide some guidance here, and it's not clear to me what that guidance should be.)

[vitalybibikov]

@ajcvickers The problem is that in 2.2 Ef Core it was possible to Include such thing as:

Question
|
|____Category
|
|
-------- Template

Where template Includes all the Categories, that we want to process, in this case - process and then soft delete.

.Include(c => c.Category.Template.Categories)

Currently it results in a cycle if query is No-Tracking, as Template also includes the Category, that belongs to a question, from which we are started the tree.

• Why are you including cycles? If your purpose is to have Questions populated then EF Core does that already. When you load Category navigation, EF Core also populate inverse navigation as a part of fix-up.

I haven't checked it in this version, but Questions were not loaded as a part of Categories resolution in previous version we used.

[ajcvickers]

Note from triage: @smitpatel will show a workaround for no-tracking queries.

[ajcvickers]

Ping @smitpatel

[ajcvickers]

Verified same behavior with EF6:

Unhandled exception. System.InvalidOperationException: The RelatedEnd with role name 'Category_Questions_Source' from relationship 'CodeFirstNamespace.Category_Questions' has already been
loaded. This can occur when using a NoTracking merge option. Try using a different merge option when querying for the related object.
   at System.Data.Entity.Core.Objects.ObjectStateManager.UpdateRelationships(ObjectContext context, MergeOption mergeOption, AssociationSet associationSet, AssociationEndMember sourceMember, IEntityWrapper wrappedSource, AssociationEndMember targetMember, IList targets, Boolean setIsLoaded)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.FullSpanAction[TTargetEntity](IEntityWrapper wrappedSource, IList`1 spannedEntities, AssociationEndMember targetMember)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.<>c__DisplayClass12_0`1.<HandleFullSpanCollection>b__0(Shaper state, List`1 spannedEntities)
   at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ResetCollection(Shaper shaper)
   at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ResetCollection(Shaper shaper)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.RowNestedResultEnumerator.MoveNext()
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.ObjectQueryNestedEnumerator.TryReadToNextElement()
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.ObjectQueryNestedEnumerator.ReadElement()
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.ObjectQueryNestedEnumerator.MoveNext()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<>c__14`1.<GetElementFunction>b__14_3(IEnumerable`1 sequence)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Single[TSource](IQueryable`1 source, Expression`1 predicate)
   at Program.Main() in C:\Stuff\EF6Core\EF6Core\Program.cs:line 64

[ajcvickers]

Pinging @smitpatel

[ajcvickers]

Pinging @smitpatel

[smitpatel]

Model

    public class Blog
    {
        public int Id { get; set; }
        public List<Post> Posts { get; set; }
    }

    public class Post
    {
        public int Id { get; set; }
        public Blog Blog { get; set; }
    }

Query:

var posts = db.Set<Post>().AsNoTracking().Include(p => p.Blog.Posts).Where(p => p.Id == 1).ToList();
// should be written to include from blog side
                var query = db.Set<Post>().Where(p => p.Id == 1);
                var blogs = db.Set<Blog>().Include(b => b.Posts).AsNoTracking()
                    .Where(b => query.Select(p => p.Blog.Id).Contains(b.Id))
                    .ToList();
                var posts = blogs.SelectMany(b => b.Posts).Where(p => p.Id == 1).ToList();

[offirpeer]

I have the following scenario which worked fine in .net core 2.2 but in .net core 3.x and .net 5 I get the error:
Cycles are not allowed in no-tracking queries

I have a location table with parent location, for example:
I have a page for Medellin which is a city in Colombia, and I want to get all the other cities in Colombia as well, so in .net core 2.2 I would do this:

location.Include(x => x.Parent).ThenInclude(x => x.InverseParent)
So the location is Medellin which is a city includes Colombia and all other cities under Colombia.

Now it doesn't work and I need to change all of queries.

[smitpatel]

@offirpeer - EF Core stopped doing identity resolution for no tracking query since 3.x. See (breaking change)[https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.x/breaking-changes#notrackingresolution]
Without it there is no way to generate a consistent graph when there are cycle since each instance would be materialized differently in results. In EF Core 2.2 while it worked upto an extent, it was not always accurate either (subject to garbage collection else you would get inconsistent results there too). In EF 6 this scenario threw exception. Hence we blocked scenario rather than giving intermittent inconsistent results.

If you want to do cycles in include then either do a tracking query or from 5.0 onwards you can also use AsNoTrackingWithIdentityResolution to go back to 2.2 behavior which will always work.