Linq 左连接 left join

http://blog.csdn.net/make1828/article/details/16343103

Suppose you have a tblRoom and tblUserInfo. Now, you need to select all the rooms regardless of whether the room has user information or not. This calls for a LEFT JOIN which will select everything from the LEFT side (the room side) regardless of the join on the right side. Here is the example.

假如你有两张表tblRoom（房 间表）和tblUserInfo（住户表）。

现在你需要检索出所有房间的信息，而不管这个房间是否有人居住。这就需要进行LEFT JOIN（左外连接），左外连接会检索出LEFT JOIN左边表中的所有行，而不管右边的表是否有匹配项。下面是一个例子：

var list = from r in dc.tblRooms
                          join ui in dc.tblUserInfos
                          on r.UserName equals ui.UserNameinto userrooms

                          from ur in userrooms.DefaultIfEmpty()

                          select new
                           {
                               FirstName = (ur.FirstName == null) ? "N/A" : ur.FirstName,
                               LastName = (ur.LastName == null) ? "N/A" : ur.LastName,
                               RoomName = r.Name
                           };

 

 

 

he anonymous type replaces the "null" FirstName and LastName with "N/A" (not available).

使用"N/A"（不可得）代替 FirstName 和 LastName 值为"null"的情况。

另附：Linq实现多个表 LEFT JOIN 如下

目标SQL语句（多表 LEFT JOIN 查询）

 

 

    SELECT id, name, jname, cname   
        FROM userinfo u   
        LEFT JOIN job j on u.job = j.jid   
        LEFT JOIN city c on u.city = c.cid  

 

 

 

 

 Linq To Sql 实现三个表 LEFT JOIN 如下：

    var list = (  
    from u in dc.userinfos  
        join j in dc.jobs on u.job equals j.jid into j_join  
    from x in j_join.DefaultIfEmpty()  
        join c in dc.cities on u.city equals c.cid into c_join  
    from v in c_join.DefaultIfEmpty()  
    select new  
    {  
        id = u.id,  
        name = u.name,  
        jname = x.jname,  
        cname = v.cname,  
        /*u1=u,x1=x,v1=v*/  
        //不要用对象的方式 因为对象可能为null那么对象.属性就会抛异常  
    }  
    ).ToList();  
      
    for (var i = 0; i < list.Count(); i++)  
    {  
        Console.WriteLine(list[i].name + '\t' + list[i].jname + '\t' + list[i].cname); //字段为null不报异常  
        //Console.WriteLine(list[i].u1.name+'\t'+list[i].x1.jname+'\t'+list[i].v1.cname+"\r\n"); //对象x1 v1 有可能为null 抛异常  
    }  
    Console.ReadLine();  

 

3个表 LEFT JOIN 例子：

Emp（员工表）、Dept（部门表）、KqEmp（人员考勤信息表）

/*
             if (listPMessageMapping.Count > 0)
             {
                 foreach (var item in listPMessageShip)
                 {
                     listMessageId.Add(item.MessageId);
                 }

                 foreach (var item in listMessageId)
                 {
                     listMessage1 = listMessage.FindAll(c => c.Id == item);
                 }
             }
             */


             //var listMessages = from PMessShip in listPersonMessageMapping
             //                   join Mess in listMessage
             //                      on PMessShip.MessageId equals Mess.Id into myTable
             //                   from t in myTable.DefaultIfEmpty()

             //                   select new Message
             //                  {
             //                      Id = t.Id,
             //                      MesContent = t.Content
             //                  };


             var listMessages = from Mess in listMessage
                                from PMessShip in listPersonMessageMapping
                                where Mess.Id == PMessShip.MessageId

                                select new Message
                               {
                                   Id = Mess.Id,
                                   MesContent = Mess.Content,
                               };

             List<Message> myMessages = new List<Message>();

            myMessages = listMessages.ToList();