CODEWITHSUNDEEP
entity-frameworklinqentity-framework-core
lucas
lucas

Reputation: 4685

Query does not return child collections

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();    
}

Upvotes: 0

Views: 1451

Answers (3)

Ivan Stoev
Ivan Stoev

Reputation: 205619

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();    
}

Upvotes: 3

shady youssery
shady youssery

Reputation: 440

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

Upvotes: 0

OJ Raque&#241;o
OJ Raque&#241;o

Reputation: 4561

Try this:

public IEnumerable<Category> GetAllForUser(string name)
{
    return _ctx.Users
                .Include(u => u.Categories)
                .Include(u => u.Categories.Select(c => c.Tasks))
                .Where(x => x.UserName == name)
                .SelectMany(x => x.Categories)
                .ToList();    
}

Upvotes: 0

Related Questions