Refactoring a LINQ query that (sometimes) returns null

Question

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<Func<Request, RequestResultModel>> 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<Func<Request, RequestResultModel>> 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.Code,
                Event = request.Event,
                StatusName = request.Status.Name,
                Submitted = request.Submitted
            };
});

Which has he following drawbacks:

1. It will not be translated to SQL by EntityFramework
2. It will be executed as multiple queries to the database

Any ideas how I can achieve the desired result?

All assistance will be greatly appreciated.

EDIT: Here is the Request class

public class Request
{
    [Key]
    public int Id { get; set; }

    public virtual Applicant Applicant { get; set; }

    public virtual Reviewer Reviewer { get; set; }

    public virtual Refferrer Refferrer { get; set; }

    [Required]
    public virtual Department Department { get; set; }

    [Required]
    public virtual Discipline Discipline {get; set; }

    [Required]
    public string Event { get; set; }

    public virtual Status Status { get; set; }

    public DateTime Submitted { get; set; }
}

Answer 1

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<Func<Request, RequestResultModel>> 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();