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

• Mathieu Guindon: Have you tried .DefaultIfEmpty() to make a LEFT JOIN instead of the INNER JOIN you're getting with your current code?
• Mathieu Guindon: Listing the Request class could be pretty helpful..

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();

• Alfero Chingono: Your suggestion to use DefaultIfEmpty() is what worked. Mind updating the answer (for the benefit of others)?
• Mathieu Guindon: @adaptive Cool, edited. Feel free to stick around and spend some of your votes - we need more voters on this site :)