Building ASP.NET Core API that expose data from existing db. All required entities based on data from few tables, so created a view for each. API output - JSON with entities hirerachy, created using includes between DbSets.
There is parent entity Projects, which includes child Components, which includes Users, and some more, but they are not part of the porblem. Simplified DTOs:
public class RrojectDTO
{
public int projectId { get; set; }
public string componentCode { get; set; }
public virtual ICollection<ComponentDTO> componentsArray { get; set; }
}
public class ComponentDTO
{
public Guid componentId { get; set; }
public virtual RrojectDTO Project { get; set; }
public string componentCode { get; set; }
public virtual ICollection<UserDTO> usersArray { get; set; }
...
}
public class UserDTO
{
public int userId { get; set; }
public virtual ComponentDTO Component { get; set; }
public Guid componentId { get; set; }
...
}
Here is how DBContext class looks like:
public MyDbContext(DbContextOptions options)
: base(options)
{
ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTrackingWithIdentityResolution;
}
public virtual DbSet<ProjectDTO> Projects{ get; set; }
public virtual DbSet<ComponentDTO> Components { get; set; }
public virtual DbSet<UserDTO> Users { get; set; }
....
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<ProjectDTO>(entity =>
{
entity.HasKey(f => f.componentCode);
entity.ToView("ProjectsArrayView", "LAB");
entity.HasMany(p => p.componentsArray).WithOne(t => t.Project)
.HasForeignKey(t => t.componentCode);
});
modelBuilder.Entity<ComponentDTO>(entity =>
{
entity.HasKey(f => f.componentId);
entity.ToView("ComponentArrayView", "LAB");
entity.HasMany(p => p.usersArray).WithOne(t => t.Component)
.HasForeignKey(t => t.componentId);
....
});
modelBuilder.Entity<UserDTO>(entity =>
{
entity.HasKey(f => f.userId);
entity.ToView("UsersArrayView", "LAB");
...
});
I'm connection this entities with next code:
`var projects = context.Projects.Where(p => p.projectId == Guid.Parse("9C373F8F-CAB8-4914-83D9-4864B2E3E7B3")).Include(co => co.componentsArray).ThenInclude(j => j.usersArray).ToList();`
Originally there is more includes to project, but other seems to be working fine. While one on the example does not work as expected, for example: there is one project, that have 3 components, same 2 users(User1 and User2) are working on each component, If you query each dbSet by key parameters, you should be able to see correspondent data. project output: 1 project -> with 3 components -> each component contains only 1 user instead of two.
My question is: Why .ThenInlcude() is adding only User1 per component, but ignoring User2 ? what am i missing here ?
If I query only components DbSet and Include(x=> x.usersArray) - result still the same, User1 per component. Also, if I'm adding User3, User4 - it is still User1 that populated.
When i found query via SQL profiler, it did contained both users (can't share query due to policies). Went over like 20 articles, but I think I'm missing something basic here, please let me know if i can add something to make this qeustion better.