Timeout during left join using LINQ to Entities

I have the following LINQ query that does a left join:

var results = from v in ctx.dat_Visitor
              join sp in ctx.vwSP on v.lu_Mentor.ID equals sp.ID into sp2
              select new {
                 Visitor = v.Name,
                 Tot = sp2.Count(),
                 SP2 = sp2
              };

My problem is that this LINQ query times out on me. I even upped the default timeout from 15 seconds to 120 seconds and it still times out.

dat_Visitor has no more than 10 records in it, but vwSP (which is a view) has somewhere around 100,000 records.

I know for sure that the timeout has to do with when I attempt to actually return sp2 in the results. If I change my LINQ query to not return sp2 in the results, then it is quick. Without sp2 the query looks like such:

var results = from v in ctx.dat_Visitor
              join sp in ctx.vwSP on v.lu_Mentor.ID equals sp.ID into sp2
              select new {
                 Visitor = v.Name,
                 Tot = sp2.Count()
              };

Lastly, I'm using the 3.5 framework, but I am having serious thoughts about upgrading the website to 4.0, sinc开发者_开发问答e I could then use DefaultIfEmpty().

I'm not sure what you're trying to accomplish here, but your first query actually returns all 100K rows for each v. When you have the server perform some operation on them, that's fine. But once you actually try to move them all over the network, that really makes you pay.

You see, sp2 is not a row from vwSP, but rather a sequence of rows from vwSP that correspond to the current v.

If you wanted to retrieve one sp for every v, return sp2.FirstOrDefault() instead of just sp2.

If you wanted to retrieve all sps with their corresponding vs, you can modify your query like so:

var results = from v in ctx.dat_Visitor
          join sp in ctx.vwSP on v.lu_Mentor.ID equals sp.ID into sps
          from sp in sps
          select new {
             Visitor = v.Name,
             Tot = sps.Count(),
             SP2 = sp
          };

If you wanted something else, please clarify what it was.