Linq 左连接 left join

 512  2015/04/22 16:40:00


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
View Code




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 = c.cid  
View Code

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

var list = (  
    from u in dc.userinfos  
        join j in on u.job equals j.jid into j_join  
    from x in j_join.DefaultIfEmpty()  
        join c in dc.cities on equals c.cid into c_join  
    from v in c_join.DefaultIfEmpty()  
    select new  
        id =,  
        name =,  
        jname = x.jname,  
        cname = v.cname,  
        //不要用对象的方式 因为对象可能为null那么对象.属性就会抛异常  
    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]'\t'+list[i].x1.jname+'\t'+list[i].v1.cname+"\r\n"); //对象x1 v1 有可能为null 抛异常  
View Code

3个表 LEFT JOIN 例子: