How do I create this query in LINQ to Entities

Question

I have an MVC app using Linq to Entities and I'm having a hard time figuring out how to get this part of a query written. This is an excerpt from a SQL Stored Procedure that works. I know I can use the procedure but I'm trying to get a better understanding of LINQ. The goal is to get the last Routing for a requisition and the ReqRoutingID is an identity filed so the last entry in the table is always the one I want.

SELECT h.ReqID, rr.RoutingSectionID FROM LOG_ReqHeader h JOIN dbo.Log_ReqRoutings rr ON rr.ReqRoutingID = ( SELECT TOP 1 r.ReqRoutingID FROM Log_ReqRoutings r1 WHERE r1.ReqID = h.ReqID ORDER BY r1.ReqRoutingID desc)

@Lazyberezovsky, I realize that I had not indicated which part I am having trouble with. It is the Join part that I am having trouble with. I edited the post to include the SELECT part so it makes more sense. Thanks. — Alan Fisher
– Alan Fisher, Commented Jan 28, 2013 at 21:21
@Lazyberezovsky, I'm confused because you had posted an answer that worked but now the post says it has been deleted. — Alan Fisher
– Alan Fisher, Commented Jan 28, 2013 at 22:34

Sergey Berezovskiy · Accepted Answer · 2013-01-28 21:22:15Z

3

var query = from h in db.LOG_ReqHeader from rr in db.Log_RequRoutings where rr.ReqRoutingID == (from r1 in db.Log_RequRoutings where r1.Req == h.ReqID orderby r1.ReqRoutingID descending select r1).FirstOrDefault().ReqRoutingID select new { h.ReqID, rr.RoutingSectionID };

answered Jan 28, 2013 at 21:22

Sergey Berezovskiy

237k44 gold badges441 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sergey Berezovskiy Over a year ago

Sorry, don't remember now if First is supported by EF (FirstOrDefault is definitely supported). You can give it a try

Magnus Over a year ago

If FirstOrDefault() returns null wont that give exception on accessing ReqRoutingID ?

Sergey Berezovskiy Over a year ago

@Magnus whole query is translated into SQL, so there will not be any exceptions here

Alan Fisher Over a year ago

This answer worked once I cleaned up things like removing the extra "u" from Log_RequRoutings. What confuses me is the fact that there are two from statements. This is the first time I have come across a statement like this.

Sergey Berezovskiy Over a year ago

@AlanFisher yes, there is no problem with several from statements. Actually it is the only way you can do join with conditions in Linq

Collectives™ on Stack Overflow

How do I create this query in LINQ to Entities

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related