I have the below LINQ that is returning zero IF there aren't any Addresses(Inner Join). How would I make this an Outer Join and then only Take(1)
?
var results = query.Join(
DB.tblAddresses.Where(t => t.AddressTypeID == 'm' || t.AddressTypeID == 'b').OrderByDescending(x => x.AddressTypeID),
p => p.PersonID,
a => a.PersonID,
(p, a) =>
new
{
p.PersonID,
开发者_JS百科 p.LastName,
p.FirstName,
p.MiddleName,
p.SSN,
p.BirthDate,
p.DeathDate,
AddressLine1 = a.AddressLine1 ?? string.Empty
});
return results.CopyLinqToDataTable();
Use GroupJoin:
var results = query.GroupJoin(
addresses,
p => p.PersonID,
a => a.PersonID,
(p, a) =>
new
{
p.PersonID,
p.LastName,
p.FirstName,
p.MiddleName,
p.SSN,
p.BirthDate,
p.DeathDate,
AddressLine1 = a.Any()
? (a.First().AddressLine1 ?? string.Empty)
: null
});
query
.SelectMany (
p => DB.tblAddresses.Where((t => t.AddressTypeID == 'm' || t.AddressTypeID == 'b') && t.PersonID == p.PersonID)**.DefaultIfEmpty()**,
(p, t) =>
new
{
p.PersonID,
p.LastName,
p.FirstName,
p.MiddleName,
p.SSN,
p.BirthDate,
p.DeathDate
Addressline1 = (t == null ? null : t.AddressLine1)
}
)
Use DefaultIfEmpty. Take a look at http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/b285be6a-3324-4b16-89cd-856190090701
精彩评论