title: Linq Subquery Where Clause
body: <p>I need some help with thsi linq query. It shoudl be fairly simple, but it is kicking my butt.</p>
  
  <p>I need to use a subquery to filter out data from the main query, but every path I have tried to use results in failure.</p>
  
  <p><strong>The subquery by itself looks like this.</strong></p>
  
  <pre><code>int pk = (from c in context.PtApprovedCertifications
           where c.FkosParticipant == 112118 &amp;&amp;
           (!excludedActionTypes.Contains(c.FkMLSosCodeActionType)) &amp;&amp;
           c.EffectiveDate &lt;= DateTime.Now &amp;&amp;
           c.FkptApprovedCertificationVoidedBy == null
           orderby c.EffectiveDate descending,c.PK descending
           select c.PK).FirstOrDefault();
  </code></pre>
  
  <p>This works as expected but as you can see I plugged in the number 112118. This should be the primary key from main query. </p>
  
  <p><strong>The combined query I've been working on looks like this.</strong></p>
  
  <pre><code>IQueryable&lt;PtAMember&gt; result = (from p in context.PtAMembers
      where (p.FkptACertification == (from c in context.PtApprovedCertifications
          where c.FkosParticipant == p.PtApprovedCertification.OsParticipant.PK &amp;&amp;
          (!excludedActionTypes.Contains(c.FkMLSosCodeActionType)) &amp;&amp;
          c.EffectiveDate &lt;= DateTime.Now &amp;&amp;
          c.FkptApprovedCertificationVoidedBy == null
          orderby c.EffectiveDate descending, c.PK descending
          select c.PK).FirstOrDefault()) &amp;&amp;
              (p.LastName.ToLower().Contains(param.ToLower()) ||
              p.FirstName.ToLower().Contains(param.ToLower()) ||
              p.SocialSecurityNumber.Contains(param))
      select p).Distinct().OrderBy(PtAMembers =&gt; PtAMembers.LastName).ThenBy(PtAMember =&gt; PtAMember.FirstName);
  </code></pre>
  
  <p>This results in an error though. Any help in solving this conundrum would greatly appreciated.</p>
  
  <p>Thanks! </p>