LinQ maximum date in a single query

entity-framework linq linq-to-entities

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??

1
9
11/2/2010 3:40:28 PM

Accepted Answer

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.

18
11/2/2010 4:07:19 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow