How can I reuse a subquery inside a select expression?

Question

In my database I have two tables Organizations and OrganizationMembers, with a 1:N relationship.

I want to express a query that returns each organization with the first and last name of the first organization owner.

My current select expression works, but it's neither efficient nor does it look right to me, since every subquery gets defined multiple times.

await dbContext.Organizations
    .AsNoTracking()
    .Select(x =>
    {
        return new OrganizationListItem
        {
            Id = x.Id,
            Name = x.Name,
            OwnerFirstName = (x.Members.OrderBy(member => member.CreatedAt).First(member => member.Role == RoleType.Owner)).FirstName,
            OwnerLastName = (x.Members.OrderBy(member => member.CreatedAt).First(member => member.Role == RoleType.Owner)).LastName,
            OwnerEmailAddress = (x.Members.OrderBy(member => member.CreatedAt).First(member => member.Role == RoleType.Owner)).EmailAddress
        };
    })
    .ToArrayAsync();

Is it somehow possible to summarize or reuse the subqueries, so I don't need to define them multiple times?

Note that I've already tried storing the subquery result in a variable. This doesn't work, because it requires converting the expression into a statement body, which results in a compiler error.

Answer 1

The subquery can be reused by introducing intermediate projection (Select), which is the equivalent of let operator in the query syntax.

For instance:

dbContext.Organizations.AsNoTracking()
    // intermediate projection
    .Select(x => new
    {
        Organization = x,
        Owner = x.Members
            .Where(member => member.Role == RoleType.Owner)
            .OrderBy(member => member.CreatedAt)
            .FirstOrDefault()
    })
    // final projection
    .Select(x => new OrganizationListItem
    {
        Id = x.Organization.Id,
        Name = x.Organization.Name,
        OwnerFirstName = Owner.FirstName,
        OwnerLastName = Owner.LastName,
        OwnerEmailAddress = Owner.EmailAddress
    })

Note that in pre EF Core 3.0 you have to use FirstOrDefault instead of First if you want to avoid client evaluation.

Also this does not make the generated SQL query better/faster - it still contains separate inline subquery for each property included in the final select. Hence will improve readability, but not the efficiency.

That's why it's usually better to project nested object into unflattened DTO property, i.e. instead of OwnerFirstName, OwnerLastName, OwnerEmailAddress have a class with properties FirstName, LastName, EmailAddress and property let say Owner of that type in OrganizationListItem (similar to entity with reference navigation property). This way you will be able to use something like

dbContext.Organizations.AsNoTracking()
    .Select(x => new
    {
        Id = x.Organization.Id,
        Name = x.Organization.Name,
        Owner = x.Members
            .Where(member => member.Role == RoleType.Owner)
            .OrderBy(member => member.CreatedAt)
            .Select(member => new OwnerInfo // the new class
             {
                 FirstName = member.FirstName,
                 LastName = member.LastName,
                 EmailAddress = member.EmailAddress
             })
            .FirstOrDefault()
    })

Unfortunately in pre 3.0 versions EF Core will generate N + 1 SQL queries for this LINQ query, but in 3.0+ it will generate a single and quite efficient SQL query.

Answer 2

How about this:

await dbContext.Organizations
    .AsNoTracking()
    .Select(x =>
    {
        var firstMember = x.Members.OrderBy(member => member.CreatedAt).First(member => member.Role == RoleType.Owner);
        return new OrganizationListItem
        {
            Id = x.Id,
            Name = x.Name,
            OwnerFirstName = firstMember.FirstName,
            OwnerLastName = firstMember.LastName,
            OwnerEmailAddress = firstMember.EmailAddress
        };
    })
    .ToArrayAsync();