Refactor LINQ to SQL/Entities Query with many subqueries

Question

I have the following LINQ to Entities query which has many subqueries to get some aggregate data:

var systems = from s in db.Systems
              orderby s.Name
              select new SystemSummary
              {
                  Id = s.Id,
                  Code = s.Code,
                  Name = s.Name,
                  LastException = (
                      from a in s.Applications
                      from e in a.Summaries
                      select e.CreationDate
                  ).Max(),
                  TodaysExceptions = (
                      from a in s.Applications
                      from e in a.Summaries
                      where e.CreationDate >= today && e.CreationDate < tomorrow
                      select e
                  ).Count(),
                  /* SNIP - 10-15 more subqueries */                              
              };

I shortened up the query to only include 2 of the subqueries, but there could be around 10-15 more of them. Is there a way that I can refactor the query to clean up the code? I'm not looking for a performance increase. I want to just clean up the code by maybe putting the subqueries into separate methods while still making sure that it is a single call to the database. Is this possible?

Answer 1

I just can offer minimize its length by something like this (by using let keyword in your original query):

var subQuery =    from a in s.Applications
                  from e in a.Summaries
                  select e;

Also you can have some refactors like:

subQuery.Count(e=>e.CreationDate >= today && e.CreationDate < tomorrow);

subQuery.max(e=>e.CreationDate);

In fact use dot notation and move your query to related function instead of extra where clause.

and use subQuery in your query:

          from s in db.Systems
          orderby s.Name
          let subQuery =    from a in s.Applications
                  from e in a.Summaries
                  select e
          select new SystemSummary
          {
              Id = s.Id,
              Code = s.Code,
              Name = s.Name,
              LastException = subQuery.max(e=>e.CreationDate),
              TodaysExceptions = subQuery.Count(e=>e.CreationDate >= today 
                                          && e.CreationDate < tomorrow),
              /* SNIP - 10-15 more subqueries */                              
          };

This is still single call to db.