SQL Join 、LINQ Join、SQO Join

以下内容大部分来自己老外的一篇技术文档，有兴趣的朋友可以研究下；

文档地址：http://www.c-sharpcorner.com/UploadFile/ff2f08/sql-join-in-linq-linq-to-entity-linq-to-sql/

 

---

 

示例：

两个表：EmployeeMaster和DepartmentMaster。这两个表之间的关系和表的虚拟数据如下所示。

部门表：

员工表：

  

---

 Inner Join （产生员工表和部门表的交集）

SQL: 

select e.Code,e.Name,d.Name from EmployeeMaster as e inner join DepartmentMaster as d on e.DepartmentId = d.DepartmentId 

LINQ:

using (var Context = new LinqTestEntities())
            {
                var innerJoin = from e in Context.EmployeeMaster
                                join d in Context.DepartmentMaster 
                                on e.DepartmentId equals d.DepartmentId
                                select new
                                {
                                    EmployeeCode = e.Code,
                                    EmployeeName = e.Name,
                                    DepartmentName = d.Name
                                };
                Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");
                foreach (var data in innerJoin)
                {
                    Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);
                }
}

SQO :

using (var Context = new DBEntity.LinqTestEntities())
            {var innerJoin = Context.EmployeeMaster
                    .Join(
                    Context.DepartmentMaster,
                    s => s.DepartmentId,
                    d => d.DepartmentId,
                    (s, d) => new { s, d })
                    .Where(sd => sd.s.DepartmentId == sd.d.DepartmentId)
                    .ToList();
                Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");
                foreach (var data in innerJoin)
                {
                    Console.WriteLine(data.s.Code + "\t\t" + data.s.Name + "\t" + data.d.Name);
                }
}

查询结果：

 

 

---

 

Left outer join （产生员工表的全集，对于没有匹配的记录，以 null 代替）

SQL： 

SELECT e.Code,e.Name,d.Name FROM EmployeeMaster as e LEFT OUTER JOIN DepartmentMaster as d ON e.DepartmentId = d.DepartmentId

LINQ：

 using (var Context = new LinqTestEntities())
            {
                var leftOuterJoin = from e in Context.EmployeeMaster
                                    join d in Context.DepartmentMaster 
                                    on e.DepartmentId equals d.DepartmentId 
                                    into dept
                                    from department in dept.DefaultIfEmpty()
                                    select new
                                    {
                                        EmployeeCode = e.Code,
                                        EmployeeName = e.Name,
                                        DepartmentName = department.Name
                                    };
                Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");
                foreach (var data in leftOuterJoin)
                {
                    Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);
                }
}

 

查询结果：

 

---

Right outer join （产生员工表的全集，对于没有匹配的记录，以 null 代替）

SQL：

SELECT e.Code,e.Name,d.Name FROM EmployeeMaster as e right outer JOIN DepartmentMaster as d ON e.DepartmentId = d.DepartmentId

 LINQ：

            using (var Context = new LinqTestEntities())
            {
                var rightOuterJoin = from d in Context.DepartmentMaster
                                     join e in Context.EmployeeMaster 
                                     on d.DepartmentId equals e.DepartmentId 
                                     into emp
                                     from employee in emp.DefaultIfEmpty()
                                     select new
                                     {
                                         EmployeeCode = employee.Code,
                                         EmployeeName = employee.Name,
                                         DepartmentName = d.Name
                                     };
                Console.WriteLine("Employee Code\tEmployeeName\tDepartmentName");
                foreach (var data in rightOuterJoin)
                {
                    Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t\t" + data.DepartmentName);
                }
            }

 查询结果：

 

---

Full outer join（产生员工表，部门表的并集，以 null 填充空值）

SQL :

SELECT e.Code,e.Name,d.Name FROM EmployeeMaster  as e FULL OUTER JOIN DepartmentMaster as d ON e.DepartmentId = d.DepartmentId  

 查询结果：