Group By And Two Counts

Question

I'd like to know if there's any better way to write this query:

(from a in Context.Pages
join b in Context.Visitors on a.PageID equals b.PageFK
join c in Context.Visits on b.VisitorID equals c.VisitorFK
join d in Context.Signups on c.VisitID equals d.VisitFK
where
d.DateOptinUtc.HasValue && a.UserFK == USER_ID
group d by new { a.PageID, a.DateCreated, a.BaseName, a.Name }
into g
select
new ListReport
 {
     DateCreated = g.Key.DateCreated,
     BaseName = g.Key.BaseName,
     Id = g.Key.PageID,
     Name = g.Key.Name,
     Optins = g.Count(),
     Views =
         (from h in Context.Visits
          where
              h.Visitors.PageFK == g.Key.PageID
          select h).Count()
 }).AsQueryable();

Are there any performance issues writing subqueries like these? I don't how I can get that value from the groupby.

Answer 1

Aside from the performance issues you should make the query more readable by using relevant variable names instead of a, b, c, d, g, and h. In addition, the ambiguous ListReport class name should be changed to something more specific like PageUsage since it contains information about a page and how many times it has been viewed/used for signup.

(from page in Context.Pages
join visitor in Context.Visitors on page.PageID equals visitor.PageFK
join visit in Context.Visits on visitor.VisitorID equals visit.VisitorFK
join signup in Context.Signups on visit.VisitID equals signup.VisitFK
where
signup.DateOptinUtc.HasValue && page.UserFK == USER_ID
group signup by new { page.PageID, page.DateCreated, page.BaseName, page.Name }
into signupsByPage
select
new PageUsage
 {
     DateCreated = signupsByPage.Key.DateCreated,
     BaseName = signupsByPage.Key.BaseName,
     Id = signupsByPage.Key.PageID,
     Name = signupsByPage.Key.Name,
     Optins = signupsByPage.Count(),
     Views =
         (from subVisit in Context.Visits
          where
              subVisit.Visitors.PageFK == signupsByPage.Key.PageID
          select subVisit).Count()
 }).AsQueryable();