I have been presented with a simple but slow performing LINQ to Entities query which I've pasted below.

Could anybody give me some pointers on how to improve performance? The query below has been significantly improved since I inherited it but I don't know what else I can do without moving to the DB (which isn't an option at this stage)

var created = DateTime.Now.AddMonths(-6);

            return (from t1 in db.Opportunities
                    from s1 in db.OpportunityStatus.Where(x => x.OpportunityStatus_ID == t1.StatusReason_ID)
                    from t2 in db.Leads.Where(x => x.Lead_ID == t1.Lead_ID)
                    from t3 in db.Tasks.Where(x => (x.Type_ID == 4) && (x.Item_ID == t1.Opportunity_ID)).DefaultIfEmpty()
                    from t4 in db.TaskAppointments.Where(x => x.Parent_Task_ID == t3.Task_ID).DefaultIfEmpty()
                    from t5 in db.Addresses.Where(x => x.Address_ID == t4.Address_ID).DefaultIfEmpty()
                    from n1 in db.Notes.Where(x => x.Type_ID == 4 && (x.Item_ID == t1.Opportunity_ID)).DefaultIfEmpty()
                    from n2 in db.Notes.Where(x => x.Type_ID == 5 && (x.Item_ID == t1.Lead_ID)).DefaultIfEmpty()
                    from t6 in db.Quotations.Where(x => x.OpportunityId == t1.Opportunity_ID).DefaultIfEmpty()
                    from u1 in db.UserNames.Where(x => x.User_Username == t1.Owner).DefaultIfEmpty()
                    from u2 in db.UserNames.Where(x => x.User_Username == t2.Owner).DefaultIfEmpty()

                    where (t1.Company_ID == company) && (t1.Created >= created)

                    orderby (t1.Created) descending

                    group new { t1, t3, t4, s1, t6, u1, u2, n1, n2, t5 } by new
                        ts = u1.Name,
                        fs = u2.Name
                    } into g

                    let latestAppointment = g.Max(uh => uh.t4.AppointmentStart)
                    let latestAppointmentStatus = g.FirstOrDefault(x => x.t4.AppointmentStart == latestAppointment).t4.AppointmentStatu.Status_Name
                    let latestAppointmentCity = g.FirstOrDefault(x => x.t4.AppointmentStart == latestAppointment).t5.City

                    let latestNoteDate = g.Max(uh => uh.n2.Date)
                    let latestNote = g.FirstOrDefault(x => x.n2.Date == latestNoteDate).n2.Note_Text

                    let latestOpportunityNoteDate = g.Max(uh => uh.n1.Date)
                    let latestQuotationDate = g.Max(uh => uh.t6.Created)

                    select new PipelineViewModel
                        Id = 0,
                        CompanyName = g.Key.Company_Name,
                        OpportunityTitle = g.Key.Opportunity_Title,
                        CompanyCity = latestAppointmentCity,
                        OpportunityStatusName = g.Key.OpportunityStatus_Name,
                        LastAppointmentDate = latestAppointment,
                        LastAppointmentStatus = latestAppointmentStatus,
                        LastOpportunityNoteDate = latestOpportunityNoteDate,
                        LastNoteDate = latestNoteDate,
                        LastNote = latestNote,
                        LastQuoteDate = latestQuotationDate,
                        TelesalesUserName = g.Key.ts,
                        FieldSalesUserName = g.Key.fs
  • 6
    \$\begingroup\$ I hope there is a strong reason why db.Oportunities has a StatusReason_ID and not a StatusReason, a Lead_ID and not a Lead, etc. Because all these joins? "POOF GONE!" \$\endgroup\$
    – ANeves
    Dec 4, 2012 at 18:06
  • 1
    \$\begingroup\$ What @ANeves said. Also, ordering then grouping probably isn't going to be doing what you want, especially since you're then transforming it later. \$\endgroup\$
    – Bobson
    Dec 4, 2012 at 19:00
  • 1
    \$\begingroup\$ Also, let latestAppointmentStatus = g.FirstOrDefault(x => x.t4.AppointmentStart == latestAppointment).t4.AppointmentStatus.Status_Name will throw a NullReferenceException under some circumstances. \$\endgroup\$
    – Bobson
    Dec 4, 2012 at 19:11
  • 1
    \$\begingroup\$ Does the backing model have any navigation properties defined? Or it's a model with just tables thrown in and no relationships? \$\endgroup\$
    – Jeroen
    Dec 6, 2012 at 3:54

3 Answers 3


This can't be called "simple". And it's not possible to tell where bottleneck is because of so many joins and subqueries.

Subqueries are certainly suspect, you can optimize them by using Dictionary<> (as you would add indexes in real DB).

I would also try to split this huge query into small ones, put ToArray() at the end of each one, and run a profiler to get some hints on what is slow.


Here is my approach which has helped one of my friends to boost the performance of a similar complex query by 450%:

  1. While opening your project in Visual Studio, set a breakpoint at the beginning of your query.
  2. Start with debugging F5.
  3. Wait till the execution hit your breakpoint (hold on).
  4. Run SQL profiler.
  5. Execute your query and wait till the execution is finished.
  6. Grab the SQL query that the SQL profiler has caught.
  7. Open a new query window in SQL server.
  8. Paste your generated SQL query.
  9. Right click, then select "Analyze Query in Database Engine Tuning Advisor".
  10. When the "Database Engine Tuning Advisor"" hit "Start Analysis".
  11. It may take some time to look for suggestions to optimize your query, but when finished, it will tell you the recommended performance gain when applying the recommended modifications.
  12. If the wizard suggested some sort of recommendations, I highly recommend you to apply them, open up "Tools=> Apply Recommendations"
  • \$\begingroup\$ Great comments, but the OP said he wasn't ready to start changing the database. \$\endgroup\$ Dec 6, 2012 at 4:44

You have gone through the hard way when you should follow the easy way!! Why do you need to load so much data all at once??

My suggestion is to break that (stupendous) query into smaller queries and call each one when is needed...(even if you need all at the same time).

But before anything, take a picture of that piece of art! :D


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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