in mongodb, what is the most efficient way to get the first and last document

Question

I have documents like this:

class A
{
    DateTime T;
    ...
}

and I would like to find the earliest and the newest document.

Is it better to do this:

        var First   = db.Collection.AsQueryable().OrderBy(_ => _.t).FirstOrDefault();
        var Last    = db.Collection.AsQueryable().OrderByDescending(_ => _.t).FirstOrDefault();

or,

        var First   = db.Collection.AsQueryable().OrderBy(_ => _.t).FirstOrDefault();
        var Last    = db.Collection.AsQueryable().OrderBy(_ => _.t).LastOrDefault();

or

        var C = db.Collection.AsQueryable().OrderBy(_ => _.t);
        var First = C.FirstOrDefault();
        var Last = C.LastOrDefault();

I am wondering if there is anything with the underlying implementation that would change the speed between these options?

I am wondering if the sort has been done once already, is it possible that the result is cached and getting the first and the last elements would be faster?

Answer 1

Profiler becomes your friend when you're not sure about driver syntax. To enable logging for all queries you need to run on your database below statement:

db.setProfilingLevel(2)

Then to check last query executed on the database you need to run:

db.system.profile.find().limit(1).sort( { ts : -1 } ).pretty()

So for the first code snippet you will get:

"pipeline" : [ { "$sort" : { "t" : 1 }  },
               { "$limit" : 1 }
]

"pipeline" : [ { "$sort" : { "t" : -1 }  },
               { "$limit" : 1 }
]

For the second pair it prints

"pipeline" : [ { "$sort" : { "t" : 1 }  },
               { "$limit" : 1 }
]

and throws NotSupportedException for LastOrDefault on my machine, if it works on your MongoDB driver's version you can check generated MongoDB statement using profiler

For the last one when you hover on c in your Visual Studio it prints

{aggregate([{ "$sort" : { "t" : 1 } }])}

but since it's of type IOrderedQueryable<T> it is only not materialized query so it will get executed on the database when you run FirstOrDefault generating the same aggregation body as previous statements. I'm getting NotSupportedException here as well. Here you can find a list of supported LINQ operators and both Last and LastOrDefault are not implemented so you need to sort descending.