示例一:
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如下:
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 };