CODEWITHSUNDEEP
c#linq
Archit Khannna
Archit Khannna

Reputation: 13

How can I optimise this LinQ query?

model.mTable = m_ctx.Shop
 .Where(t => t.CustomerID== model.mFilter.CustomerID)
 .Select(f => new GroupedData
  {
      iGroupID = f.iCustomerID,
      dtGroupDt = new DateTime(f.dtDate.Year, f.dtDate.Month, f.dtDate.Day, f.dtStartTime.Hours, f.dtStartTime.Minutes, f.dtStartTime.Seconds, f.dtStartTime.Milliseconds),
 })
 .OrderByDescending(f => f.dtGroupDt)
 .Take(2)
 .ToList();

Shop is the table and CustomerID is the primary key of the table but if there is a lot of data then this query is giving me timeout exception. Is there any way to optimise this query?

Upvotes: 1

Views: 81

Answers (1)

Camilo Terevinto
Camilo Terevinto

Reputation: 32072

There are 3 things that should help you here:

1) Combine dtDate and dtStartTime into a single column, if possible
2) Create an index on dtDate (see @TimothyStepansky comments) and then
3) Reorder how you execute your query (untested, syntax might not be entirely correct):

model.mTable = m_ctx.Shop
              .Where(t => t.CustomerID== model.mFilter.CustomerID)
              .OrderByDescending(t => t.dtDate)
              .ThenBy(t => t.dtStartTime)
              .Select(f => new GroupedData
              {
                  iGroupID = f.iCustomerID,
                  dtGroupDt = new DateTime(f.dtDate.Year, f.dtDate.Month, f.dtDate.Day, f.dtStartTime.Hours, f.dtStartTime.Minutes, f.dtStartTime.Seconds, f.dtStartTime.Milliseconds),
              })
              .Take(2)
              .ToList();

Upvotes: 1

Related Questions