MVC - Linq - Populate List<T> with Records in Another Table

Question

I'm prototyping my first MVC application, it's a simple forum. I've done part of the domain model and I'm trying to figure out how to do something that's pretty basic in SQL alone, but I can't figure it out in my application. Here are my Entities:

[Table(Name="Users")]
public class User
{
    [Column(IsPrimaryKey=true, IsDbGenerated=true, AutoSync=AutoSync.OnInsert)]
    public int Id { get; set; }

    [Column] public string Username { get; set; }
    [Column] public string Password { get; set; }
    [Column] public string PasswordHash { get; set; }
    [Column] public string PasswordSalt { get; set; }
    [Column] public string FirstName { get; set; }
    [Column] public string LastName { get; set; }
    public List<Forum> AllowedForums { get; set; }
    [Column] public DateTime LastLogin { get; set; }
    [Column] public DateTime MemberSince { get; set; }
}

[Table(Name="Forums")]
public class Forum
{
    [Column(IsPrimaryKey=true, IsDbGenerated=true, AutoSync=AutoSync.OnInsert)]
    public int Id { get; set; }
    [Column] public int ParentId { get; set; }
    [Column] public string Title { get; set; }
    [Column] public string Description { get; set; }
    [Column] public bool IsGlobal { get; set; }
    [Column] public int DisplayOrder { get; set; }
}

I also have a linking table called AllowedForums that looks like:

userid  forumid
  1       4

In order to select the forums that a user is allowed to view and forums where IsGlobal == true I'd do this in SQL:

SELECT * FROM Forums
LEFT OUTER JOIN AllowedForums ON Forums.id = AllowedForums.Forumid
WHERE AllowedForums.Userid = 1
OR Forums.IsGlobal = 1

How should I populate the

public List<Forum> AllowedForums

field using C#/Linq to SQL?

Should AllowedForum be a value object with its own table mapping? That seems like overkill but I could easily join on it. I looked briefly at EntitySet but the simple example I saw didn't seem to fit. It feels like there should be an elegant way to get a collection of Forum objects for each User, but I can't come up with any. BTW, I'm new to C# & OO. I should also mention that since these are the early stages of the app, I'm open to changing the structure/relationships of the entities or tables if there's a better approach I'm not seeing.

Answer 1

Something like:

var AllowedForums = from f in ForumsTable
                    join af in AllowedForumsTable on f.Id equals af.forumid into temp
                    from aft in temp.DefaultIfEmpty()
                    where (f.IsGlobal == 1 || aft.userid == 1)
                    select f;

Answer 2

You should have another Entity class (probably should be internal) that mirrors your AllowedForums table in the database. Now I'm assuming your User table and your Forums table both have PK/FK relationships to this AllowedForums table. Therefore, there should be an internal property on the User class that looks like this:

internal EntitySet<AllowedForums> AllowedForumsRelationships
{
   get;set;
}

Or something like that. This should be on both the User and Forums class. Your AllowedForums class will have two properties on it. One for User and one for Forum. (If you use the LINQ to SQL designer, all this will happen for you automatically).

Once you have that, if you want to get all the AllowedForums for a user you can do something like this:

    public IList<Forum> AllowedForums
    {
       get
       {
          var result = new List<Forum>();
          foreach(var relationShip in this.AllowedForumsRelationships)
          {
             result.Add(relationShip.Forum);
             return result;
          }
       }
    }

This is some rough code I just banged out, and I'm not sure it's 100% accurate, but I think you'll get the idea. Basically you're dealing with a many to many relationship which is always a pain.

EDIT: I just messed with this idea with the Northwind Database with these tables:

Orders OrderDetails Products

There's a many to many relationship there: An order can have multiple products, and a product can belong to many orders. Now say you want to get all products for an order:

public partial class Order
{
        public IList<Product> Products
        {
            get
            {
                var list = new List<Product>();
                foreach (var item in this.Order_Details)
                {
                    list.Add(item.Product);
                }
                return list;
            }
        }
 }

That works, so it should work in the scenario you're talking about as well.