CODEWITHSUNDEEP
c#sqllinq
Mike H.
Mike H.

Reputation: 1751

Optimize LINQ queries

I'd like to optimize my LINQ query.

Orders = (from m in dataContext.SupplierOrdersViews
          where (fromDate != toDate ?
                 m.RecordCreated >= fromDate && m.RecordCreated <= toDate :
                 (m.RecordCreated.Value.Year == fromDate.Year &&
                  m.RecordCreated.Value.Month == fromDate.Month &&
                  m.RecordCreated.Value.Day == fromDate.Day))
          select new
          {
             id = m.ID,
             RecordCreated = m.RecordCreated,
             RecordDeleted = m.RecordDeleted,
             Status = m.Status,
             DepRunningNo = m.DepRunningNo,
             Name = m.Name,
             Address1 = m.Address1,
             VehicleRegNo = m.VehicleRegNo,
             ProductName = m.ProductName,
             Tare = m.Tare < m.Gross ? m.Tare : m.Gross,
             Gross = m.Tare < m.Gross ? m.Gross : m.Tare,
             NetWeight = m.NetWeight,
             NetPrice = m.NetPrice
          }).OrderBy(m => m.RecordCreated).ThenByDescending(m => m.Status != 2).ToList();

I think the issue is with these lines:

Tare = m.Tare < m.Gross ? m.Tare : m.Gross,

Gross = m.Tare < m.Gross ? m.Gross : m.Tare,

How does this work behind the scenes, and is there a better way to accomplish it? I'm happy that it works but its not perfect. This populates a grid with (using default filters) 77 records and it takes like 3 seconds...way too long!

Is there a better way to assign gross/tares? I need to do a check similar to what I have here because the weights are ambiguously stored in the database.

Upvotes: 0

Views: 633

Answers (1)

Servy
Servy

Reputation: 203821

fromDate and toDate are not variable per row; they're fixed for the whole query, so rather than making that check a part of the query, you can do it before the query:

Expression<Func<SupplierOrders, bool>> filter;

if(fromDate != toDate)
    filter = m => m.RecordCreated >= fromDate && m.RecordCreated <= toDate;
else
    filter = m => (m.RecordCreated.Value.Year == fromDate.Year &&
                  m.RecordCreated.Value.Month == fromDate.Month &&
                  m.RecordCreated.Value.Day == fromDate.Day);

dataContext.SupplierOrdersViews.Where(filter)
    //the rest of the query goes here

Upvotes: 5

Related Questions