Linq學習(五)-多表連接

本將主要介紹 內連接與 外連接

1.join

Linq to sql

from a in Blog_Users
join b in Blog_UserInfo
on  a.UserId equals b.ID
select new
{
    昵稱=a.NickName,
    真實名=b.RealName
}

sql

SELECT [t0].[NickName] AS [昵稱], [t1].[RealName] AS [真實名]
FROM [Blog_User] AS [t0]
INNER JOIN [Blog_UserInfo] AS [t1] ON ([t0].[UserId]) = [t1].[ID]

Lambda

Blog_Users
   .Join (
      Blog_UserInfo, 
      a => (Int32?)(a.UserId), 
      b => b.ID, 
      (a, b) => 
         new  
         {
            昵稱 = a.NickName, 
            真實名 = b.RealName
         }
   )

2.外連接

Linq to sql

from a in Blog_Users
join b in Blog_UserInfo
on  a.UserId equals b.ID
into ab
from c in ab.DefaultIfEmpty()
select new
{
    昵稱=a.NickName,
    真實名=c.RealName
}

sql

SELECT [t0].[NickName] AS [昵稱], [t1].[RealName] AS [真實名]
FROM [Blog_User] AS [t0]
LEFT OUTER JOIN [Blog_UserInfo] AS [t1] ON ([t0].[UserId]) = [t1].[ID]

Lambda

Blog_Users
   .GroupJoin (
      Blog_UserInfo, 
      a => (Int32?)(a.UserId), 
      b => b.ID, 
      (a, ab) => 
         new  
         {
            a = a, 
            ab = ab
         }
   )
   .SelectMany (
      temp0 => temp0.ab.DefaultIfEmpty (), 
      (temp0, c) => 
         new  
         {
            昵稱 = temp0.a.NickName, 
            真實名 = c.RealName
         }
   )