• 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如下:

    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
    };
    
  • 相关阅读:
    【模式分解】无损连接&保持函数依赖
    【范式与函数依赖】3NF与BCNF的区别
    C#设置按钮三态背景图片
    C#代码设置窗体和Panel的位置大小
    C#窗体嵌套
    C#中弹出新窗口
    C#定义委托函数实现在别的窗体中操作主窗体中的SerialPort控件
    C#界面设计疑问2:panel摆放问题
    C#拖动自己的定义标题栏(panel)以及实现窗体拖动关闭和最小化
    C#界面设计疑问
  • 原文地址:https://www.cnblogs.com/zhaow/p/9796526.html
Copyright © 2020-2023  润新知