LinqToSql - prevent subqueries when limiting the number of rows returned
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), one request is sent to the Sql server.
So how do I limit the number of rows returned and also prevent a new subquery from being executed for each row?
source to share
edit2 When working with nested types, by executing a new {r, r.childrenCollection}, LINQ translates this value to (SELECT TOP 100 FROM r), removing the connection information. When you do it yourself, it doesn't. 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);
won't cause the same problem.
other things that can be applied
You are doing ToArray () which causes the query to be executed since it is not an IQueryable. Just do ToArray () after you do Take ().
edit According to this SO topic: Is there a LINQ equivalent to string.Join (string, string []) , it is not possible to use String.Join if you want to do everything on the server as there is no such SQL command in TSQL.
source to share