linq to mysql 左关联_Linq 左连接 left join

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 indc.tblRooms

join uiindc.tblUserInfos

on r.UserName equals ui.UserNameinto userroomsfrom ur inuserrooms.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 u.city= c.cid

View Code

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

var list =(from u indc.userinfos

join jindc.jobs on u.job equals j.jid into j_joinfrom x inj_join.DefaultIfEmpty()

join cindc.cities on u.city equals c.cid into c_joinfrom v inc_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();

View Code

3个表 LEFT JOIN 例子：

Emp(员工表)、Dept(部门表)、KqEmp(人员考勤信息表)