LINQ to Entities query with lots of grouping

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
{
t1.Opportunity_Title,
t2.Company_Name,
s1.OpportunityStatus_Name,
ts = u1.Name,
fs = u2.Name
} into g

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.

StackExchange Code Review Q#19299, answer score: 5