title: Linq left join into new object
body: <p>I haven't done a LEFT JOIN in linq yet, and I am getting very stuck.</p>
  
  <p>At the moment, I have this:</p>
  
  <pre><code>var results = (from csr
                                 in _context.project_sprint_resource
                             join pr in _context.project_resource
                                 on csr.ProjectResourceId equals pr.Id
                              join prpa in _context.project_resource_person_allocation
                                  on pr.Id equals prpa.ProjectResourceId
                                  where csr.ProjectSprintId == sprintId
                             select new SprintResourceDto
                                 {
                                     SprintId = csr.ProjectSprintId,
                                     ProductiveHours = pr.ExpectedProductiveHours,
                                     ProjectResourceId = pr.Id,
                                     ResourceTypeId = pr.ResourceTypeId,
                                     ResourceType = new ReferenceTypeDto
                                         {
                                             Description = pr.resource_type.Description,
                                             Id = pr.resource_type.Id
                                         },
                                     AssignedHours = pr.ExpectedProductiveHours,
                                     Person = new PersonDto
                                         {
                                             Id = prpa.Id,
                                             Firstname = prpa.person.Firstname,
                                             Surname = prpa.person.Surname
                                         },
                                         PersonId = prpa.PersonId
                                 }).ToList();
              return results.ToList();
  </code></pre>
  
  <p>I need to change it so that the "prpa in _context.project_resource_person_allocation" becomes a LEFT join, as this table might not have a matching row.</p>
  
  <p>It looks like a need an INTO, but the examples I see don't make it clear how. </p>
  
  <p>My other option might be to do it in two queries somehow? But, I'm a SQL person, but yet, finding it hard to grasp how to do a LEFT join in linq.</p>
  
  <p>Edit:
  I have progressed a bit, but am having a final issues. I have modified the query, and it seems I am getting the right rows back, BUT... on the cases where there is no right side row (The left join), it crashes, because I can't assign NULL to my Person property (Which is a calss I have created):</p>
  
  <pre><code>        var results = (from csr
                             in _context.project_sprint_resource
                         join pr in _context.project_resource
                             on csr.ProjectResourceId equals pr.Id
                         join prpa in _context.project_resource_person_allocation
                             on pr.Id equals prpa.ProjectResourceId into sub
                         from subq in sub.DefaultIfEmpty()
                         where csr.ProjectSprintId == sprintId
                         select new SprintResourceDto
                             {
                                 SprintId = csr.ProjectSprintId,
                                 ProductiveHours = pr.ExpectedProductiveHours,
                                 ProjectResourceId = pr.Id,
                                 AssignedHours = pr.ExpectedProductiveHours,
                                 ResourceTypeId = pr.ResourceTypeId,
                                 PersonId = subq != null ? subq.PersonId : (int?)null,
                                 ResourceStartDate = pr.StartDate,
                                 ResourceEndDate = pr.EndDate,
                                 Deleted = csr.Deleted.HasValue,
                                 ResourceType = new ReferenceTypeDto
                                     {
                                         Description = pr.resource_type.Description,
                                         Id = pr.resource_type.Id
                                     },
                                 Person = null
                                  //= subq != null ? new PersonDto
                                  //   {
                                  //       Id = subq != null ? subq.Id : 0,
                                  //       Firstname = subq != null ? subq.person.Firstname : "",
                                  //       Surname = subq != null ? subq.person.Surname : ""
                                  //   } : null,
                             });
          return results.ToList();
      }
      catch (Exception e)
      {
          var ne = e;
          return null;
      }
  </code></pre>
  
  <p>The error happens when I attempt to assign NULL to Person. I get an error saying that:</p>
  
  <blockquote>
    <p>Unable to create a null constant value of type
    'SharedObjects.EntityObjects.PersonDto'. Only entity types,
    enumeration types or primitive types are supported in this context.</p>
  </blockquote>
  
  <p>The PersonDto object is defined like this:</p>
  
  <pre><code>public class PersonDto
  {
      public int Id { get; set; }
      public string Firstname { get; set; }
      public string Surname { get; set; }
      public decimal GrossSalary { get; set; }
      public string FullName
      {
          get { return string.Format("{0} {1}", Firstname, Surname); }
      }
  }
  </code></pre>