6
\$\begingroup\$

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; }
}
\$\endgroup\$
  • 1
    \$\begingroup\$ Have you tried .DefaultIfEmpty() to make a LEFT JOIN instead of the INNER JOIN you're getting with your current code? \$\endgroup\$ – Mathieu Guindon May 30 '14 at 14:20
  • \$\begingroup\$ Listing the Request class could be pretty helpful.. \$\endgroup\$ – Mathieu Guindon May 30 '14 at 14:52
7
\$\begingroup\$

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();
\$\endgroup\$
  • 1
    \$\begingroup\$ Your suggestion to use DefaultIfEmpty() is what worked. Mind updating the answer (for the benefit of others)? \$\endgroup\$ – Alfero Chingono May 30 '14 at 17:26
  • \$\begingroup\$ @adaptive Cool, edited. Feel free to stick around and spend some of your votes - we need more voters on this site :) \$\endgroup\$ – Mathieu Guindon May 30 '14 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.