Subsonic 3 simple repository many to many relations

Question

I saw in an earlier post here on stackoverflow a example on many to many relations. To keep it simple, lets have a look at these classes :

public class Role(){
    public int Id { get; set; }
    public string Rolename { get; set; }
    public string Description { get; set; }
}

public class User(){
    public int Id { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
    public IList<UsersAndRoles> UsersAndRoles { get; set; }
}

public class UsersAndRoles(){
    public int Id { get; set; }
    public User User { get; set; }
    public Role Role { get; set; }
}

This is freehand so bear with me.

Now what is the best way to make the linq query for these classes. Lets say I want to get all roles with the users attached to them. Can someone help me with this?

Answer 1

Weel, as i want to keep my Entities clean without any database lookups I can't do it like you sugested.

I made my entities like this :

Role entity

public class Role(){
    public int RoleId { get; set; }
    public string Rolename { get; set; }
    public string Description { get; set; }
    public IList<User> Users { get; set; }
    public Role()
    {
        RoleId = 0;
        Rolename = "";
        Description = "";
        Users = new List<User>();
    }
}

User entity

public class User(){
    public int UserId { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
    public string Email { get; set; }
    public User()
    {
        UserId = 0;
        Username = "";
        Password = "";
        Email = "";
    }
}

Users and roles entity

public class UsersAndRoles(){
    public int Id { get; set; }
    public int UserId { get; set; }
    public int RoleId { get; set; }
    public UsersInRoles()
    {
        Id = 0;
        UserId = 0;
        RoleId = 0;
    }
}

Now from my repository I try to do the folowing :

public IList<Role> GetRolesAndUsers()
{
    //--- Load all roles
    var roles = base.All<Role>();

    //--- Run through all roles and add the users to the roles.
    foreach (var role in roles)
    {
        //--- Load the users in the given role
        var users = (from u in base.All<User>()
                     join ur in base.All<UsersInRoles>() on role.RoleId equals ur.RoleId
                     where u.UserId == ur.UserId
                     select u).ToList();

        //--- Run through the list of users and add the user to the role
        foreach (var user in users)
        {
            role.Users.Add(user);
        }
    }
    //--- Return roles and users
    return roles.ToList();
}

However the users does not get added to the roles even though I can se the users get loaded when I step through the code using debug.

What am I missing here ?