EF 左连接

1

var aList = menu.GetPagedList(page, limit, out totalCount, filter, orderBy).ToList(); //集合1
var bList = menu.GetAll().ToList(); //集合2
var data = from a in aList
           join b in bList on a.menuParentId equals b.id into temp
           from bb in temp.DefaultIfEmpty()
           select new
           {
               id=a.id,
               menuName = a.menuName,
               menuIcon = a.menuIcon,
               menuParentId = a.menuParentId,
               menuUrl = a.menuUrl,
               menuDescription = a.menuDescription,
               sort = a.sort,
               state = a.state,
               createTime = a.createTime,
               updateTime=a.updateTime,
               parentName = bb == null ?"":bb.menuName //特别要注意这个bb是否为null的情况
           };
var list = data.ToList();

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


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

//left join, 为空时使用默认对象
var leftJoinQuery =  s in db.Student
join c in db.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,
};

三个表的左连接

            var aList = agent.GetPagedList(page, limit, out totalCount, filter, orderBy).ToList();
            var bList = offic.GetAll();
            var cList = package.GetAll();

            var result = from a in aList
                         join b in bList on a.id equals b.agentId into bTemp
                         from bb in bTemp.DefaultIfEmpty()
                         join c in cList on bb.packageId equals c.id into cTemp
                         from cc in cTemp.DefaultIfEmpty()
                         select new
                         {
                             id = a.id,
                             officName = a.name,
                             phone = a.phone,
                             linkman = a.linkman,
                             isSys = a.isSys,
                             state = a.state,
                             type = a.type,
                             createTime = a.createTime,
                             updateTime = a.updateTime,
                             packageName = cc == null ? "" : cc.name,
                             packageId = bb == null ? 0 : bb.packageId,
                             url = bb == null ? "" : bb.url

                         };