EF Core 3 not loading related data in second or deeper level

c# entity-framework entity-framework-6 entity-framework-core

Question

I'm having a problem loading data from a second level entity using EF Core 3 but it works as expected with EF6 but I'm converting a project to try to migrate the lot to .NET Core, including EF.

I'm not sure if this is the right way to express this but in short, I have a list of companies that have users and these users have roles and for some reason, I can load the companies and their relevant users from a many to many relationship but the roles for each user are not being loaded.

I have the following Generic function:

protected virtual IQueryable<TEntity> GetQueryable(
    Expression<Func<TEntity, bool>> filter = null,
    Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
    string includeProperties = null,
    bool isCollection = false,
    int? skip = null,
    int? take = null)
{
    IQueryable<TEntity> query = this.Context.Set<TEntity>();

    if (filter != null)
    {
        query = query.Where(filter.Expand());
    }

    if (includeProperties != null)
    {
        query = includeProperties
            .Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
            .Aggregate(query, (current, include) => current.Include(include));
    }

    if (orderBy != null)
    {
        query = orderBy(query);
    }

    if (skip.HasValue)
    {
        query = query.Skip(skip.Value);
    }

    if (take.HasValue)
    {
        query = query.Take(take.Value);
    }

    query = query.AsExpandableEFCore();

    return query;
}

and I call it as follows:

var databaseCompanies = await this.UnitOfWork.Companies
       .GetAllAsync(null, "Companies.User.Roles");

And my entities are defined as follows:

Company:

public class Company: Entity<Guid>
{
    private ICollection<CompanyUsers> _companyUsers;

    public virtual ICollection<CompanyUsers> CompanyUsers
    {
        get => this._companyUsers ?? (this._companyUsers = new HashSet<CompanyUsers>());
        set => this._companyUsers = value;
    }
}

User:

public class User : Entity<Guid>
{
    private ICollection<CompanyUsers> _companyUsers;
    private ICollection<UserRole> _roles;

    public virtual ICollection<UserRole> Roles
    {
        get => this._roles ?? (this._roles = new HashSet<UserRole>());
        set => this._roles = value;
    }

    public virtual ICollection<CompanyUsers> CompanyUsers
    {
        get => this._companyUsers ?? (this._companyUsers = new HashSet<CompanyUsers>());
        set => this._companyUsers = value;
    }
}

Role:

public class UserRole
{
    public Guid UserId { get; set; }

    public RoleType Role { get; set; }

    public User User { get; set; }
}

Class to define the many to many relation:

public class CompanyUsers
{
    public Guid UserId { get; set; }

    public User User { get; set; }

    public Guid CompanyId { get; set; }

    public Company Company { get; set; }
}

As you can see, the Company and User classes have a many to many relation defined by CompanyUsers and there is a one to many relationship between the User the the UserRole classes. When calling:

var databaseCompanies = await this.UnitOfWork.Companies
       .GetAllAsync(null, "Companies.User");

It only loads the companies and the users, so I tried this instead:

var databaseCompanies = await this.UnitOfWork.Companies
       .GetAllAsync(null, "Companies.User.Roles");

or

var databaseCompanies = await this.UnitOfWork.Companies
      .GetAllAsync(null, "Companies.User,Companies.User.Roles");

But none of them work and the roles for the users belonging to the companies never get loaded.

In the last example, it will add 2 .Include but when looking at the query variable but I noticed that expression has 2 arguments but they are different of different type:

1) {value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Data.Entities.Company])} 2) "Company.User.Roles"

Again, if I load only one level below the main entity such as load a specific user, it will load its roles accordingly:

var databaseUser = await this.UnitOfWork.Users
      .GetFirstAsync(u => u.Username == username, null, "Roles", true);

Any ideas on how to resolve this?

Thanks.

UPDATE:

As mentioned below, I really wish I could swear right now!! The penny dropped after noticing that the query generated by EF Core was correct and returned the role as part of the query but they were all NULL.

I went to check my Roles table and that's when I realized that it had been wiped when I rolled back a migration regarding roles in order to fix the relationship and I never re-generated the dummy roles that were associated with all the users of the companies I had defined!!

Apologies to all of you who helped!!

1
0
12/27/2019 11:42:28 PM

Popular Answer

In your GetQueryable method parameters replace string includeProperties = null with Func<IQueryable<T>, IIncludableQueryable<T, object>> includes == null and then in the method body update the code as follows:

if (includes != null)
{
    query = includes(query);
}

Now when calling the GetQueryable method, pass the value of includes as follows:

sp => sp.Include(i => i.FirstLevel).ThenInclude(f => f.SecondLevel)

Job done! Now It should work as expected!

0
12/27/2019 4:41:00 AM


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