Linq to sql之left join运用示例

示例一：

?

var l= from a in cardsBll.GetCards()

      join b in usersBll.GetAllUsers()

      on a.CardSn equals b.CardSn into temp

      where a.CardSn.Contains(key)

      from t in temp.DefaultIfEmpty()

      select new CardsAndUsersView

      {

          Card_Id = a.Id,

          CardPw = a.CardPw,

          Expries = a.Expries,

          IsEnabled = a.IsEnabled,

          CardSn = a.CardSn,

          CreateDate = a.CreateDate,

          ModifyDate = a.ModifyDate,

          User_Id = t==null?0:t.Id,

          Province = t == null ? "" : t.Province,

          City = t == null ? "" :t.City,

          AreaOrCounty = t == null ? "" : t.AreaOrCounty,

          AddressInfo = t == null ? "" : t.AddressInfo

      };

生成的SQL如下：

+ View Code?

exec sp_executesql N'SELECT TOP (20)

[Project1].[Id] AS [Id],

[Project1].[CardPw] AS [CardPw],

[Project1].[Expries] AS [Expries],

[Project1].[IsEnabled] AS [IsEnabled],

[Project1].[CardSn] AS [CardSn],

[Project1].[CreateDate] AS [CreateDate],

[Project1].[ModifyDate] AS [ModifyDate],

[Project1].[C1] AS [C1],

[Project1].[C2] AS [C2],

[Project1].[C3] AS [C3],

[Project1].[C4] AS [C4],

[Project1].[C5] AS [C5]

FROM ( SELECT [Project1].[Id] AS [Id], [Project1].[CardSn] AS [CardSn], [Project1].[CardPw] AS [CardPw], [Project1].[IsEnabled] AS [IsEnabled], [Project1].[Expries] AS [Expries], [Project1].[CreateDate] AS [CreateDate], [Project1].[ModifyDate] AS [ModifyDate], [Project1].[C1] AS [C1], [Project1].[C2] AS [C2], [Project1].[C3] AS [C3], [Project1].[C4] AS [C4], [Project1].[C5] AS [C5], row_number() OVER (ORDER BY [Project1].[Id] ASC) AS [row_number]

    FROM ( SELECT

        [Extent1].[Id] AS [Id],

        [Extent1].[CardSn] AS [CardSn],

        [Extent1].[CardPw] AS [CardPw],

        [Extent1].[IsEnabled] AS [IsEnabled],

        [Extent1].[Expries] AS [Expries],

        [Extent1].[CreateDate] AS [CreateDate],

        [Extent1].[ModifyDate] AS [ModifyDate],

        CASE WHEN ([Extent2].[Id] IS NULL) THEN 0 ELSE [Extent2].[Id] END AS [C1],

        CASE WHEN ([Extent2].[Id] IS NULL) THEN N'''' ELSE [Extent2].[Province] END AS [C2],

        CASE WHEN ([Extent2].[Id] IS NULL) THEN N'''' ELSE [Extent2].[City] END AS [C3],

        CASE WHEN ([Extent2].[Id] IS NULL) THEN N'''' ELSE [Extent2].[AreaOrCounty] END AS [C4],

        CASE WHEN ([Extent2].[Id] IS NULL) THEN N'''' ELSE [Extent2].[AddressInfo] END AS [C5]

        FROM  [dbo].[Cards] AS [Extent1]

        LEFT OUTER JOIN [dbo].[UserInfo] AS [Extent2] ON ([Extent1].[CardSn] = [Extent2].[CardSn]) OR (([Extent1].[CardSn] IS NULL) AND ([Extent2].[CardSn] IS NULL))

        WHERE (1 = [Extent1].[IsEnabled]) AND ([Extent1].[CardSn] LIKE @p__linq__0 ESCAPE N''~'')

    )  AS [Project1]

)  AS [Project1]

WHERE [Project1].[row_number] > 0

ORDER BY [Project1].[Id] ASC',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'%%'

执行结果：


页面效果：

示例二：

?

DataClasses1DataContext db = new DataClasses1DataContext();

var leftJoinSql = from student in db.Student

join book in db.Book on student.ID equals book.StudentID into temp

from tt in temp.DefaultIfEmpty()

select new

{

    sname= student.Name,

    bname = tt==null?"":tt.Name

};