Many-to-Many EF6 query ... trying to get entity Bs that belong to entity A and include all entity As

My models … (fyi “Application" as in a job application and “Administrator” as in a person who has been assigned responsibility for this application)

public class Application
{
  public int Id { get; set; }
  public string Title { get; set; }

  public ICollection<Administrator> Administrators { get; set; }
}

public class Administrator
{
  public int Id { get; set; }
  public string Username { get; set; }

  public ICollection<Application> Applications { get; set; }
}

Notice many-to-any relationship between applications and administrators.

Its an intranet application and the user (who will also be an administrator of one or more applications) will be identified using windows authentication. I want to return JSON, only for the applications for which the user is an administrator but also include all of the administrators for those applications. For example, John created a new application titled “Nurse Practitioner for Pediatric Clinic" which makes John automatically an administrator for that application but John also assigned Betty as an administrator for that application. Betty is not an administrator for any other applications. If Betty log in, she should get the Application object titled "Nurse Practitioner for Pediatric Clinic” and that application’s 2 administrators (John and Betty).

This works but includes all applications not limited to Betty’s ...

context.Applications.Include("Administrators").ToList();

These work but obviously is not what I want ...

context.Administrators.Include("Applications").ToList();
context.Applications.Include("Administrators").Where(a => a.Id.Equals(1)).ToList();
context.Administrators.ToList();
context.Administrators.Find(1);

This does not work …

context.Applications.Include("Administrators").Where(a => a.Administrators.Contains(context.Administrators.First())).ToList();

Get ...

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code
Additional information: The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead.

And this does not work …

context.Administrators.Find(1).Applications.ToList();

Get ...

An exception of type 'System.ArgumentNullException' occurred in System.Core.dll but was not handled in user code
Additional information: Value cannot be null.

Although I know that the Administrator with Id=1 has 2 Applications.

This works …

(from app in context.Applications
                         from admin in app.Administrators
                         where admin.Username == “John"
                         select app).ToList();

But it doesn’t include the Administrators and if I add an Include() clause like this …

(from app in context.Applications
                         from admin in app.Administrators
                         where admin.Username == “John"
                         select app).Include(“Administrators”).ToList();

it doesn’t work.

Can’t figure this out. Any help would be much appreciated. Thanks.

Solution

Figured it out myself ...

// get the current user's id
var userId = 1;
var apps = (from Application app in context.Applications.Include("Administrators")
                        where app.Administrators.Select(a => a.Id).Contains(userId)
                        select app).ToList();