I'm currently working on an API to manage loots (and other stuff eventually) for FFXIV and I'm facing an issue now that when I try to get the Roles from the database, it returns null for a list of Jobs.

These are my model classes:

public class Job
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
    public bool Active { get; set; }
    public int RoleId { get; set; }
    public virtual Role Role { get; set; }
}

public class Role
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
    public bool Active { get; set; }
    public virtual ICollection<Job> Jobs { get; set; }
}

And this is the current state of my DbContext implementation:

public class FFContext : DbContext
{
    public FFContext()
    {
    }

    public FFContext(DbContextOptions<FFContext> context) : base (context)
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder builder)
    {
        builder.UseMySql(@"server=*;database=FFXIVManager;uid=*;password=*", ServerVersion.AutoDetect(@"server=*;database=FFXIVManager;uid=*;password=*"));
    }

    #region datasets
    public DbSet<Job> Job { get; set; }
    public DbSet<Player> Player { get; set; }
    public DbSet<Role> Role { get; set; }
    public DbSet<Team> Team { get; set; }
    #endregion
}

I have already tried using Fluent API to fix it, adding this override to my context with no success:

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Role>()
            .HasMany(y => y.Jobs)
            .WithOne(x => x.Role)
            .HasForeignKey(x => x.RoleId);
    }

What am I missing out here?

Edit: forgot to add repositories

I have an interface IBaseRepository, this is it:

public interface IBaseRepository<T> where T : class
{
    Task<IEnumerable<T>> GetAllAsync();
    Task<T> GetById(int id);
    Task AddAsync(T obj);
    Task UpdateAsync(T obj);
    Task DeleteAsync(int id);
}

And also a IRoleRepository interface, which inheritsIBaseRepository

public interface IRoleRepository : IBaseRepository<Role>
{
}

public class BaseRepository<TEntity> : IBaseRepository<TEntity> where TEntity : class
{
    protected readonly FFContext _context;

    public BaseRepository(FFContext context)
    {
        _context = context;
    }

    public async Task AddAsync(TEntity obj)
    {
        await _context.Set<TEntity>().AddAsync(obj);
        await _context.SaveChangesAsync();
    }

    public async Task DeleteAsync(int id)
    {
        var obj = await GetById(id);
        _context.Set<TEntity>().Remove(obj);
        await _context.SaveChangesAsync();
    }

    public async Task<IEnumerable<TEntity>> GetAllAsync()
    {
        return await _context.Set<TEntity>().AsNoTracking().ToListAsync();
    }

    public async Task<TEntity> GetById(int id)
    {
        return await _context.Set<TEntity>().FindAsync(id);
    }

    public async Task UpdateAsync(TEntity obj)
    {
        _context.Set<TEntity>().Update(obj);
        await _context.SaveChangesAsync();
    }
}

And this is the RoleRepository class:

public class RoleRepository : BaseRepository<Role>, IRoleRepository
{
    public RoleRepository(FFContext context) : base(context)
    {
    }
}

Also, this is the result from my Postman request:

{
    "id": 1,
    "name": "Tank",
    "active": true,
    "jobs": null
},
{
    "id": 2,
    "name": "Healer",
    "active": true,
    "jobs": null
},
{
    "id": 3,
    "name": "Magical Ranged DPS",
    "active": true,
    "jobs": null
},
{
    "id": 4,
    "name": "Melee DPS",
    "active": true,
    "jobs": null
},
{
    "id": 5,
    "name": "Physical Ranged DPS",
    "active": true,
    "jobs": null
}

I expected "jobs" to have the list of every job registered in the database

CodePudding user response：

I had a similar issue not long ago. To perform what you are looking for in your OnModelCreating.

Change:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<Role>()
        .HasMany(y => y.Jobs)
        .WithOne(x => x.Role)
        .HasForeignKey(x => x.RoleId);
}

To:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<Role>()
        .Navigation(x => x.Jobs)
        .AutoInclude()
        .UserPropertyAccessMode(PropertyAccessMode.Property);
}

This tells EF that you want to include the list of Jobs whenever you query for a Role.

CodePudding user response：

You need to include what child tables you want to query. This can be done by using the .Include() method on your linq query. Seeing you have a generic repository, it isn't possible to use this, so you need to override your "find" or "get" methods.

public override async Task<IEnumerable<Role>> GetAllAsync()
{
    return await _context.Set<Role>()
     .Include(role => role.Jobs)
     .AsNoTracking()
     .ToListAsync();
}