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());
        }

Corruption

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. – Cultured

Oh I'll take that into account, it is db drivem – Corruption

Speaking of IEnumerable<T>, make sure that what you're getting from db.FindWords(term) is an IQueryable<T>. – Koval

Answer 1

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());
    }

Mestas

Oh I thought I tried this and it didn't work, I must have made a silly error when trying! Thanks. – Corruption

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. – Koval

Answer 2

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());
}

Koeninger

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 – Corruption

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. – Mestas

@Mestas 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 :-) – Platen

Answer 3

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.

Indifference

This doesn't give the count before taking 100 though - unless this works differently than I think – Platen

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. – Cultured