Web API and Entity Framework with a 1 to many to many relationship

Question

I need help on how to get a data from a one to many. I'm using .NET Core 3.1 Web API with Entity Framework.

Table 1 : User

ID Guid
UserName String

Table 2 : UsererAgency

UserId GUID
AgencyCode String

Table 3 Agency

AgencyCode string
AgencyName String

The path is from table User, column ID to UserAgency.UserID and from UserAgency to Agency using the AgencyCode column.

I want to get all the users and their Agencies. So I want to return just:

• ID from User
• AgencyCode and AgencyName from Agency table.

Here are my POCO's that I'm using to get data back form entity framework.

User

public User()
{
    public User()
    {
        UserAgency = new HashSet<UserAgency>();
    }

    [NotMapped]
    public GUID ID { get; set; }
       
    [NotMapped]
    public virtual ICollection<UserAgency> UserAgency { get; set; }
}

public class UserAgency
{
    [Key]
    public Guid UserId { get; set; }
    [Key]
    [ForeignKey("Agency")]
    public string AgencyCode { get; set; }
    [NotMapped]
    public virtual Agency Agency { get; set; }
    [NotMapped]
    public virtual User User { get; set; }
}
  
public partial class Agency
{
    public Agency()
    {
        Employee = new HashSet<Employee>();
        UserAgency = new HashSet<UserAgency>();
        User = new HashSet<ApplicationUser>();
    }

    public string AgencyCode { get; set; }
    publicstring AgencyName { get; set; }

    [NotMapped]
    public virtual ICollection<UserAgency> UserAgency { get; set; }

    [NotMapped]
    public virtual ICollection<User> User { get; set; }
} 

var members = await _unitOfWork.UserRepository.FindAllAsync(null, null, new List<string> {"UserAgency" });

It is returning my data correctly. Returning all my Users (which is correct) and all the UserAgency with all their Agency under Useragency. I guess I don't know how to drill down or how to format my DTO. Would I use AutoMapper or could I select what I need?

Answer 1

There's no _unitOfWork, UserRepository or FindAllAsync in EF Core. It looks like you used the "generic repository" antipattern that typically breaks EF operations, disabling the real UoW functionality and LINQ querying. Gunnar Peipman's No need for repositories and unit of work with Entity Framework Core explains in detail why this is a bad idea.

DbContext is already a multi-entity Unit-of-Work. DbSet is already a single-entity Repository. There's no need for a "generic repository" in EF Core, because EF already provides higher-level abstractions. What does make sense though, are specific repositories that address specific use cases/scenarios/bounded contexts (if you prefer DDD terminology).

In EF Core, to return just the Agent ID and the Agency data you'd need just :

var query=from user in dbContext.Users
            from ua in user.UserAgency 
            select new {
                UseID = user.ID, 
                AgencyCode = ua.Agency.AgencyCode,
                AgencyName = ua.Agency.AgencyName };
var results=await query.ToListAsync();

Anonymous types can't be used as return types though. You could return a tuple instead, but a far cleaner solution would be to define and use a record with the fields you want :

public record AgencyWithUser(Guid UserID,string AgencyCode,string AgencyName);

....
var query=from user in dbContext.Users
            from ua in user.UserAgency 
            select new AgencyWithUser(
                user.ID, 
                ua.Agency.AgencyCode,
                ua.Agency.AgencyName);
var results=await query.ToListAsync();

If this is a common operation, the code can be added as a function to your DbContext. This could be a good idea even if the call isn't that common, to hide the query from higher-level code :

public class MyAgenciesContext: DbContext
{

....

    public async Task<List<AgencyWithUser>> GetAgenciesWithUsersAsync()
    {
        var query=from user in this.Users
            from ua in user.UserAgency 
            select new AgencyWithUser(
                user.ID, 
                ua.Agency.AgencyCode,
                ua.Agency.AgencyName);
        var results=await query.ToListAsync();
    }
}

You could also create a specialized repository with methods that fulfill the requirements of specific use cases. This query wouldn't be any different :

public MyAgencyManagementRepository
{

    readonly MyContext _dbContext;

    public MyAgencyManagementRepository(MyContext dbContext)
    {
        _dbContext=dbContext;
    }

    public async Task<List<AgencyWithUser>> GetAgenciesWithUsersAsync()
    {
        var query=from user in _dbContext.Users
            from ua in user.UserAgency 
            select new AgencyWithUser(
                user.ID, 
                ua.Agency.AgencyCode,
                ua.Agency.AgencyName);
        var results=await query.ToListAsync();
    }

    public async Task<List<AgencyWithUser>> GetAgenciesForUserAsync(Guid userId)
    {
        var query=from user in _dbContext.Users
            where user.Id == useId
            from ua in user.UserAgency 
            select new AgencyWithUser(
                user.ID, 
                ua.Agency.AgencyCode,
                ua.Agency.AgencyName);
        var results=await query.ToListAsync();
    }
}

Not that different from putting the method to the DbContext. The real benefits of the repository though come from encapsulating complex operation needed for specific scenarios.

For example :

• For reporting you'd need only querying functions, often combining multiple entities, aggregations, complex filtering. There's no need for transactions, validation or concurrency management. There may be a need for dynamic filtering though.

• For agency management, you need basic CRUD operations that can also handle mapping to users. Validation is needed, and your code will have to handle concurrency issues.

Even though both scenarios can use the same DbContext, they have different needs, require different code and change for different reasons. They need different unit tests and may even be developed by different developers.

In such a case it would make sense to create two separate specialized repositories

Answer 2

With EntityFramework, you can try like this:

_unitOfWork.UserRepository.FindAllAsync().Include(i => i.UserAgency).ThenInclude(i => i.Agency).Select(i => new { i.ID, i.UserAgency.Agency.AgencyCode, i.UserAgency.Agency.AgencyName  
});