How to combine a multi-part LINQ to Entities query

I posted this code snippet over at Stack Exchange's Code Review (beta) in order to obtain some feedback on how best to refactor a multi-part LINQ query.

Being relatively new to LINQ, I'm not really sure where to begin with this query.

If anybody could give me any advice on combining a few of the LINQ queries within the method, I'd appreciate it; especially the 'exclude' IQueryable collections into the main query (see comments).

The query is not particularly performant at the moment and any advice you could give in order to improve its performance from a code perspective would be appreciated.

The comments received on Code Review were more architectural, however I'm not currently in a position where I can move anything to the database at this time.

I appreciate it's a large method, but I've posted the whole lot to give context.

Thanks in advance for any advice you're able to give.

The Method

    /// Get templates by username and company 
    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); 
            } 

            // MAIN QUERY <--
            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>(); 
        } 
    } 

Answers


I don't understand why this:

false == false

And that:

where (0 == 1)

Then for excludes 1 and 2:

            //To prepare call backs pending 
            var phoneCallTypeId = (int) RecordEnums.TaskType.PhoneCall;
            var exclude1 = GetExclude(hideWithCallBacksPending, companyId, phoneCallTypeId);

            //To prepare appointments backs pending       
            var appointmentTypeId = (int) RecordEnums.TaskType.Appointment;
            var exclude2 = GetExclude(hideWithCallBacksPending, companyId, appointmentTypeId);

using the following GetExclude method:

    private object GetExclude(bool hideWithCallBacksPending, int companyId, int typeId)
    {
        return hideWithCallBacksPending
                   ? (from l1 in db.Leads
                      where (l1.Company_ID == companyId)
                      from l2
                          // Hiding Pending Call Backs 
                          in
                          db.Tasks.Where(
                              o =>
                              (o.IsCompleted ?? false) &&
                              (o.TaskType_ID == typeId) &&
                              (o.Type_ID == (int) RecordEnums.RecordType.Lead) &&
                              (o.Item_ID == l1.Lead_ID) &&
                              (o.Due_Date > EntityFunctions.AddDays(DateTime.Now, -1)))
                      select l1)
                   : (from l1 in db.Leads where (0 == 1) select l1);
    }

Exclude3:

            //To prepare deletions    
            var exclude3 = hidePendingDeletions
                           ? (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)
                           : (from d1 in db.Leads where (0 == 1) select d1);

Excludes 1 and 2 can be called inline as they are short:

// Either owned by the user or mode 2 (view all)      
...select t1)
    .Except(GetExclude(hideWithCallBacksPending, companyId, phoneCallTypeId))
    .Except(GetExclude(hideWithCallBacksPending, companyId, appointmentTypeId))
    .Except(exclude3);

Need Your Help

What is the proper MvcContrib 3 syntax for this?

c# asp.net-mvc-3 razor mvccontrib

I inherited an ASP.NET MVC 1 website using MVCContrib 1.5 that I'm trying to upgrade to MVC 3 and MVCContrib 3.

Firefox handles xxx.submit(), Safari doesn't … what can be done?

javascript django django-templates

I'm trying to make a pull down menu post a form when the user selects (releases the mouse) on one of the options from the menu. This code works fine in FF but Safari, for some reason, doesn't subm...