title: Linq query on many to many relationship
body: <p>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</p>
  
  <p>Database which is using the aspnet entity stuff</p>
  
  <p>So the tables I am querying are</p>
  
  <pre><code> - AspNetRoles
   - AspNetUserRoles
   - AspNetUsers
  </code></pre>
  
  <p>I also have tables of my own</p>
  
  <pre><code> - myEvents (Id, Title, PrivateEvent)
   - myEventsInRoles (EventId, RoleId)
    - EventId is FK to myEvents(Id) and RoleId is FK to AspNetRoles(Id)
  </code></pre>
  
  <p>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.</p>
  
  <p>So my scenario is this</p>
  
  <p>A user logs in and wants to view the events list. So example could be</p>
  
  <pre><code> - 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
  </code></pre>
  
  <p>So let's say our user is in the red team</p>
  
  <p>The events selected for them to view should be events 1 and 2 because they should fall under the following rules</p>
  
  <ol>
  <li>All events that have the PrivateEvent flag set to false</li>
  <li>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</li>
  </ol>
  
  <p>So I would expect to see the Big event and the Red Team event</p>
  
  <p>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.</p>