Refactoring a LINQ query that (sometimes) returns null

I have the following LINQ query that should return a result for each Request. I'd prefer to have the query translated to SQL by EntityFramework.

private static Expression> requestResultExpression = request =>
    request.Applicant
            .ApplicantAddresses
            .OrderBy(address => address.IsPreferred)
            .Select(address => new RequestResultModel
            {
                Id = request.Id,
                ApplicantName = request.Applicant.FullName,
                ReviewerName = request.Reviewer.FullName,
                RefferrerName = request.Refferrer.Name,
                City = address.Address.City,
                Province = address.Address.Province.Code,
                DisciplineCode = request.Discipline.Code,
                Event = request.Event,
                StatusName = request.Status.Name,
                Submitted = request.Submitted
            })
            .FirstOrDefault();

The problem is that when the Applicant has no ApplicantAddresses, then the expression returns null for that specific Request. The correct behavior should be that a RequestResultModel should still be returned but with empty address information.

Unfortunately, I can only think of doing this in two steps like this:

```
private static Expression> requestResultExpression = request =>
{
var address = request.Applicant
.ApplicantAddresses
.OrderBy(address => address.IsPreferred)
.FirstOrDefault();

return new RequestResultModel
{
Id = request.Id,
ApplicantName = request.Applicant.FullName,
ReviewerName = request.Reviewer.FullName,
RefferrerName = request.Refferrer.Name,
City = address == null ? null : address.Address.City,
Province = address == null ? null : address.Address.Province.Code,
DisciplineCode = request.Discipline.Cod

Your current code generates an INNER JOIN, which as you've noticed, excludes any Applicant without an ApplicantAddress.

You want to select from Applicant and generate a LEFT JOIN on ApplicantAddress, this SO answer shows how to use DefaultIfEmpty() to do that:

var query = from u in usergroups
            join p in UsergroupPrices on u equals p.UsergroupID into gj
            from x in gj.DefaultIfEmpty()
            select new { 
                UsergroupID = u.UsergroupID,
                UsergroupName = u.UsergroupName,
                Price = (x == null ? String.Empty : x.Price) 
            };

It's hard to tell exactly what/how to change in your code to make it work, because we're not seeing the DbContext and it's not clear how Request turns out hitting the database; I'd rather not say anything than assume what's going on.

In this specific case:

private static Expression> requestResultExpression = request =>
    request.Applicant
            .ApplicantAddresses
            .DefaultIfEmpty()
            .OrderBy(address => address.IsPreferred)
            .Select(address => new RequestResultModel
            {
                Id = request.Id,
                ApplicantName = request.Applicant.FullName,
                ReviewerName = request.Reviewer.FullName,
                RefferrerName = request.Refferrer.Name,
                City = address.Address.City,
                Province = address.Address.Province.Code,
                DisciplineCode = request.Discipline.Code,
                Event = request.Event,
                StatusName = request.Status.Name,
                Submitted = request.Submitted
            })
            .FirstOrDefault();

StackExchange Code Review Q#52080, answer score: 7