Writing a sub query in LINQ with Top X

Question

I require assistance on writing the following query within LINQ. Ideally I want to use a variable (passed in as a parameter) as the Top X value. Suggestions 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 inner query joins using SiteID as I'm trying to retrieve the top 3 rows per SiteID.

Answer 1

What about that:

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's you SQL clearly translated into LINQ query.

Answer 2

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

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

By the way, it seems like your SQL could be simplified (unless I'm not understanding your model correctly) to

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