Linq query joining with a subquery

Posted by Alan Fisher on Stack Overflow See other posts from Stack Overflow or by Alan Fisher
Published on 2012-12-18T01:46:37Z Indexed on 2012/12/18 23:03 UTC
Read the original article Hit count: 231

Filed under:

I am trying to reproduce a SQL query using a LINQ to Entities query. The following SQL works fine, I just don't see how to do it in LINQ. I have tried for a few hours today but I'm just missing something.

SELECT 
  h.ReqID,
  rs.RoutingSection
FROM ReqHeader h
JOIN ReqRoutings        rr ON rr.ReqRoutingID = (SELECT TOP 1   r1.ReqRoutingID  
                                                FROM ReqRoutings r1
                                                WHERE r1.ReqID = h.ReqID 
                                                ORDER BY r1.ReqRoutingID desc)
JOIN ReqRoutingSections rs ON rs.RoutingSectionID =  rr.RoutingSectionID  

Edit*** Here is my table scema-

Requisitions:
 ReqID PK string
 ReqDate datetime
 etc...

ReqRoutings:
 ID PK int
 ReqID FK 
 RoutingSection FK int
 RoutingDate

ReqRoutingSections:
 Id PK int
 RoutingSection string

The idea is that each Requisition can be routed many times, for my query I need the last RoutingSection to be returned along with the Requisition info.

Sample data:

Requisitions:   - 1 record
ReqID 123456
ReqDate '12/1/2012'

ReqRoutings:  -- 3 records
 id 1
 ReqID 123456
 RoutingSection 3
 RoutingDate '12/2/2012'

 id 2
 ReqID 123456
 RoutingSection 2
 RoutingDate '12/3/2012'

 id 3
 ReqID 123456
 RoutingSection 4
 RoutingDate '12/4/2012'

 ReqRoutingSections: -- 3 records

 id 2
 Supervision 

 id 3
 Safety 

 id 4
 Qaulity Control

The results of the query would be

ReqID = '123456'
RoutingSection = 'QualityControl'   -- Last RoutingSection requisition was routed to

© Stack Overflow or respective owner

Related posts about LINQ