2
\$\begingroup\$
Job -> Many Quotes 
Quote -> EmailLogList
EmailLogList -> Many EmailLogs

I want to return a CreatedDate from an EmailLog which has been created most recently - using a Job.Guid.

Is this the best way possible?

public DateTime? GetMostRecentEmailLog(Guid jobGuid)
{
    using (DbContext dbContext = new DbContext())
    {
        IEnumerable<Guid?> logLists = dbContext
            .Jobs
            .Include(j => j.Quotes)
            .First(j => j.Guid == jobGuid)
            .Quotes
            .Where(q => q.EmailLogListGuid != null)             
            .Select(q => q.EmailLogListGuid);

        return dbContext
            .EmailLogs
            .OrderByDescending(e => e.CreatedDate)                  
            .Where(e => logLists.Contains(e.EmailLogListGuid))
            .Select(e => e.CreatedDate)
            .FirstOrDefault();                              
    }
}
\$\endgroup\$
3
\$\begingroup\$

This frequently used pattern...

entities.First().Property

... is hardly ever an economical way to do querying. It fetches a complete entity from the database of which subsequently all but one property is discarded. Or, when Property is a lazy loaded collection, everything is discarded.

In general a better way is...

entities.Where(e => e.Id == id).Select(e => e.Property).First()

... because this only gets the single property from the database.

Or, if Property is a collection,...

entities.Where(e => e.Id == id)
        .SelectMany(e => e.Property)
        .Select(p => p.Property1)

Applying this would turn the first statement into...

IEnumerable<Guid> logLists = dbContext.Jobs
                                      .Where(j => j.Guid == jobGuid)
                                      .SelectMany(j => j.Quotes)
                                      .Where(q => q.EmailLogListGuid != null)             
                                      .Select(q => q.EmailLogListGuid.Value);

The second part is OK. Getting the most recent item from a collection always requires some ordering that defines "most recent".

Note that using the improved form, EF will translate this into one query, because logList isn't materialized yet. It is an expression tree1 that can be merged with the second expression. If you want, you can create one statement in LINQ as well:

return dbContext.Jobs
                .Where(j => j.Guid == jobGuid)
                .SelectMany(j => j.Quotes)
                .SelectMany(q => dbContext.EmailLogs
                    .Where(e => q.EmailLogListGuid == e.EmailLogListGuid))
                .OrderByDescending(e => e.CreatedDate)
                .Select(e => e.CreatedDate)
                .FirstOrDefault();

Or, if Quote has a navigation property EmailLogs (recommended):

return dbContext.Jobs
                .Where(j => j.Guid == jobGuid)
                .SelectMany(j => j.Quotes)
                .SelectMany(q => EmailLogs)
                .OrderByDescending(e => e.CreatedDate)
                .Select(e => e.CreatedDate)
                .FirstOrDefault();

1 Even though you cast the expression tree to IEnumerable, it's still an IQueryable containing an expression.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.