Writing a sub-query to LINQ using Top X

Question

I need help writing the following query in LINQ. Ideally, I want to use a variable (passed as a parameter) as the value of Top X. The sentences are appreciated.

SELECT * FROM [Link] a WHERE ID IN (SELECT TOP 3 ID FROM [Link] b WHERE b.SiteID = a.SiteID ORDER BY a.ID) 

The internal query is combined using SiteID as I try to get the top 3 rows on SiteID.

Answer 1

How about this:

 from l in links where (from l2 in links where l2.SiteID == l.SiteID orderby l2.ID select l2.ID).Take(3).Contains(l.ID) select l 

That you explicitly translated SQL into a LINQ query.

Answer 2

You need to use the Take () method for your Linq query. I don't think this is possible using query syntax, but you can do something like

links.OrderBy(l => l.ID).Take(3);

By the way, it seems that your SQL can be simplified (if I do not understand your model correctly) to

 SELECT TOP 3 * FROM [Link] a ORDER BY a.ID