LINQ to SQL / Entities Query Refactoring with Many Subqueries

Question

I have the following LINQ to Entities query that has many subqueries to get some aggregated 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 the request to include only 2 subqueries, but there can be about 10-15. Is there a way so that I can reorganize the request to clear the code? I am not looking for performance improvements. I want to just clear the code, perhaps by placing the subqueries in separate methods , still making sure that this is one database call. Is it possible?

Answer 1

( let ):

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

:

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

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

where.

subQuery :

          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 */                              
          };

db.

Answer 2

. .

, IEumerable. .

IEnumerable , , . .

:

private MyContext context = new MyContext()
private IEnumerable<User> getUser(Guid userID)
{
    return context.User.Where(c => c.ID == userID);  
}

private void evaluateUser()
{
    bool isUserActive getUser().Any(c => c.IsActive)
}

, . , IEnumerable , . .

Answer 3

, let "", ( ). :

var systems = from s in db.Systems
              orderby s.Name
              let lastException = (from a in s.Applications from e in a.Summaries select e.CreationDate).Max()
              ...

, , - .

var systems = from s in db.Systems
              orderby s.Name
              from summaries in 
                  (from ta in s.Applications
                   from te in ta.Summaries
                   ...
                   select { APPS = ta, SUMMS = te ,/*anything else you want*/ })
              let lastExpire = (from summaries select SUMMS.CreationDate).Max()

, summaries . , , , , summaries , .