title: How to select data based on what a user has access to using Entity Framework
body: <p>I'm using code-first Entity Framework, and I've created all of the models (Category, Data, UserData) and let EF create all of the tables. So, my database currently looks like this:</p>
  
  <p><img src="https://i.stack.imgur.com/LODMC.png" alt="Relationship of tables"></p>
  
  <p>I've managed to get the "self relationship" sorted with the category table, and I've managed to also link in the "data" table. EF quite happily pulls out all of the categories for a given parent, along with the associated "data" items.</p>
  
  <p>The bit I'm stuck on is; only grabbing the "data" items in a category for which the user has "access" to. So the idea is a user can create "data" items in a category, and then give permission as to who can view them. Here's the code listing for my models:</p>
  
  <pre><code>[Table("Category")]
  public class Category
  {
      [Key]
      public Int32 CategoryId { get; set; }
      public string CategoryName { get; set; }
      public Int32? Category_ParentID { get; set; }
      [ScriptIgnore]
      public virtual Category Parent_Category { get; set; }
      [ScriptIgnore]
      public virtual ICollection&lt;Category&gt; SubCategories { get; set; }
      [ScriptIgnore]
      public virtual ICollection&lt;Data&gt; Datas { get; set; }
      public Int16 CategoryOrder { get; set; }
      public bool Deleted { get; set; }
  }
  
  [Table("Data")]
  public class Data
  {
      [Key]
      public Int32 DataId { get; set; }
      public string Data { get; set; }
      public Int32 Parent_CategoryId { get; set; }
      [ScriptIgnore]
      public virtual Category Parent { get; set; }
  
  }
  
  [Table("UserData")]
  public class UserData
  {
      [Key]
      [Column(Order=1)]
      public Int32 Id { get; set; }
      [Key]
      [Column(Order = 2)]
      public Int32 DataId { get; set; }
      public Data UsersData { get; set; }
      public ApplicationUser UserDataUser { get; set; }
  }
  </code></pre>
  
  <p>And in the database context class I have the mappings for category->category and category->data:</p>
  
  <pre><code>    protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
          base.OnModelCreating(modelBuilder);
  
          modelBuilder.Entity&lt;Category&gt;()
              .HasRequired(a =&gt; a.Parent_Category)
              .WithMany(b =&gt; b.SubCategories)
              .HasForeignKey(c =&gt; c.Category_ParentID) // FK_Category_ParentID
              .WillCascadeOnDelete(false);
  
  
          modelBuilder.Entity&lt;Data&gt;()
              .HasRequired(a =&gt; a.Parent)
              .WithMany(b =&gt; b.Datas)
              .HasForeignKey(c =&gt; c.Parent_CategoryId) // FK_Parent_CategoryId
              .WillCascadeOnDelete(false);
  
      }
  </code></pre>
  
  <p><strong>More Info</strong></p>
  
  <p>I'm trying to retreive a list of categories, including the associated the "data" items, that the user has "access" to:</p>
  
  <pre><code>            //return the selected item - with its children
              var selectedCategoryItem = DatabaseContext.Categories
                      .ToList().Select(c =&gt; new Category()
                      {
                          SubCategories = c.SubCategories.Where(sub =&gt; !sub.Deleted).ToList(),    //make sure only undeleted subcategories are returned
                          CategoryId = c.CategoryId,
                          CategoryName = c.CategoryName,
                          Category_ParentID = c.Category_ParentID,
                          CategoryOrder = c.CategoryOrder,
                          Parent_Category = c.Parent_Category,
                          Datas = c.Datas.Where(d =&gt; !d.Deleted).ToList(),          //make sure only undeleted Datas are returned
                          Deleted = c.Deleted
                      })
                      .Single(c =&gt; c.CategoryId == ParentCategoryId);
  </code></pre>
  
  <p>The above code currently grabs a specified parent category, with its associates sub categories and "data" items - I need to filter out the "data" items the user doesnt have access to.</p>