Can take be used in a query expression in c# linq instead of using .Take(x)?

Question

I'm trying to write some LINQ To SQL code that would generate SQL like

SELECT t.Name, g.Name
FROM Theme t
INNER JOIN (
    SELECT TOP 5 * FROM [Group] ORDER BY TotalMembers
) as g ON t.K = g.ThemeK

So far I have

var q = from t in dc.Themes 
join g in dc.Groups on t.K equals g.ThemeK into groups 
select new { 
    t.Name, Groups = (from z in groups orderby z.TotalMembers select z.Name )
};

but I need to do a top/take on the ordered groups subquery. According to http://blogs.msdn.com/vbteam/archive/2008/01/08/converting-sql-to-linq-part-7-union-top-subqueries-bill-horst.aspx in VB I could just add TAKE 5 on the end, but I can't get this syntax to work in c#. How do you use the take syntax in c#?

edit: PS adding .Take(5) at the end causes it to run loads of individual queries

edit 2: I made a slight mistake with the intent of the SQL above, but the question still stands. The problem is that if you use extension methods in the query like .Take(5), LinqToSql runs lots of SQL queries instead of a single query.

Answer 1

Just bracket your query expression and call Take on it:

var q = from t in dc.Themes 
join g in dc.Groups on t.K equals g.ThemeK into groups 
select new { t.Name, Groups = 
       (from z in groups orderby z.TotalMembers select z.Name).Take(5) };

In fact, the query expression isn't really making things any simpler for you - you might as well call OrderBy directly:

var q = from t in dc.Themes 
join g in dc.Groups on t.K equals g.ThemeK into groups 
select new { t.Name, Groups = groups.OrderBy(z => z.TotalMembers).Take(5) };

Answer 2

I tried that but in SQL Profiler it ran an extra query for each theme

Answer 3

Second answer, now I've reread the original question.

Are you sure the SQL you've shown is actually correct? It won't give the top 5 groups within each theme - it'll match each theme just against the top 5 groups overall.

In short, I suspect you'll get your original SQL if you use:

var q = from t in dc.Themes 
join g in dc.Groups.OrderBy(z => z.TotalMembers).Take(5)
  on t.K equals g.ThemeK into groups 
select new { t.Name, Groups = groups };

But I don't think that's what you actually want...

Answer 4

Heh thats a good point. I think I may want the impossible :)

Answer 5

Here's a faithful translation of the original query. This should not generate repeated roundtrips.

var subquery =
  dc.Groups
  .OrderBy(g => g.TotalMembers)
  .Take(5);

var query =
  dc.Themes
  .Join(subquery, t => t.K, g => g.ThemeK, (t, g) => new
  {
    ThemeName = t.Name, GroupName = g.Name
  }
  );

The roundtrips in the question are caused by the groupjoin (join into). Groups in LINQ have a heirarchical shape. Groups in SQL have a row/column shape (grouped keys + aggregates). In order for LinqToSql to fill its hierarchy from row/column results, it must query the child nodes seperately using the group's keys. It only does this if the children are used outside of an aggregate.