Using subquery in entity framework

Question

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.

The Sql I want to generate is:

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

]

And the code I wrote till now is:

return this.DbContext.Table1
            .Join(this.DbContext.Table2, rc => rc.RefId, ri => ri.RefId, (rc, ri) => new { rc, ri })                     
            .Join(this.DbContext.StateChangeTable, request => request.ri.RefId,  rsc => rsc.RefId, (request, rsc) => new {request, rsc})
           .Where(r => (r.rsc.ChangeId == ((from rsc in this.DbContext.StateChangeTable                                                               
                                                orderby rsc.ChangeId descending
                                                select rsc.ChangeId).FirstOrDefault())) &&
                        (r.request.rc.StatusId == 4 || r.request.rc.StatusId == 6) &&
                        (r.request.rc.StatusDate >= startDateTime && r.request.rc.StatusDate <= endDateTime))              
            .Select(requestDetails => 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();

Can some please let me know what I am doing wrong?

Many Thanks

Answer 1

Here is the Full query

var query = (from D in
                 ((from tab1 in DbContext.Table1
                   join tab2 in DbContext.Table2 on tab1.RefId equals tab2.RefId
                   where (tab1.StatusId == 4 || tab1.StatusId == 6)
                          && (tab1.StatusDate >= startDate && tab1.StatusDate <= endDate)
                   select new
                   {
                       RefId = tab1.RefId,
                       StatusId = tab1.StatusId,
                       StatusDate = tab1.StatusDate,
                       Reference = tab2.Reference,
                       LastChangeId = (from RSC in DbContext.StateChangeTable
                                       where RSC.RefId == tab1.RefId
                                       orderby RSC.ChangeId descending
                                       select RSC.ChangeId).FirstOrDefault()
                   }))
             join RSC in DbContext.StateChangeTable on D.LastChangeId equals RSC.ChangeId
             select new StatusDetail
             {
                 RefId = D.RefId,
                 StatusId = D.StatusId,
                 StatusDate = D.StatusDate,
                 Reference = D.Reference,
                 Event = RSC.Event,
                 Information = RSC.Information,
                 CreatedDate = RSC.CreatedDate
             }).ToList();

Answer 2

Don't use .Join() you have to use the navigation properties on your entities.