Query on many-to-many relationship Users - Classes

Question

I have two entities, Users and Classes, with a many to many relationship. I'm working with .Net Core, and I set up the relationship following this blog post HERE (up to part 3). The database works, and I'm able to add classes and fill the join table, but I don't know how to retrieve all the classes the user is enrolled in, or the other way around all the users that are enrolled in a specific class.

I need that to solve the case in which the user is already signed into a class and I don't have to add a new entry to the join table (which would still throw an error).

User.cs

public class User : IdentityUser
{
    // Making the classes a JoinCollectionFacade allows us to perform actions like adding a class
    public User()
        => Classes = new JoinCollectionFacade(
            UserClass,
            uc => uc.Class,
            c => new UserClass { User = this, Class = c });

    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Type { get; set; }
    //public string Email { get; set; }
    public string Phone { get; set; }
    public string Membership { get; set; }

    private ICollection UserClass { get; } = new List();

    // This element does not need to be mapped. It's used for easier reference
    // to the Users enrolled in the class
    [NotMapped]
    public ICollection Classes { get; }
}

Class.cs

public class Class
{

    // Making the classes a JoinCollectionFacade allows us to perform actions like adding a class
    public Class()
        => Users = new JoinCollectionFacade(
            UserClass,
            uc => uc.User,
            u => new Models.UserClass { User = u, Class = this });

    public Class(Instructor i, string d)
    {
        Instructor = i;
        Description = d;
    }

    public int Id { get; set; }
    public virtual Instructor Instructor { get; set; } // Made virtual so we can take advantage of certain Entity Framework such as lazy loading (from Microsoft docs)
    public string Description { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime EndTime { get; set; }

    private ICollection UserClass { get; } = new List();

    // This element does not need to be mapped. It's used for easier reference
    // to the Users enrolled in the class
    [NotMapped]
    public ICollection Users { get; }
}

UserClass.cs

public class UserClass
{
    public string UserId { get; set; }
    public User User { get; set; }

    public int ClassId { get; set; }
    public Class Class { get; set; }
}

user_13105916 · Answer

I solved my problem by

making the instances of UserClass public in both User.cs and Class.cs
using this kind of query below to retrieve all the users enrolled in a specific class

var usersInClass = _context.Users
                      .Include(i => i.UserClass).ThenInclude(i => i.Class)
                      .Where(x => x.UserClass.Select(pc => pc.Class).Any(c => c.Id == id))
                      .ToList();

Query on many-to-many relationship Users -> Classes

1 Answers