ASP.NET EF6 Query Model

Question

I have the following model. Each Module has a nested collection of children of type module. Each module also has a collection of Permissions.

[DataContract(IsReference = true)]
public class Module
{
    [Key]
    [DataMember]
    public Guid ModuleId { get; set; }

    [Required]
    [StringLength(100)]
    [DataMember]
    public string Title { get; set; }

    [StringLength(100)]
    [DataMember]
    public string Description { get; set; }

    [StringLength(50)]
    [DataMember]
    public string Icon { get; set; }

    [Required]
    [RegularExpression(@"[^\s]+")]
    [StringLength(50)]
    [DataMember]
    public string Route { get; set; }

    [DataMember]
    public ICollection<Permission> Permissions { get; set; }

    [DataMember]
    public Guid? ParentModuleId { get; set; }

    [ForeignKey("ParentModuleId")]
    [DataMember]
    public virtual ICollection<Module> Children { get; set; }
}
[DataContract(IsReference = true)]
public class Permission
{
    [Key]
    [DataMember]
    public Guid PermissionId { get; set; }

    [Required]
    [StringLength(100)]
    [DataMember]
    public string Role { get; set; }

    [DataMember]
    public Guid ModuleId { get; set; }

    [ForeignKey("ModuleId")]
    [DataMember]
    public Module Module { get; set; }
}

I have a Query All function as below, which would return correctly all root with its children.

public override IQueryable<Module> All()
    {
        return this.Context.Set<Module>().Include(c => c.Children).Where(p => p.ParentModuleId == null);
    }

Now, I want to return same list of root with its children which has the Permission "User". How do i do this. This is what i have so far. Is this correct way of doing this? please help.

return this.Context.Set<Module>().Include(c => c.Children).Where(p => p.ParentModuleId == null).Include(p => p.Permissions).Where(s => s.Permissions.Any(r=>r.Role=="User"));

btw, i have no idea how to use properly these functions such as include,where,any,select many functions. Any tutorials or books for this are appreciated. I can't find any good tutorial about this since i don't know what keyword to search for. Was this EF or LINQ.

Your code looks correct to me, is there a problem with it? Although, it would only check the permission set of the root module, not its children. — Dan, Dec 31 '14 at 06:35
I would also like to add that lazy loading is enabled by default, which means that you don't need to use `.Include` (unless you specifically disable lazy loading), you can access the navigational property directly and EF will lazy load it. — Dan, Dec 31 '14 at 06:36
The problem is with the children, its not checking permissiion for children. how do i do it? — Shah Nawas Khan, Dec 31 '14 at 07:37
I think you're looking for "filtered include" (which is not possible): http://stackoverflow.com/a/16801205/861716 — Gert Arnold, Dec 31 '14 at 10:50
Just to confirm: Obviously a children will not have permissions that their parents don't, right? — Corey Adler, Dec 31 '14 at 15:24
@IronMan84, Yes, the parent has all childrens permission. e.g, if Child A has permission "A", Child B has permission "B", then The parent would automatically has permission "A,B". I don't know if this makes sense, but so far i'm thinking in this design. Basically all this parents and its children would be loaded into and accordion, and mapped as routes to html pages. So based on login role, I find correct accordion for each user. — Shah Nawas Khan, Jan 01 '15 at 05:40

score 0 · Answer 1 · answered Dec 31 '14 at 15:27

The Include method tells Entity Framework to go populate that specific navigational property when it goes to the database to bring back the records (i.e. it eager loads the data instead of utilizing lazy loading, which would require EF to go back to the database later). It doesn't do any kind of filtering. All that is done by the 'Where` method.

To do the kind of filtering that you're asking for across all the children you'll have to do one of two things:

1) Create a Common Table Expression in SQL which will recursively grab all of the children for a specific module, place that CTE in a SQL View, map an EF entity to that View, and then query that view, including a Join to the Permissions table to grab only those that have the permission you're looking for.

2) To do this without the T-SQL fun, simply create a recursive function on your Module class with the [NotMapped] attribute over it that will go through all of the children and return only those children that have the permission you're looking for (Note: This will require more resources than the first one, and will be slower in your application, since this will be primarily a LINQ to Objects query instead of LINQ to Entities).

Something like this:

[NotMapped]
public List<Module> GrabModulesWithPermission(string permission)
{
  var toReturn = new List<Module>();

  if (this.Children != null && this.Children.Any(c => c.Permissions.Any(r => r.Role == permission))
  {
     toReturn.AddRange(this.Children.Where(c => c.Permissions.Any(r => r.Role == permission).SelectMany(c => c.GrabModulesWithPermission(permission)));
  }

  toReturn.Add(this);

  return toReturn;
}

As far as tutorials go, I would highly recommend looking at Pluralsight. There are a number of videos there on EF, including some by Julie Lerman who is Microsoft's EF MVP.

ASP.NET EF6 Query Model

1 Answers1