Subsonic many-to-many relationship

Question

Hi, I have 3 tables, one is called "Users", one is called "Categories" and one is a linking table called "User_Categories_Map" to link users to categories in a many-to-many relationship. The linking table consists of UserId's and CategoryId's. After generating the subsonic classes, I would assume I'd be able to then type User.singleOrDefault(x => x.ID == 1).Categories to select all the categories for a user. However, this doesn't work. If you can understand what I'm trying to accomplish here, can anyone tell me how I can make this work in subsonic? Consequently, I cannot find any documentation on subsonic. Subsonicproject.com only has a short page a few articles about how to set it up. Is there documentation somewhere for subsonic?

Answer 1

int lUserID =1;    // suppose 1 is Id of user

CategoriesCollection lCategories = DB.Select().From<Categories>()  
  .InnerJoin(User_Categories_Map)  
  .InnerJoin(Users)  
  .Where(Users.Columns.Id).IsEqualTo(lUserID)  
  .ExecuteAsCollection<CategoriesCollection>();

It will return collection of categories associated to a specific user..