1
\$\begingroup\$

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>();
        }
    }
\$\endgroup\$
2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.