CODEWITHSUNDEEP
linqentity-frameworklinq-to-entities
Nick
Nick

Reputation: 5882

Performance issues with LINQ query when including OR clause

The following LINQ to Entities query slows down substantially when the commented line below is included in the query. Is there a better way to phrase this?

The 'OR' clause should only take into account the following lines:

((o.Type_ID == (int) RecordEnums.RecordType.Lead) && (o.Item_ID == l1.Lead_ID)) 
|| ((o.Type_ID == (int)RecordEnums.RecordType.Opportunity)(o.Item_ID == o1.Opportunity_ID))

Full Query

return withItemsPending
                       ? (from l1 in db.Leads
                          from o1 in db.Opportunities.Where(x => (x.Lead_ID == l1.Lead_ID) && (x.Company_ID == companyId)).DefaultIfEmpty()
                          from l2
                              in
                              db.Tasks.Where(
                                  o =>
                                  ((o.IsCompleted ?? false) == false) &&
                                  (o.TaskType_ID == typeId) &&
                                  ((o.Type_ID == (int) RecordEnums.RecordType.Lead) && (o.Item_ID == l1.Lead_ID)) 
                                  //|| ((o.Type_ID == (int)RecordEnums.RecordType.Opportunity) && (o.Item_ID == o1.Opportunity_ID)) 
                                  &&
                                  (o.Due_Date > EntityFunctions.AddDays(DateTime.Now, -1)))


                          where (l1.Company_ID == companyId)
                          select l1)


                       : (from l1 in db.Leads where (0 == 1) select l1);
        }

Here's the offending query:

SELECT     Extent1.Lead_ID
FROM         Leads AS Extent1 LEFT OUTER JOIN
                      Opportunities AS Extent2 ON Extent2.Lead_ID = Extent1.Lead_ID AND Extent2.Company_ID = 118 INNER JOIN
                      Tasks AS Extent3 ON 0 = (CASE WHEN ([Extent3].[IsCompleted] IS NULL) THEN CAST(0 AS bit) ELSE [Extent3].[IsCompleted] END) AND Extent3.TaskType_ID = 1 AND 
                      5 = Extent3.Type_ID AND Extent3.Item_ID = Extent1.Lead_ID OR
                      4 = Extent3.Type_ID AND Extent3.Item_ID = Extent2.Opportunity_ID AND Extent3.Due_Date > DATEADD(day, - 1, SysDateTime())
WHERE     (Extent1.Company_ID = 118)

Upvotes: 0

Views: 203

Answers (1)

GTG
GTG

Reputation: 4954

If what you want is "give me the items that are not completed AND are due later than yesterday AND are EITHER Lead OR Opportunity", you need to add extra brackets around the 2 statements you want to OR. What you are currently saying is "give me the items that are either not completed AND Lead OR Opportunity AND due later than yesterday".

The code will then be like this:

return withItemsPending
                       ? (from l1 in db.Leads
                          from o1 in db.Opportunities.Where(x => (x.Lead_ID == l1.Lead_ID) && (x.Company_ID == companyId)).DefaultIfEmpty()
                          from l2
                              in
                              db.Tasks.Where(
                                  o =>
                                  ((o.IsCompleted ?? false) == false) &&
                                  (o.TaskType_ID == typeId) &&
                                  (((o.Type_ID == (int) RecordEnums.RecordType.Lead) && (o.Item_ID == l1.Lead_ID)) 
                                  || ((o.Type_ID == (int)RecordEnums.RecordType.Opportunity) && (o.Item_ID == o1.Opportunity_ID)))
                                  &&
                                  (o.Due_Date > EntityFunctions.AddDays(DateTime.Now, -1)))


                          where (l1.Company_ID == companyId)
                          select l1)


                       : (from l1 in db.Leads where (0 == 1) select l1);
        }

Upvotes: 1

Related Questions