0

when I have LINQ-to-SQL query and do a

var count = query.Count();
var list = query.Take(100).ToList();

to get the amount of possible results, but take only the first 100 (eg for paging), I will get two statements fired to the database.

Is it possible to combine this into one SQL statement using LINQ? In plain SQL I can write a single statement that will return the result count and the top 100 results.

jansepke
  • 1,933
  • 2
  • 18
  • 30
  • These are duplicates: http://stackoverflow.com/questions/2553167/linq-paging-how-to-incorporate-total-record-count and http://stackoverflow.com/questions/2008647/get-the-total-number-of-records-when-doing-pagination You have to run two queries. – Tim Schmelter Aug 12 '15 at 08:07
  • *In plain SQL I can write a single statement that will return the result count and the top 100 results.* May I ask how you achieve that? `count()` is an aggregate operation, so I don't see how you could combine it with a `select` in a single query (unless you have a way to duplicate the count in every row, maybe). – Frédéric Hamidi Aug 12 '15 at 08:10
  • 1
    @FrédéricHamidi He could write a query that returns two result sets... In this way there is one less roundtrip to the SQL Server... or another favorite trick in SQL is to include the `COUNT()` in each row in a subquery `(SELECT a, b, c, (SELECT COUNT(*) ...) Cnt FROM ... )` ... Hopefully the SQL Server will optimize it and execute it only once. – xanatos Aug 12 '15 at 08:13
  • Just as a note, the trick of including the `COUNT(*)` in each row doesn't work in LINQ-to-SQL... it splits it in two queries. – xanatos Aug 12 '15 at 08:18
  • @TimSchmelter hard to find these duplicates, cause the main question is about pagination, not about doing it in a single statement – jansepke Aug 12 '15 at 08:31
  • @FrédéricHamidi just combine the two statements with a `;` assigning the count to an output parameter will work in MS-SQL, so you will only have one roud trip to the DB server – jansepke Aug 12 '15 at 08:33
  • 2
    You can duplicate the SQL trick of returning the count in each row in LINQ to entities, `var result = context.Table.Select(x => new { Entity = x, Count = context.Table.Count() }).Take(100).ToList(); var count = result.Any() ? result.First().Count : 0; var entities = result.Select(x => x.Entity).ToList();` One SQL statement is created. I can't really see when you would want to though, is the extra round trip really a performance issue. – Mant101 Aug 12 '15 at 08:36
  • @Mant101: your sample needs to execute 4 queries at worst. But you're right that you can include the total-count. But are you sure that this is also omptimized by the query optimizer to not execute it 100 times? – Tim Schmelter Aug 12 '15 at 08:41
  • @Tim Schmelter I'm not sure what you mean by "query" here, you have one Linq to Entities query and one SQL statement is generated for the whole thing, you can test in in LinqPad (I did) and see the SQL generated. Looking at the execution plan I'm not sure SQL sever is making a good job of optimizing it, it looks like half the work is going to compute the count which seems like it too much, but I'm a bit rusty on reading them. I certainly wouldn't even try this unless getting the count was really a performance issue which seems unlikely, – Mant101 Aug 12 '15 at 08:56

0 Answers0