Using Linq to return the count of items from a query along with its resultset

Question

I am using C# MVC4 with Linq.

I have used dependency injection for my project which resulted in me having a separate Model's project along with a separate Repository project (and one for testing ect). All this no problem.

I moved my queries out of the controllers (old style) and into the repository (new DI style), and injected them. It works fine.

I have a standard linq query (pick any example, they are basic enough), which returns a set of items from the database as normal. No problems here either.

My problem is, that I want to implement paging, and I taught it would be simple enough to. Here is my steps:

Take in the results of the linq query from the repository (injected into the controller) store it in a var. It looks something like:

var results = _someInjectedCode.GetListById(SomeId);

Before, I was able to do something simple like:

results.Count()
results.Skip(SomeNum).Take(SomeOtherNum)

But now that I want paging, I need to do my Skip Take something like this:

var results = from xyz in _someInjectedCode.GetListById(SomeId).SomeId).Skip(SomeNum).Take(SomeOtherNum)
select new[] {a,id, a.fName, a.lName .....}

The problem with this is that I no longer have access to the total count of items before the list was shortened to the Pre Skip...Take state unless I do two queries which means hitting the DB twice.

What is the best way to resolve this issue.

Solution 1

I just do it like this:

var result = (from n in mycollection 
              where n.someprop == "some value" 
              select n).ToList();
var count = result.Count;

There are probably other ways, but this is the simplest that I know of.

Solution 2

Thinking about it from a SQL point of view, I can't think of a way in a single normal query to retrieve both the total count and a subset of the data, so I don't think you will be able to do it in LINQ either.

To avoid creating two separate commands, only thing I can think of is a stored proc that returns two tables (one with just the count, the other with your subset of results). It would still execute two queries, but in a single connection. You'd lose your LINQ though. So if you want to keep your LINQ query, you might be stuck with making two separate calls.

The other way is to retrieve the entire unpaged resultset into memory, then run your Take and Skip against the array, but this is pretty wasteful and probably worse than two calls.

Solution 3

You can either add additional parameters to your repository interface/class which will provide paging parameters and return count alongside your result or modify your interfaces to return IQueryable and then apply count and then skip/take before query is compiled and sent for execution.

Comments

[Francis Rodgers]

Using your solution I got something working without making 2 calls to the db. But as Joe Enos suggests, its a little bit more memory intensive. However, at the moment, I just need something that works, and this does. Essentially, I assigned the result of my DI repository to a variable. Done a count on it, then done a skip take using the in memory variable. I just have to hope we don't retrieve 5 million records. :) Thanks.

[Francis Rodgers]

Thanks for your answer. While a little complicated, I do think that long term this is the proper way to do things. I selected another answer because it gave me a quick solution to my problem. But it is not the best solution. I think yours is the best. The problem is that I don't fully understand how to make it happen right now so I have to go with another answer. I will do further research on this though when I get time so I can do it better next time. Thanks for your help.

[Primary Key]

No problem. However I must warn you, the solution you picked seems wrong to me. When you have 100 records in the database you can call ToList and then get the count (of the list!). Paging will be applied to the list (and not to the query). Think about 2 million items in a database query which are loaded into objects each time you execute your query. It's a pretty unfair compensation for an extra db call.

[Tyler Jensen]

I missed it and it was rejected, but it is worth mentioning here. An anonymous user suggested that you could get the same thing by removing the ().ToList() and just execute Count() on the result. NOT TRUE. Doing so would make result an IQueryable and the Count() method would execute the query, returning only the count and NOT the data. Very critical knowledge to include here.