【转】LINQ的左连接、右连接、内连接

参考地址：https://www.cnblogs.com/shiyh/p/6701839.html

参考地址：https://blog.csdn.net/hangyejiadao/article/details/82229200

参考地址：https://www.cnblogs.com/yxys/p/5895372.html

1、左连接：

var LeftJoin = from t1 in l1
join t2 in l2 on t1.ID equals t2.ID 
into Joinedt12
from t3 in Joinedt12.DefaultIfEmpty()
select new                        
{
Name = t1.Name,
Age=t2.Age                      
};

var list = from a in db.tableInfos1
           join b in db.tableInfos2 on a.ID equals b.Id into bb
           from bbdata in bb.DefaultIfEmpty()
           select new
           {
               info1=a,
               info2 = bbdata ,
           };

说明：新生成的列表list有两个属性info1，info2。其中info1对应的 tableInfos1 表，info2对应的 tableInfos2 表。

若要实现三个表左连接查询：

var list = from a in db.tableInfos1
           join b in db.tableInfo2 on a.ID equals b.Id into bb
           from bbdata in bb.DefaultIfEmpty()
           join c in db.tableInfos3 on a.EID equals c.Id into cc
           from ccdata in cc.DefaultIfEmpty()
           select new
           {
                info1=a,
                info2 = bbdata ,
                info3 = ccdata
           };

2、右连接：

var RightJoin = from t2 in l2
join t1in l1 on t2.ID equals t1.ID 
into joint12
from t3 in joint12.DefaultIfEmpty()
select new                          
{
Name = t1.Name,
Age=t2.Age  
};

3、内连接：

 var query = from t1 in l1
       join t2 in l2
       on t1.ID equals t2.ID
　　　　select new t3
      {
        USER_ID = t1.USER_ID,
        USER_NAME = t1.USER_NAME,
        USER_PASSWORD = t1.USER_PASSWORD,
      };

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

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

多字段关联查询

var a = from m in cx.Set<A>()
join q in cx.Set<B>()
on new { Id = m.Id, EId = m.Ext } equals new { Id = q.BId, EId = q.BExt }
select new { m };

cx.Set<A>().Join(cx.Set<B>(), outer => new {Id = outer.Id, EId = outer.Ext},
inner => new {Id = inner.BId, EId = inner.BExt},
(outer, inner) => new { });