Improving a simple Linq query

Question

I'm new to Linq queries, and I wonder if my query can be improved unilaterally to others:

long vehid = json.VehicleId.Value;
DateTime date = DateTime.Parse(json.date.Value);

var Alerts = (from t2 in entities.Alerts.AsNoTracking()
              where 
                 t2.GeneratedTimeLocal.Year == date.Year
              && t2.GeneratedTimeLocal.Month == date.Month
              && t2.GeneratedTimeLocal.Day == date.Day
              && (t2.AlertType == 2 || t2.AlertType == 3)
              && t2.vId == vid
              select new
              {
                  GeneratedTimeLocal = t2.GeneratedTimeLocal,
                  OptionalText = t2.OptionalText
              });

return Alerts;

The problem is that Alerts data data has a huge amount of data that is growing day by day, and now it is slow. Field GeneratedTimeLocalof data Alerts datatable - a type datetimeoffset (7). Is there any way to improve this query?

Answer 1

Define a date range to improve your query. Then check the query execution plan and based on this, decide whether you need a new index or modify existing indexes.

long vehid = json.VehicleId.Value;
DateTime dateFrom = DateTime.Parse(json.date.Value).Date; // date with no time
DateTime dateTo = dateFrom.AddDays(1); // add one day to create the date range

var Alerts = (from t2 in entities.Alerts.AsNoTracking()
              where 
                 t2.GeneratedTimeLocal >= dateFrom 
              && t2.GeneratedTimeLocal <= dateTo
              && (t2.AlertType == 2 || t2.AlertType == 3)
              && t2.vId == vid
              select new
              {
                  GeneratedTimeLocal = t2.GeneratedTimeLocal,
                  OptionalText = t2.OptionalText
              });

return Alerts;

On the other hand, remember that this request will not be executed until you do ToList (), for example.

Try this index:

CREATE INDEX IX_Alert_GeneratedTimeLocal_vId_AlertType_with_include ON Alert (GeneratedTimeLocal, vId, AlertType) INCLUDE ( )

, SQL Server. , . : https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes