LinqToSql - Prevent sub queries when limiting number of rows returned

Question

Dim query = (From p in Parent _
      select _
       p.ID, _
       Tags = String.Join("|", p.Child.Select(Function(c) c.Tag.TagName).ToArray)).Take(100)

In the above query, when using Take to limit the rows returned, a separate SQL query is executed for each row to return the 'Tags' field. If I remove Take(100), a single query to sent to Sql Server.

So, how do I limit the number of rows returned, while preventing a new sub query being executed for each row?

Answer 1

edit2 When working with nested types, so doing new { r, r.childrenCollection }, LINQ translates this to (SELECT TOP 100 FROM r), deleting the join information. When doing a join by yourself this doesn't happen. So something like:

        var thingyWithChilds
            = (from p in dc.RightCategories
               join r in dc.Rights on p.Id equals r.CategoryId
               select new { p.Id, r });

        var bla = thingyWithChilds.Take(100);

will not cause the same problem.

other stuff that might apply

You're doing ToArray() which causes the query to execute as it isn't a IQueryable. Just do ToArray() after you do Take().

edit According to this SO topic: http://stackoverflow.com/questions/934635/is-there-a-linq-equivalent-of-string-joinstring-string, it is neither possible to use String.Join if you want to do everything on the server, as there is no SQL command available to do that in TSQL.