How do I return a record even if they have no records in a child table?

The following query returns a list of employees and their names, email address, and respective company names. The problem is with employees that do not have an email address in our system. If an employee doesn't have an em开发者_Go百科ail address, they are excluded entirely from the resultset.

How do I return null (or empty string, etc) for an email address that doesn't exist, rather than excluding the employee?

var employees = from e in Employees where e.ContactAttributes.Any (ca => ca.AttributeID == 19730317 )
from om in e.ChildOrganizationMaps
from ea in e.EmailAddresses
select new {e.FName, e.LName, ea.EmailAddress, om.Parent.CompanyName};

employees.Dump();

Your query is using joins. You can either use an outer join, or use Include for "eager loading" so that you will pull all entities and their relations:

var employees = Employees.Include("ChildOrganizationMaps").Include("EmailAddresses").Where(e=>e.ContactAttributes.Any(ca.AttributeID = 19730317)
.Select(e => new {e.FName, e.LName, e.EmailAddress.Address});

Use an outer join. It will return null for fields in the table which doesn't have a row.

Edit: Ok, do it with explicit left join (DefaultIfEmpty) than.

var employees = 
from e in Employees
from om in ChildOrganizationMaps
  .where(o => e.pkKey == o.fkKey).DefaultIfEmpty()
from ea in EmailAddresses
  .where(o => e.pkKey == o.fkKey).DefaultIfEmpty()
where 
  e.ContactAttributes.Any (ca => ca.AttributeID == 19730317 )
select new 
{
  e.FName, 
  e.LName, 
  ea.EmailAddress, 
  om.Parent.CompanyName
};