Improve speed of LINQ query

Question

How can I improve the speed of this LINQ query?

using (var txn = new TransactionScope(TransactionScopeOption.Required,
 new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
 var myAlertsList = (from alerts in _db.Alerts.AsNoTracking()
 where alerts.AlertID >= Id.AlertID && alerts.DataWystapienia >Id.DataWystapienia
 select new MyAlerts { AlertID = alerts.AlertID, DataWystapienia = alerts.DataWystapienia, Message = alerts.Message })
 .OrderByDescending(a => a.AlertID).Take(Id.NumberOfRows).Concat((
 from subAlerts in _db.SubAlerts.AsNoTracking()
 where subAlerts.AlertID >= Id.AlertID && subAlerts.DataWystapienia > Id.DataWystapienia
 select new MyAlerts { AlertID = subAlerts.AlertID, DataWystapienia = subAlerts.DataWystapienia, Message = subAlerts.Message })
 .OrderByDescending(c => c.AlertID).Take(Id.NumberOfRows)).OrderByDescending(a => a.AlertID).Take(Id.NumberOfRows);
 return myAlertsList;
}

It seems like the order by date is the longest process in this query. Is this correct? The database has about a million records. NumberOfRows is max 200. Time of this query is 45ms (after using only one orderby). Is there something more that I can do to improve that LINQ query?

• ratchet freak: you have 3 OrderByDescendings followed by takes of which you can remove all but the last
• Malachi: @ratchetfreak, explain that more in an answer, that is a review of the code, and is a good answer as long as you explain it a little bit more.

Answer 1

First lets split up the query so we can read it:

var alertList = from alerts in _db.Alerts.AsNoTracking()
 where alerts.AlertID >= Id.AlertID && alerts.DataWystapienia >Id.DataWystapienia
 select new MyAlerts { AlertID = alerts.AlertID, DataWystapienia = alerts.DataWystapienia, Message = alerts.Message };
var subAlertList = from subAlerts in _db.SubAlerts.AsNoTracking()
 where subAlerts.AlertID >= Id.AlertID && subAlerts.DataWystapienia > Id.DataWystapienia
 select new MyAlerts { AlertID = subAlerts.AlertID, DataWystapienia = subAlerts.DataWystapienia, Message = subAlerts.Message };
var myAlertsList = alertList.OrderByDescending(a => a.AlertID).Take(Id.NumberOfRows)
 .Concat(subAlertList.OrderByDescending(a => a.AlertID).Take(Id.NumberOfRows))
 .OrderByDescending(a => a.AlertID).Take(Id.NumberOfRows);

this is fully equivalent to your code I only extracted the 2 lists

the last operation of code has 3 OrderByDescending(a => a.AlertID).take(Id.NumberOfRows) while only 1 is needed

var myAlertsList = alertList.Concat(subAlertList).OrderByDescending(a => a.AlertID).Take(Id.NumberOfRows);

• Malachi: the inside queries aren't doing anything that needs them to be in order for any reason, you just need the final result to be in order for viewing purposes I assume.