2
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?

Geoff Appleford
  • 18,538
  • 4
  • 62
  • 85

1 Answers1

2

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: Is there a LINQ equivalent of string.Join(string, 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.

Community
  • 1
  • 1
Jan Jongboom
  • 26,598
  • 9
  • 83
  • 120
  • Why does it work without the Take()? If I substitute Take with ToList() to execute the query, only one SQL query is generated! – Geoff Appleford Sep 16 '09 at 08:58
  • Because Take is an additional action that has to be performed on the SQL server. query-thats-already-partial-executed.ToList().Take(100), will generate 1 query; query-thats-already-partial-executed.Take(100).ToList() will generate additional queries. – Jan Jongboom Sep 16 '09 at 09:21
  • Okay, but it still seems that there is no way to solve my original question. Even removing the toArray(eg change to simply p.Child) results in the same behaviour. – Geoff Appleford Sep 16 '09 at 09:32
  • Have you used String.Join in your last test, maybe there is no sufficient translation from String.Join to a SQL function in Linq-SQL – Jan Jongboom Sep 16 '09 at 09:38
  • No, changed to Tags = p.Child – Geoff Appleford Sep 16 '09 at 09:40
  • Thanks for all your effort Jan. The problem with using joins is that it returns a flat list rather than ID plus entity set. – Geoff Appleford Sep 16 '09 at 10:29
  • In my code example you get an object containing the ID and the corresponding entity set, afaik exactly what you got in the original query. – Jan Jongboom Sep 16 '09 at 11:35
  • I dont get that in LinqPad. Group Join works as you describe but has the same multi sql query problem. – Geoff Appleford Sep 16 '09 at 11:54