CODEWITHSUNDEEP
c#asp.net-coreentity-framework-core
Felipe Endlich
Felipe Endlich

Reputation: 620

Query many-to-many related data using Entity Framework Core

I have two entities that have a many-to-many relationship. I also have an entity between then that holds the two ID's. I want to load only the two main entities.

My entities:

public class User 
{
    public long Id { get; set; }
    public string Email { get; set; }
    public virtual ICollection<UserRole> UserRoles { get; set; }
}

public class Role 
{
    public long Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<UserRole> UserRoles { get; set; }
}

public class UserRole 
{
    public long UserId { get; set; }
    public User User { get; set; }
    public long RoleId { get; set; }
    public Role Role { get; set; }
}

Expected JSON result:

{
  "Email": ...
  "Roles": [
    {"Name": ...},
    {"Name": ...},
    ...
  ] 
}

Upvotes: 1

Views: 116

Answers (1)

Chris Pratt
Chris Pratt

Reputation: 239260

EF Core doesn't technically support M2M relationships. You can fake said support via a "join" entity, which connects the two sides of the relationship, as you have here with UserRole. There's no way around this, unfortunately, so if you don't want to see the UserRole relationship in your results, you'll have to manually reshape the data. For example:

var users = await _context.Users.Select(u => new
{
    Email = u.Email,
    Roles = u.UserRoles.Select(r => r.Role)
}).ToListAsync();

Pro Tip: You don't need Include if you're selecting from relationships. EF Core is smart enough to issue joins in order to fill the data.

Upvotes: 2

Related Questions