Assistance with Linq Query

Question

I’m new to LINQ and need some help with a query.

I need all of the Resources from (tblResources) that belong to the Anonymous, and Public ResourceGroups (tblResourceGroups). In addition, I also need all of the Resources that belong to any of the ResourceGroups that the currentUser belongs to.

If the currentUser isn’t logged in (currentUser == null) then only Resources belonging to Anonymous, and Public ResourceGroups should be returned.

NOTE: My data model does not contain an entity for tblResourceAccess. I'm not sure why this entity wasn't added when the model was created.

        string currentUser = IdentityHelper.GetUserIdFromRequest(Context.Request);

        var result = from r in DbContext.Resources                                                                           
                     where r.Active == true // && r.ResourceGroups?????                         
                     select new
                     {                                                          
                         ResourceTypeName = r.ResourceType.Name,
                         Name = r.Name,
                         Version = r.Version,
                         Description = r.Description,
                         Path = r.Path,
                         Active = r.Active
                     };

Answer 1

Finally, after a lot of trial and error! I'm not sure if this is best way performance wise to implement this query, but it works.

Thanks for your help @The Sharp Ninja!

string currentUser = IdentityExtensions.GetUserId(HttpContext.Current.User.Identity);

var resources = from r in DbContext.Resources                            
                where r.ResourceGroups.Any(
                      rg => rg.Name == "Anonymous" || 
                      rg.Name == "Public" || 
                      rg.ResourceUserGroups.Any(ug => ug.UserID == currentUser))
                select r;

Answer 2

The tblResourceAccess was abstracted away by EF and the ResourceGroups property added to the Resource table to provide the functionality. Using this relationship we can piece together the following query:

from r in DBContext.Resources.ToList()
where (currentUser == null 
        && ("anonymous,public").Contains(
            r.ResourceGroups.Name.ToLower()))
    || (currentUser != null)
select new
    {                                                          
        ResourceTypeName = r.ResourceType.Name,
        Name = r.Name,
        Version = r.Version,
        Description = r.Description,
        Path = r.Path,
        Active = r.Active
    };