CODEWITHSUNDEEP
c#.netvb.netlinq-to-sql
Geoff Appleford
Geoff Appleford

Reputation: 18832

LinqToSql - Prevent sub queries when limiting 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), 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?

Upvotes: 2

Views: 435

Answers (1)

Jan Jongboom
Jan Jongboom

Reputation: 27342

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.

Upvotes: 2

Related Questions