内连接查询
内连接与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 ListOfEmployeesjoin dept in ListOfDepartmenton emp.DeptID equals dept.ID into JoinedEmpDeptfrom dept in JoinedEmpDept.DefaultIfEmpty()select new {EmployeeName = emp.Name,DepartmentName = dept != null ? dept.Name : null }; 2、右连接：var RightJoin = from dept in ListOfDepartmentjoin employee in ListOfEmployeeson dept.ID equals employee.DeptID into joinDeptEmpfrom 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