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: 233
LINQ
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