How to get total results count before .Take() - but when using .Take()

Question

I am using .Take() to get a fixed number of results.

What is the best way to get the TotalCountBeforeTake (ie as if I didn't use the .Take())?

Can I get the TotalCountBeforeTake without running the query twice?

        var Results = (from results in db.FindWords(term)
                       orderby results.word
                       select results.word).Take(100);

        //just to get the total record count
        int TotalCountBeforeTake = (from results in db.FindWords(term)
                            select results.word).Count();

        // only showing 100 out of TotalCountBeforeTake results,
        // but in order to know the TotalCountBeforeTake I had to run the query twice.
        foreach (var result in Results)
        {
            Console.Write(result.ToString());
        }

The best way to do this really depends on the `IEnumerable` in question. If backed by a database, then doing multiple queries will be faster. If it were Linq to Objects though, then writing a while loop would probably be faster. — Billy ONeal, Aug 24 '13 at 09:30
Speaking of `IEnumerable`, make sure that what you're getting from `db.FindWords(term)` is an `IQueryable`. — ta.speot.is, Aug 24 '13 at 10:06

score 10 · Accepted Answer · answered Aug 24 '13 at 09:20

10

You want to query two things - the total number of items and a subset of items. So you need to run two queries:

    // Define queries
    var query1 = from results in db.FindWords(term)
                 orderby results.word
                 select results.word;

    var query2 = query1.Take(100);

    // Run queries
    int totalCountBeforeTake = query1.Count();

    foreach (var result in query2)
    {
        Console.Write(result.ToString());
    }

answered Aug 24 '13 at 09:20

dtb

213,145
36
401
431

Oh I thought I tried this and it didn't work, I must have made a silly error when trying! Thanks. – Ian G Aug 24 '13 at 09:23
1

This answer is indeed optimised. Consider a database table of one million words. It would first `COUNT(*)` them and then `SELECT TOP 100 ...` them. Significantly faster than reading one million words and storing them in a list. – ta.speot.is Aug 24 '13 at 09:45

score 1 · Answer 2 · answered Aug 24 '13 at 09:21

1

I don't know of a way to get the count without splitting this up (hopefully someone else does) but in your situation I'd suggest:

//first get the records
var query = (from results in db.FindWords(term)
                       orderby results.word
                       select results.word).ToList();

//get the total record count
int TotalCountBeforeTake = query.Count();

// only showing 100 out of results,
foreach (var result in query.Take(100))
{
    Console.Write(result.ToString());
}

answered Aug 24 '13 at 09:21

Paul D'Ambra

7,629
3
51
96

This is what I thought might be the better solution, i.e. getting once but enumerating twice. I was interested to see if anyone had anything better. Thanks – Ian G Aug 24 '13 at 09:22
2

Note that, if the total number of items is high, this approach will be very slow compared to running two queries, because it has to fully load every item into memory. – dtb Aug 24 '13 at 09:25
@dtb I very nearly suggested essentially the same queries as yours... as always with these things the relative cost of the query versus the enumeration is the thing to balance out... if that db.FindWords(term) is a text search against a table without a full text index you could conceivably have a very expensive query compared to a cheap enumeration. Poke it with a stick and see what happens :-) – Paul D'Ambra Aug 24 '13 at 09:28

score -3 · Answer 3 · answered Aug 24 '13 at 09:19

IEnumerables and LINQ are used to create a selection chain. Before you actually start iterating, nothing is being executed (except for creating the selection chain).

This seems magic, as it drastically boosts performance, because trying to achieve the same with lists requires several iterations over the list(s).

But when you start iterating over an enumerable more than once, you are buying the elegance of LINQ with multiple operations which drops your performance benefit to zero and below.

In other words: convert your linq expression into an array and continue.

 var Results = (from results in db.FindWords(term)
                     orderby results.word
                     select results.word).Take(100).ToArray();

Now you can count, iterate without performance loss.

This doesn't give the count before taking 100 though - unless this works differently than I think — Paul D'Ambra, Aug 24 '13 at 09:23
No, this doesn't work. Counting the array will only tell you "100" or less every time -- not the total number of items the query would have returned. — Billy ONeal, Aug 24 '13 at 09:27

How to get total results count before .Take() - but when using .Take()

3 Answers3

Linked