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 bare 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

I've never really written linq queries for this sorta thing as I would rather use the built in active directory repository. If you want to go this route:

I would start first by making sure that your database has the appropriate foreign keys configured:

Table 'Roles'
int Id (PK)

Table 'Users'
int Id (PK)

Table 'UsersAndRoles'
int RoleId (FK)
int UserId (FK)

If this is done correctly, Subsonic3 will generate the classes for you creating IQueryable child objects. If you want to get all roles with the user attached, you'll also need to add a list of UsersAndRoles to the Role Object. You'll have to name this differently as you can't name it the same as the IQueryable object:

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

    private IList<UsersAndRoles> _UserToRoleLinks
    public IList<UsersAndRoles> UserToRoleLinks 
    { 
     get
     {
      if(_UserToRoleLinks == null)
       _UserToRoleLinks = this.UsersAndRoles.ToList();
      return _UserToRoleLinks;
     }
    }
}

public partial class UsersAndRoles
{
    private Role _Role;
    public Role Role
    {
     get
     {
      if (_Role == null)
       _Role = this.Roles.SingleOrDefault();
      return _Role;
     }
     set
     {
      _Role = value;
     }
    }

    private User _User;
    public User User
    {
     get
     {
      if (_User == null)
       _User = this.Users.SingleOrDefault();
      return _User;
     }
     set
     {
      _User = value;
     }
    }
}

Now to get all roles with the users attached, do this:

var roles = Role.All();

to access all users for all roles:

foreach (var role in roles)
{
    foreach (var userToRoleLink in role.UserToRoleLinks)
    {
     var currentUser = userToRoleLink.User;
    }
}

Of course this is freehand so there are bound to be compile errors. Let me know if this helps or not. Good luck!

Answer 2

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 ?