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>();        }    }
Optimising multi-part LINQ to Entities query
c#;linq;entity framework;asp.net mvc 3
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.