title: Using subquery in entity framework
body: <p>I am trying to write the entity framework linq query to generate the following SQL. But I am not sure how to use subqueries with entity framework.</p>
  
  <p>The Sql I want to generate is:</p>
  
  <pre><code>Declare @StartDate Datetime2; Set @Startdate = '2014-Feb-16 09:52'
  Declare @EndDate Datetime2; Set @Enddate = '2014-Feb-18 09:52'
  
   SELECT
           [D].[RefId]
          ,[D].[StatusId]
          ,[D].[StatusDate]
          ,[D].[Reference]
          ,[RSC].[Event] 
          ,[RSC].[Information] 
          ,[RSC].[CreatedDate] 
    FROM (
      SELECT
             [R].[RefId]
            ,[R].[StatusId]
            ,[R].[StatusDate]
            ,[I].[Reference]          
            ,(SELECT TOP 1
                     [RSC].[ChangeId]
              FROM
                     [dbo].[StateChangeTable] AS [RSC] (nolock)
              WHERE
                     [RSC].[RefId] = [R].[RefId]
              ORDER BY
                     [RSC].[ChangeId] DESC) AS [LastChangeId]
      FROM
             [dbo].[Table1] AS [R] (nolock)
      INNER JOIN
             [dbo].[Table2] AS [I] (nolock)
      ON
             [R].[RefId] = [I].[RefId]
  
      WHERE
             [R].[StatusId] IN (4, 6)
      AND    [R].[StatusDate] between @StartDate and @EndDate
      ) AS [D]
    INNER JOIN
           [dbo].[StateChangeTable] AS [RSC] (nolock)
    ON
           [D].[LastChangeId] = [RSC].[ChangeId
  </code></pre>
  
  <p>]</p>
  
  <p>And the code I wrote till now is: </p>
  
  <pre><code>return this.DbContext.Table1
              .Join(this.DbContext.Table2, rc =&gt; rc.RefId, ri =&gt; ri.RefId, (rc, ri) =&gt; new { rc, ri })                     
              .Join(this.DbContext.StateChangeTable, request =&gt; request.ri.RefId,  rsc =&gt; rsc.RefId, (request, rsc) =&gt; new {request, rsc})
             .Where(r =&gt; (r.rsc.ChangeId == ((from rsc in this.DbContext.StateChangeTable                                                               
                                                  orderby rsc.ChangeId descending
                                                  select rsc.ChangeId).FirstOrDefault())) &amp;&amp;
                          (r.request.rc.StatusId == 4 || r.request.rc.StatusId == 6) &amp;&amp;
                          (r.request.rc.StatusDate &gt;= startDateTime &amp;&amp; r.request.rc.StatusDate &lt;= endDateTime))              
              .Select(requestDetails =&gt; new StatusDetail
                  {
                      RefId = requestDetails.request.rc.RefId,
                      StatusDate = requestDetails.request.rc.StatusDate,
                      StatusId = requestDetails.request.rc.StatusId,
                      Reference = requestDetails.request.ri.DistributionReference.Value,
                      Event = requestDetails.rsc.Event,
                      CreatedDate = requestDetails.rsc.CreatedDate,
                      Information = requestDetails.rsc.Information
                  }).ToList();
  </code></pre>
  
  <p>Can some please let me know what I am doing wrong?</p>
  
  <p>Many Thanks</p>