开发者

LINQ Join (Left Outer) with Take(1)

开发者 https://www.devze.com 2023-01-17 09:11 出处:网络
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)?

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

0

精彩评论

暂无评论...
验证码 换一张
取 消