Linq query on many to many relationship

Question

I work on a website for an organisation, this is non profit and I do it for free, so I am not looking for someone to do my job for me here, just a bit of help with something I am finding extremely tricky as it is relatively new to me

Database which is using the aspnet entity stuff

So the tables I am querying are

 - AspNetRoles
 - AspNetUserRoles
 - AspNetUsers

I also have tables of my own

 - myEvents (Id, Title, PrivateEvent)
 - myEventsInRoles (EventId, RoleId)
  - EventId is FK to myEvents(Id) and RoleId is FK to AspNetRoles(Id)

Now when I created my ADO Entity Model, because myEventsInRoles (like AspNetUserRoles) has only 2 columns which are both foreign keys, like AspNetUserRoles it does not generate an entity for it. So those two tables cannot be queried with linq.

So my scenario is this

A user logs in and wants to view the events list. So example could be

 - AspNetUsers
  - Id=1
 - AspNetUserRoles
  - UserId=1, RoleId=1
 - AspNetRoles
  - Id=1, Name=Red Team 
  - Id=2, Name=Blue Team 
  - Id=3, Name=Green Team
 - myEvents
  - Id=1, Title=Big event, PrivateEvent=0
  - Id=2, Title=Red And Green Team event, PrivateEvent=1
  - Id=3, Title=Blue Team event, PrivateEvent=1
 - myEventsInRoles
  - EventId=2, RoleId=1
  - EventId=2, RoleId=3
  - EventId=3, RoleId=2

So let's say our user is in the red team

The events selected for them to view should be events 1 and 2 because they should fall under the following rules

1. All events that have the PrivateEvent flag set to false
2. All Events that have the PrivateEvent flag set to true AND that eventId exists in the myEventsInRoles AND the user has the same RoleId associated with that record in myEventsInRoles

So I would expect to see the Big event and the Red Team event

I believe this is a bit more complicated but not dissimilar a query to another I also cannot workout, which is to simply get all Role Id's and Role Names for a User.

Answer 1

So after a flash of inspiration I managed to actually work out my two issues, the get roles for user turned out to be easier than i thought

var ctx = new ApplicationDbContext();
var user = HttpContext.Current.User;
var userId = user.Identity.GetUserId();
    var roles =
      ctx.Roles.Where(a => a.Users.Select(b => b.UserId).ToList().Contains(userId))
        .ToList();

And my events issue

    var events =
              db.myEvents.Where(
                e =>
                e.date >= DateTime.Now
                && ((!e.@private)
                    || (e.@private
                        && e.AspNetRoles.Where(a => a.AspNetUsers.Select(b => b.Id)
                             .ToList()
                             .Contains(userId))
                             .Select(c => c.myEvents.Select(d => d.Id)
                             .ToList()
                             .Contains(e.Id))
                             .Any())))
                .Select(
                  e =>
                  new EventItem
                    {
                      id = e.Id,
                      date = e.date,
                      description = e.description,
                      privateE = e.@private,
                      title = e.title,
                      type = e.type,
                      url = e.url
                    });

public class EventItem
{
  public int id { get; set; }
  public DateTime? date { get; set; }
  public string description { get; set; }
  public string title { get; set; }
  public string type { get; set; }
  public string url { get; set; }
  public bool privateE { get; set; }
}