Query does not return child collections

entity-framework entity-framework-core linq

Question

I still struggle with this, why each of 'Category' items returns null 'Task' collections. I do have data in the database, what am I missing?

public class ApplicationUser : IdentityUser
{
    public ICollection<Category> Categories { get; set; }
}

public class Category
{
    public int CategoryId { get; set; }
    public string Name { get; set; }
    public DateTime Timestamp { get; set; }

    public ICollection<Task> Tasks { get; set; }
}

public class Task
{
    public int TaskId { get; set; }
    public string Name { get; set; }
    public DateTime Timestamp { get; set; }
}

And here is the query:

public IEnumerable<Category> GetAllForUser(string name)
{
    return _ctx.Users.Where(x => x.UserName == name)
                     .SelectMany(x => x.Categories)
                     .Include(x => x.Tasks).ToList();    
}
1
0
8/9/2016 9:22:41 AM

Accepted Answer

Your query is falling into Ignored Includes case:

If you change the query so that it no longer returns instances of the entity type that the query began with, then the include operators are ignored.

As explained in the link, if you add the following to your DbContext OnConfiguring:

optionsBuilder.ConfigureWarnings(warnings => warnings.Throw(CoreEventId.IncludeIgnoredWarning));

then instead null collection you'll get InvalidOperationException containing something like this inside the error message:

The Include operation for navigation: 'x.Tasks' was ignored because the target navigation is not reachable in the final query results.

So how to fix that? Apparently the requirement is to start the query from the entity for which you want to add includes. In your case, you should start from _ctx.Categories. But in order to apply the same filter, you need to add the reverse navigation property of the Application.Users to the Category class:

public class Category
{
    // ...
    public ApplicationUser ApplicationUser { get; set; }
}

Now the following will work:

public IEnumerable<Category> GetAllForUser(string name)
{
    return _ctx.Categories
        .Where(c => c.ApplicationUser.UserName == name)
        .Include(c => c.Tasks)
        .ToList();    
}
3
8/9/2016 9:21:59 AM

Popular Answer

public virtual ICollection<Task> Tasks { get; set; }


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow