Return to Answer

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

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

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.

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

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.

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.

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.