Linq左连接leftjoin
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"的情况。
多表left join另附：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 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 = vame,
/*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]ame); //字段为null不报异常
//Console.WriteLine(list[i].u1.name+'\t'+list[i].x1.jname+'\t'+list[i].v1ame+"\r\n"); //对象x1 v1 有可能为null 抛异常    }
Console.ReadLine();
View Code
3个表 LEFT JOIN 例⼦：
Emp（员⼯表）、Dept（部门表）、KqEmp（⼈员考勤信息表）

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。