title: Filtering index list of a table if a value exists in another table
body: <p>So I have three tables in my database:</p>
  
  <p>Projects:</p>
  
  <pre><code>    [ProjectId]               INT            IDENTITY (1, 1) NOT NULL,
      [UserId]                  NVARCHAR (128) NULL,
      [Title]                   NVARCHAR (100) NOT NULL,
  </code></pre>
  
  <p>Applications:</p>
  
  <pre><code>    [ApplicationId]  INT             IDENTITY (1, 1) NOT NULL,
      [ProjectId]      INT             NOT NULL,
      [UserId]         NVARCHAR (128)  NOT NULL,
  </code></pre>
  
  <p>Contracts:</p>
  
  <pre><code>    [ContractId]    INT            IDENTITY (1, 1) NOT NULL,
      [ApplicationId] INT            NOT NULL,
      [CompanyId]     NVARCHAR (128) NOT NULL,
      [CVR]           NVARCHAR (128) NOT NULL,
      [StudentId]     NVARCHAR (128) NOT NULL,
      [IsSigned]      BIT            DEFAULT ((0)) NOT NULL,
  </code></pre>
  
  <p>The way that the MVC application works is that there is an Index of Projects (relatively simple db.Projects.ToList() method), a student can apply, which creates an Application. When the Employee goes to their project and sees the student's application, he clicks a button to choose the student, creating a new Contract in the database. 
  How can I modify </p>
  
  <pre><code>List&lt;Project&gt; projectsList = (from p in db.Projects select p).ToList(); 
  </code></pre>
  
  <p>so that only projects that have not been "contracted" are shown? The contract has the applicationId, when the application can be retrieved, from which the projectId can be retrieved. There should be some kind of way to easily check if the projectID exists in the Contracts table, and if so, should not display that project in the index. </p>