LinQ max Date in one Query Optimized

Question

I have the following code:

Decimal initialBalance;
DateTime dailyDate = ctx.DailyBalances.Max(c => c.DailyDate);
          if (dailyDate != null)
              initialBalance = ctx.DailyBalances.Where(c => c.DailyDate == dailyDate).Select(c => c.FinalBalance).FirstOrDefault();
            else
                initialBalance = 0;

            return initialBalance;

Nevertheless i've been trying to get ways to optimized it,making one query instead of one... any sugestion??

Answer 1

Use OrderByDescending and take the first record:

initialBalance = ctx.DailyBalances
   .OrderByDescending(c => c.DailyDate)
   .Select(c => c.FinalBalance)
   .FirstOrDefault();

This type of query is optimized in SQL Server so that it doesn't require an O(n log(n)) sort of the entire table. If there is an index on DailyDate it will find the last row in the index, and without an index it will use an optimized algorithm called Top N Sort that runs in linear time.

However this query will be O(n log(n)) in LINQ to Objects.