Efcore: Include->ThenInclude for a collection

[sitefinitysteve]

Steps to reproduce

This could be any structure, basically an object which has a collection as a nav property

var educator = this.Context.Educators.Include(x => x.Studies) //List<Study>
                                                 .Include(x => x.Sites) //List<Site>
                                                 .Single(x => x.Id == id);

This returns what I want, however if I then try to loop through

 @foreach (var s in Model.Sites)
{
                                        <tr>
                                            <td>
                                                @s.Site.Name //WILL CRASH
                                                @s.SiteId //JUST FINE
                                            </td>
                                        </tr>
}

The issue

Every example for Include->ThenInclude seems to assume the nav properties are simple objects not collections. In the "ThenInclude" how to we populate each array items objects?

-- No Exception, outside of NULL REF --

Further technical details

EF Core version: 1.0.1
Operating system: Win10
Visual Studio version: 2015

Other details about my project setup:

[smitpatel]

The query written above will populate Studies & Sites navigations for educator but in the for loop you are trying to access s.Site.Name. If the Site is navigation property then it won't be loaded as it wasn't specified during the query so null ref is expected. You need to use ThenInclude for that.

Every example for Include->ThenInclude seems to assume the nav properties are simple objects not collections. In the "ThenInclude" how to we populate each array items objects?

There is no such assumption. Rather when navigation is collection, to load nav props for each collection item ThenInclude is required. See the examples at https://ef.readthedocs.io/en/latest/querying/related-data.html#including-multiple-levels

[sitefinitysteve]

I suppose this is what the problem is, how would you structure a ThenInclude for a collection?

Example:

var data = context.Blogs.Include(x => x.Post).ThenInclude(x => x.Author)

So the second nested level is just another simple object, easy...but how would you operate on a collection on that level to say like "For each study in studies, ThenInclude it's site object"

[smitpatel]

@sitefinitysteve - Can you share the classes (only navigation properties needed) and which are the navigations you want to eager load?

[rowanmiller]

Here is the code you want

this.Context.Educators.Include(x => x.Studies).ThenInclude(x => x.Sites)

[sitefinitysteve]

@rowanmiller I'm sorry I dont think I'm explaining this properly

(non-important props removed)

    public class Educator
    {
        public virtual List<StudyEducator> Studies { get; set; }
    }

So yes this:

this.Context.Educators.Include(x => x.Studies)

will give me a list of StudyEducator items

However each StudyEducator object has navigation properties as well

    public class StudyEducator
    {
        public int StudyId { get; set; }
        public virtual Study Study { get; set; }

        public int EducatorId { get; set; }
        public virtual Educator Educator { get; set; }
    }

So a ThenInclude is operating on the List and you get this

Where instead I want to say "Foreach StudyEducator hydrate it's Study nav property as well"

[rowanmiller]

@sitefinitysteve that's a bug in IntelliSense (we've passed it to the IntelliSense team to look at). The compiler handles it just fine though, so you'll just need to type out that code without using IntelliSense.

[sitefinitysteve]

@rowanmiller Amazing... you have no idea the time I burned on this one, OY... thanks 👍

[divega]

Intellisense bug is https://github.com/dotnet/roslyn/issues/8237. @sitefinitysteve feel free to post your feedback there.

[hawkpgl]

Yeah I hit the same problem. I'm on VS 2017 and .Net core 1.1.1. If that intellisense gets corrected would be great.

[DMW007]

Still an issue in Visual Studio 2017 Version 15.2 (26430.12). Intellisense suggest me that we've a list. When I _blind type_ the attribute from the foreign relation, the IDE get it. But it's required to type the attribute manually without intellisense. Would be nice to have this fixed in VS, since Intellisense is a great feature of VS.

[divega]

@DMWOO7 a fix has been delayed to a future version. Please provide any feedback in the Roslyn issue linked above.

[manojgarg]

@rowanmiller you saved my day

[ryansealy]

Yes please fix this. While it's frustrating to deal with Intellisense that won't come up, it's completely different when the Intellisense is wrong.

We trust the Intellisense. It has to be right.

I ran into this today. Thanks for the great question and answers here.

[ajcvickers]

@ryansealy We appreciate the feedback, but please comment on https://github.com/dotnet/roslyn/issues/8237. That is the issue that needs to get fixed for this to work.

[abbradar]

