Linq连接查询之左连接、右连接、内连接、全连接、交叉连接、Union合并、Concat连接、Intersect相交、Except与非查询

内连接查询

内连接与SqL中inner join一样，即找出两个序列的交集

Model1Container model = new Model1Container();
            //内连接
            var query = from s in model.Student
                        join c in model.Course on s.CourseCno equals c.Cno
                        where c.Cno == 1
                        select new
                        {
                            ClassID = s.CourseCno,
                            ClassName = c.Cname,
                            Student = new
                            {
                                Name = s.Sname,
                                ID = s.Sno
                            }
                        };
            foreach (var item in query)
            {
                Response.Write("ClassID:" + item.ClassID + "ClassName:" + item.ClassName + "Name:" + item.Student.Name);
            }

与上面的内连接语句相对应的SQL脚本语言如下所示：

SELECT [t0].[CourseCno] AS [ClassID], [t1].[Cname] AS [ClassName], [t0].[Sname] AS [Name], [t0].[Sno] AS [ID]
FROM [Student] AS [t0]
INNER JOIN [Course] AS [t1] ON [t0].[CourseCno] = [t1].[Cno]
WHERE [t1].[Cno] = @p0

二、左连接

 左(外)连接与SqL中left join一样

Model1Container model = new Model1Container();
            var query = from s in model.Student
                        join c in model.Course on s.CourseCno equals c.Cno into gc
                        from gci in gc.DefaultIfEmpty()
                        select new
                        {
                            ClassID = s.CourseCno,
                            ClassName = gci.Cname,
                            Student = new
                            {
                                Name = s.Sname,
                                ID = s.Sno
                            }
                        };
            //Outer join时必须将join后的表into到一个新的变量gc中，然后要用gc.DefaultIfEmpty()表示外连接。
            foreach (var item in query)
            {
                Response.Write("ClassID:" + item.ClassID + "ClassName:" + item.ClassName + "Name:" + item.Student.Name);
            }

注：上例中使用了DefaultIfEmpty操作符，它能够为实序列提供一个默认的元素。DefaultIfEmpty使用了泛型中的default关键字。default关键字对于引用类型将返回null,而对于值类型则返回0。对于结构体类型，则会根据其成员类型将它们相应地初始化为null(引用类型)或0(值类型)

我们可以不使用default关键字，但在要DefaultIfEmpty中给定当空时的默认对象值。语句如下：

//left join, 为空时使用默认对象
            var leftJoinQuery = from s in model.Student
                                join c in model.Course
                                on s.CourseCno equals c.Cno into gc
                                from gci in gc.DefaultIfEmpty(
                                new Course { Cname = "",Cperiod="" }     //设置为空时的默认值
                                )
                                select new
                                {
                                    ClassID = s.CourseCno,
                                    ClassName = gci.Cname,
                                };

与上面的左外连接语句相对应的SQL脚本语言如下所示：

右连接只要把 查询对象的顺序对调就是了。例如：

1、左连接：
var LeftJoin = from emp in ListOfEmployees
join dept in ListOfDepartment
on emp.DeptID equals dept.ID into JoinedEmpDept
from dept in JoinedEmpDept.DefaultIfEmpty()
select new                        
{
EmployeeName = emp.Name,
DepartmentName = dept != null ? dept.Name : null                        
};
 
2、右连接：
var RightJoin = from dept in ListOfDepartment
join employee in ListOfEmployees
on dept.ID equals employee.DeptID into joinDeptEmp
from employee in joinDeptEmp.DefaultIfEmpty()
select new                          
{
EmployeeName = employee != null ? employee.Name : null,
DepartmentName = dept.Name
};

全连接查询例子：

 var res = from a in sys.Apple
                      from b in sys.Banana
                      select new {
                         a.Id,
                         a.Name,
                         //Bananas = b1
                         t = b.AppleId
                      };

 

LINQ的连接查询通过join字句实现，但一个join字句一次只能连接2个数据源。其基本语法如下：

var query= from a in list1

                 jion b in list2

                 on a.id equals b.id

                select ……

当有3个或更多的数据源需要连接查询时，当个join字句就不能胜任了。解决办法有2个：一是嵌套查询，二是将连接查询的结果和另外的数据源再次连接查询。

第二种方法的实例查询如下：

var query = from x in stuinfo
            join y in studetail
            on x.sid equals y.sid
            select new {sid=x.sid,sname=x.sname,ssex=y.ssex };

var query2 = from z in query
                join k in stuscore
                on z.sid equals k.sid
                select new {sid=z.sid,sname=z.sname,ssex=z.ssex,smath=k.smath
                };
foreach (var t in query2)
{
    listBox1.Items.Add(t.sid+"-"+t.sname+"-"+t.ssex+"-"+t.smath); 
}

 

交叉连接查询

交叉连接与SqL中Cross join一样。如下例中找出SampleData.Publishers与SampleData.Books的交叉连接。

    交叉连接查询语句：

var crossJoinQuery = from publisher in SampleData.Publishers
                                 from book in SampleData.Books
                                 select new
                                 {
                                     PublisherName = publisher.Name,
                                     BookName = book.Title
                                 };

查询操作符语句：

            //不使用查询表达式
            SampleData.Publishers.SelectMany(publisher => SampleData.Books.Select(
                book => new
                {
                    PublisherName = publisher.Name,
                    BookName = book.Title
                }
                ));

 

LinQ中Union合并查询：连接不同的集合，自动过滤相同项；延迟。即是将两个集合进行合并操作，过滤相同的项

var cities = (from p in mylinq.System_Places
          where p.PID == place
          select p).Union(
          from q in mylinq.System_Places
          where q.Parentid==place
          select q
          );

LinQ中的Concat连接查询：连接不同的集合，不会自动过滤相同项；延迟。

  (from p in System_Places
     where p.PID == 1010
     select p).Concat(
       from q in System_Places
         where q.Parentid==1010
         select q
         ).Concat(
           from n in System_Places
             where n.Parentid==1010
              select n
   )

LinQ中的Intersect相交查询：获取不同集合的相同项（交集），即两集合都出现的项。

 (from c in Bst_System_Places
  select c.CnPlaceName).Intersect(
  from e in Bst_Company_Jobs
  select e.WorkPlace)

LinQ中的Except与非查询：排除相交项，即从某集合中排除与另一集合中相同的项，以前集合为主

 (from e in Bst_Company_Jobs
  select e.WorkPlace).Except(
  from c in Bst_System_Places
  select c.CnPlaceName)

 

另可对照sql的连接查询随笔：区分SQL Server关联查询之inner join,left join, right join, full outer join并图解 

http://www.cnblogs.com/shy1766IT/p/5191917.html