Optimize LINQ queries

Question

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.

Probably better suited at: http://codereview.stackexchange.com/ — Philip Gullick, Dec 11 '13 at 16:24
Have you tried commenting out the two lines in order to see if the query speeds up? — Aducci, Dec 11 '13 at 16:25
You could probably simplify `m.RecordCreated.Value.Year == fromDate.Year && m.RecordCreated.Value.Month == fromDate.Month && m.RecordCreated.Value.Day == fromDate.Day` to `m.RecordCreated.Value.Date == from.Date`. — rae1, Dec 11 '13 at 16:25
To make things clear: is that LINQ to SQL or LINQ to Entities? — MarcinJuraszek, Dec 11 '13 at 16:26
@MarcinJuraszek LINQ to SQL **is a form of** LINQ to Entities, I believe you meant is that LINQ to SQL or Entity Framework? — Scott Chamberlain, Dec 11 '13 at 16:29
@ScottChamberlain No, there is a different between LINQ to Entities and LINQ to SQL ([What is the difference between “LINQ to Entities”, “LINQ to SQL” and “LINQ to Dataset”](http://stackoverflow.com/questions/2443836/what-is-the-difference-between-linq-to-entities-linq-to-sql-and-linq-to-da)) — MarcinJuraszek, Dec 11 '13 at 16:30
@MarcinJuraszek my MVC model data is stored in 'LINQ to SQL Classes' .dbml's. The code itself? I was under the impression it was just called LINQ. — Mike H., Dec 11 '13 at 16:30

score 5 · Accepted Answer · answered Dec 11 '13 at 16:31

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

So the conversion would be `dataContext.SupplierOrdersViews.Where(filter).Select(m => new { id = m.ID, //etc... }).Etc().Etc()` then? Just did this...looks like its a massive improvement! I'm not very familiar with LINQ but I love it more and more everyday. — Mike H., Dec 11 '13 at 16:40

Optimize LINQ queries

1 Answers1