I encountered this with .NET Core 2.0 on Linux and EF Core 2.0, in F# -- but during compilation, not auto-completion.

schema (most fields omitted):

    public class Schema
    {
        public List<Entity> Entities { get; set; }
    }

    public class Entity
    {
        public int? SchemaId { get; set; }
        public Schema Schema { get; set; }
        public List<Field> Fields { get; set; }
    }

    public class Field
    {
        [Required]
        public int EntityId { get; set; }
        public Entity Entity { get; set; }
    }

code (type annotation is given because otherwise the error is about unknown field):

    let q = db.Schemas
                .Include(fun sch -> sch.Entities)
                .ThenInclude(fun (ents : Entity) -> ents.Fields)
                .ToList()

error:

/home/app/project/FunDB/src/FunQL/Meta.fs(98,35): error FS0001: This expression was expected to have type    'System.Collections.Generic.List<Entity>'    but here has type    'Entity' [/home/app/project/FunDB/FunDB.fsproj]

[ajcvickers]

@abbradar This seems like a different issue from the one discussed here. Can you please file a new issue together with complete code listing or project to reproduce what you are seeing?

[manchoz]

@abbradar @ajcvickers F# is not able to automatically pick the overload method with following signature (please, note the IEnumerable<TPreviousProperty> for the type returned from previous .Include() call):

ThenInclude<TEntity,TPreviousProperty,TProperty(IIncludableQueryable<TEntity,IEnumerable<TPreviousProperty>>,Expression<Func<TPreviousProperty,TProperty>>)

which would convert from List<TEntity> to TEntity for chained ThenInclude.

I suppose that this happens because F# won't automatically upcast List<T> to IEnumerable<T>. Explicitly upcasting the .Include() lambda to IEnumberable<T> seems to resolve the issue:

let q =
    db.Schemas
       .Include(fun sch -> sch.Entities :> IEnumerable<_>)
           .ThenInclude(fun (ents : Entity) -> ents.Fields)
       .ToList()

Hope this helps!

[irensaltali]

@rowanmiller I spend tons of hours because of that IntelliSense bug.

[Programm3r]

@rowanmiller Many thanks for the info, it really helped - one would think that after two years this would be fixed 😕

[devsoitic12]

This got me today too. =/

[MOO2Extractor]

Me too today. Two+ year old Intellisense bug isn't fixed?

[DMW007]

It's a shame that this issue got closed and nobody from MS, the reputed world wide leading software company (?) care about fixing this in a expensive IDE... 👎

[sitefinitysteve]

It's the wrong repo for the issue to be in... Still would be nice to be fixed SOMEWHERE.

[ajcvickers]

@DMW007 The issue is tracked here: https://github.com/dotnet/roslyn/issues/8237 That's where comments need to be made to reach the people who could fix this.

[DMW007]

@sitefinitysteve You're right, but since VS is only proprietary software, we don't have a github repo to report/discuss the bug. And we didn't get any fix or at least feedback from @rowanmiller about the progress. He said that the bug was passed to the IntelliSense team, but it doesn't seem that they care about this annoying bug. I don't want to know how much time was wasted on this bug...

[nyolamike]

OMG, this thing wasted hours and even spoiled my plans for a very important presentation, this is really not good people

[whitebear-gh]

@manchoz Thanks - you saved my day! :)

[kvijayan-aliera]

I'm working on EntityFramework Core 2.2. And still unable to use "ThenInclude" for a collection

ICollection <Order> Orders;

.Include(member => member.Orders)   
                .ThenInclude(ords => ords.Select(o => o.OrderSubCategory))
                .ThenInclude(oc => oc.OrderCategory),

Tried the above code to include an object OrderSubCategory within a collection property Orders. This throws an exception. Does anyone have a work around for this ?

[irensaltali]

@kvijayan-aliera I don't think you can use .Select in .ThenInclude.

[kvijayan-aliera]

@kvijayan-aliera I don't think you can use .Select in .ThenInclude.

You can use .Select in .ThenInclude my VS2017 IDE does not red squiggly on this line. However it throws an exception stating

The expression should represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, specify an explicitly typed lambda parameter of the target type, E.g. '(Derived d) => d.MyProperty'.

Do you happen to have a work around to use "ThenInclude" for a collection or include objects within the items in a collection?

[ajcvickers]

