左外连接会检索出LEFT JOIN左表中的所有行,而不管右表是否有匹配项。
今天的一个项目中,需要将classlist表与其他3张表进行连接查询,且classlist表中某列为空的数据也需要查询出来,这就需要进行LEFT JOIN(左外连接查询),且项目使用的是Entity Framework框架,因此,可以使用Linq语句进行左外连接查询。
通过左连接4张表(klc_classlist、klc_object 、klc_student 、klc_license )进行查询,语句如下:
// linq语句多表联合查询
var classlist = from a in dc.klc_classlist
join b in dc.klc_object on a.object_id equals b.id into result1
from ab in result1.DefaultIfEmpty()
join c in dc.klc_student on a.student_id equals c.id into result2
from abc in result2.DefaultIfEmpty()
join d in dc.klc_license on abc.license_id equals d.id into result3
from abcd in result3.DefaultIfEmpty()
where a.order_list.Contains(stu_id_str) && (a.student_id == stu_id || a.student_id == null)
select new
{
id = a.id,
date = a.date,
start_time = a.start_time,
end_time = a.end_time,
duration = a.duration,
object_name = ab.name,
student_name = abc.name,
status = a.status
};