Optimising multi-part LINQ to Entities query

Question

I have inherited an application which uses LINQ-to-Entities for ORM and don't have a great deal of exposure to this.

There are many examples like this throughout the application. Could you tell me where to start in refactoring this code?

I appreciate that it's a large chunk of code, however the query isn't particularly performant and I wondered what you thought the main bottlenecks are in this case.

 /// <summary>
 /// Get templates by username and company
 /// </summary>
 /// <param name="companyId"></param>
 /// <param name="userName"></param>
 /// <returns></returns>
 public List<BrowsingSessionItemModel> GetItemBrowsingSessionItems(
 int companyId,
 string userName,
 Boolean hidePendingDeletions,
 Boolean hideWithAppointmentsPending,
 Boolean hideWithCallBacksPending,
 int viewMode,
 string searchString,
 List<int?> requiredStatuses,
 List<int?> requiredSources,
 string OrderBy,
 BrowsingSessionLeadCustomField fieldFilter)
 {
 try
 {
 IQueryable<Lead> exclude1;
 IQueryable<Lead> exclude2;
 IQueryable<Lead> exclude3;
 //To prepare call backs pending
 if (hideWithCallBacksPending == true)
 {
 exclude1 = (from l1 in db.Leads
 where (l1.Company_ID == companyId)
 from l2 // Hiding Pending Call Backs
 in db.Tasks
 .Where(o => (o.IsCompleted ?? false == false)
 && (o.TaskType_ID == (int)RecordEnums.TaskType.PhoneCall)
 && (o.Type_ID == (int)RecordEnums.RecordType.Lead)
 && (o.Item_ID == l1.Lead_ID)
 && (o.Due_Date > EntityFunctions.AddDays(DateTime.Now, -1))
 )
 select l1);
 }
 else
 {
 exclude1 = (from l1 in db.Leads
 where (0 == 1)
 select l1);
 }
 //To prepare appointments backs pending
 if (hideWithAppointmentsPending == true)
 {
 exclude2 = (from a1 in db.Leads
 where (a1.Company_ID == companyId)
 from a2 // Hiding Pending Appointments
 in db.Tasks
 .Where(o => (o.IsCompleted ?? false == false)
 && (o.TaskType_ID == (int)RecordEnums.TaskType.Appointment)
 && (o.Type_ID == (int)RecordEnums.RecordType.Lead)
 && (o.Item_ID == a1.Lead_ID)
 && (o.Due_Date > EntityFunctions.AddDays(DateTime.Now, -1))
 )
 select a1);
 }
 else
 {
 exclude2 = (from a1 in db.Leads
 where (0 == 1)
 select a1);
 }
 //To prepare deletions
 if (hidePendingDeletions == true)
 {
 exclude3 = (from d1 in db.Leads
 where (d1.Company_ID == companyId)
 from d2 // Hiding Pending Deletions
 in db.LeadDeletions
 .Where(o => (o.LeadId == d1.Lead_ID))
 select d1);
 }
 else
 {
 exclude3 = (from d1 in db.Leads
 where (0 == 1)
 select d1);
 }
 IQueryable<Lead> list = (from t1 in db.Leads
 from t2
 in db.LeadSubOwners
 .Where(o => t1.Lead_ID == o.LeadId && o.Expiry >= DateTime.Now)
 .DefaultIfEmpty()
 where (t1.Company_ID == companyId)
 where ((t2.Username == userName) && (viewMode == 1)) || ((t1.Owner == userName) && (viewMode == 1)) || ((viewMode == 2)) // Either owned by the user or mode 2 (view all)
 select t1).Except(exclude1).Except(exclude2).Except(exclude3);
 // Filter sources and statuses seperately
 if (requiredStatuses.Count > 0)
 {
 list = (from t1 in list
 where (requiredStatuses.Contains(t1.LeadStatus_ID))
 select t1);
 }
 if (requiredSources.Count > 0)
 {
 list = (from t1 in list
 where (requiredSources.Contains(t1.LeadSource_ID))
 select t1);
 }
 // Do custom field filter here
 if (fieldFilter != null)
 {
 string stringIntegerValue = Convert.ToString(fieldFilter.IntegerValue);
 switch (fieldFilter.FieldTypeId)
 {
 case 1:
 list = (from t1 in list
 from t2
 in db.CompanyLeadCustomFieldValues
 .Where(o => t1.Lead_ID == o.Lead_ID && fieldFilter.TextValue == o.LeadCustomFieldValue_Value)
 select t1);
 break;
 case 2:
 list = (from t1 in list
 from t2
 in db.CompanyLeadCustomFieldValues
 .Where(o => t1.Lead_ID == o.Lead_ID && stringIntegerValue == o.LeadCustomFieldValue_Value)
 select t1);
 break;
 default:
 break;
 }
 }
 List<Lead> itemsSorted; // Sort here
 if (!String.IsNullOrEmpty(OrderBy))
 {
 itemsSorted = list.OrderBy(OrderBy).ToList();
 }
 else
 {
 itemsSorted = list.ToList();
 }
 var items = itemsSorted.Select((x, index) => new BrowsingSessionItemModel
 {
 Id = x.Lead_ID,
 Index = index + 1
 }); 
 return items.ToList();
 }
 catch (Exception ex)
 {
 logger.Info(ex.Message.ToString());
 return new List<BrowsingSessionItemModel>();
 }
 }

Answer 1

You're right... this is a large chunk of code that is a little difficult to digest. With that said, several things come to mind.

• First off, this method is way too large. This is not a style issue perse... In this case, it's so large, it will not be JIT Optimized because the resulting IL will be too large. Break this up into logical sub-functions.

• I would suggest you push portions (if not all) of this back to the database using a SPROC, VIEW, or more likely a combination of the two. I would buy some donuts for your DB folks and make a new friend.

• If that is not an option, I would drop back to ADO.NET and build a single dynamic query that incorporates all the 'exclude' calls, main list call, and the filtering in one shot. This will be a little tedious but should provide you something that is a lot more performant. Keep in mind that old-style ADO.NET is between 150%-300% faster then even the best ORM. That's not a critism because they are really valueable in some areas, especially if you can leverage near-caching options. That said, they're not a solution for everything.