@kvijayan-aliera Red squigglies in Visual Studio in this case may not mean the code is wrong. It likely just means Visual Studio doesn't understand it, even though it will compile and run. See https://github.com/dotnet/roslyn/issues/8237 which will (finally) fix this for VS 2019 16.1

[kvijayan-aliera]

@ajcvickers Thanks for the references! I'm using VS 2017 Pro and I'm able to use .ThenInclude on collections in spite of VS intellisense failing to show properties underlying the collection object.

[kvijayan-aliera]

@ajcvickers One other question? I find that I'm unable to use a where clause on .ThenInclude object property?

await memberRepository.GetPagedListAsync(
selector: a =>a, 
predicate: m => m.MemberId,
include: src => src
            .Include(m => m.Orders)
            .Where(m => m.Status == 1)
                .ThenInclude(ord => ord.OrderCategory)
                .Where(ord => ord.OrderCategoryId == "<someIdentity>")

The where clause always considers the predicate on the MemberRepository and does not allow me to query the Orders like the line of code below
.Where(ord => ord.OrderCategoryId == "<someIdentity>")

Would it be possible to query the OrderCategory in the ThenInclude?

[ajcvickers]

@kvijayan-aliera #1833

[wrkettlitz]

Still bugged in version 3.0 preview 6

[GerardoSero]

I encountered this with .NET Core 2.0 on Linux and EF Core 2.0, in F# -- but during compilation, not auto-completion.

schema (most fields omitted):

    public class Schema
    {
        public List<Entity> Entities { get; set; }
    }

    public class Entity
    {
        public int? SchemaId { get; set; }
        public Schema Schema { get; set; }
        public List<Field> Fields { get; set; }
    }

    public class Field
    {
        [Required]
        public int EntityId { get; set; }
        public Entity Entity { get; set; }
    }

code (type annotation is given because otherwise the error is about unknown field):

    let q = db.Schemas
                .Include(fun sch -> sch.Entities)
                .ThenInclude(fun (ents : Entity) -> ents.Fields)
                .ToList()

error:

/home/app/project/FunDB/src/FunQL/Meta.fs(98,35): error FS0001: This expression was expected to have type    'System.Collections.Generic.List<Entity>'    but here has type    'Entity' [/home/app/project/FunDB/FunDB.fsproj]

In C# you could try with

var q = db.Schemas
                 .Include(s => s.Entities)
                 .ThenInclude<Schema, Entity, Field>(e -> e.Fields)

[hammadsajid786]

Steps to reproduce

This could be any structure, basically an object which has a collection as a nav property

var educator = this.Context.Educators.Include(x => x.Studies) //List<Study>
                                                 .Include(x => x.Sites) //List<Site>
                                                 .Single(x => x.Id == id);

This returns what I want, however if I then try to loop through

 @foreach (var s in Model.Sites)
{
                                        <tr>
                                            <td>
                                                @s.Site.Name //WILL CRASH
                                                @s.SiteId //JUST FINE
                                            </td>
                                        </tr>
}

The issue

Every example for Include->ThenInclude seems to assume the nav properties are simple objects not collections. In the "ThenInclude" how to we populate each array items objects?

-- No Exception, outside of NULL REF --

Further technical details

EF Core version: 1.0.1
Operating system: Win10
Visual Studio version: 2015

Other details about my project setup:

I was having Same issue
I am using Visual Studio 2019 Enterprise Version 16.3.0

[jmoralesv]

This still happens on Visual Studio 2019, version 16.4, with .NET Core 3.1 and EF Core 3.1. It's incredible that I wasted a lot of time in a bug that still exists on Intellisense more than 4 years ago.
Unbelievable.

[wrkettlitz]

The least they could have done is to describe the limitation on the methods xml comment.

[rozek1szymon]

issue still exist, get it done eventually :/

[roji]

For anybody still running into this, can you please try upgrading to Visual Studio 16.5? According to https://github.com/dotnet/roslyn/issues/41527#issuecomment-584296900 it should be fixed there.

[MhozaifaA]

i'am using VS 16.6.2 , issue still , some collection(dbset) Include ( other collection ) ThenInclude ( !! ) shown like list of the object , until put the object my hand

[arilishu]

Microsoft Visual Studio Community 2019 Version 16.4.1

Same problem....
I have a Lead who was a List.
A Vehicle has a List
When i try to bring all the information, i see the same as the rest

Any idea? Is this maintained? Thanks!