LINQ 左连接(Left Join)

 1. 数据准备

using System.Collections.Generic;

namespace LINQTutorial
{
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int AddressId { get; set; }
        public static List<Employee> GetAllEmployees()
        {
            return new List<Employee>()
            {
              new Employee { ID = 1, Name = "Preety", AddressId = 1},
                new Employee { ID = 2, Name = "Priyanka", AddressId =2},
                new Employee { ID = 3, Name = "Anurag", AddressId = 0},
                new Employee { ID = 4, Name = "Pranaya", AddressId = 0},
                new Employee { ID = 5, Name = "Hina", AddressId = 5},
                new Employee { ID = 6, Name = "Sambit", AddressId = 6}
            };
        }
    }
    public class Address
    {
        public int ID { get; set; }
        public string AddressLine { get; set; }
        public static List<Address> GetAddress()
        {
            return new List<Address>()
            {
                new Address { ID = 1, AddressLine = "AddressLine1"},
                new Address { ID = 2, AddressLine = "AddressLine2"},
                new Address { ID = 5, AddressLine = "AddressLine5"},
                new Address { ID = 6, AddressLine = "AddressLine6"},
            };
        }
    }
}

2.左连接查询写法

using System;
using System.Linq;

namespace LINQTutorial
{
    class Program
    {
        static void Main(string[] args)
        {
            //例子一:
            var query = from emp in Employee.GetAllEmployees()
                        join add in Address.GetAddress() on emp.AddressId equals add.ID into EmpAddGroup
                        from empadd in EmpAddGroup.DefaultIfEmpty()
                        select new { emp, empadd };
            foreach (var item in query)
            {
                Console.WriteLine($"Name:{item.emp.Name}, Address:{item.empadd?.AddressLine}");
            }


            //例子二:
            var query2 = from emp in Employee.GetAllEmployees()
                         join add in Address.GetAddress() on emp.AddressId equals add.ID into EmpAddGroup
                         from empadd in EmpAddGroup.DefaultIfEmpty()
                         select new
                         {
                             EmployeeName = emp.Name,
                             Address = empadd == null ? "NA" : empadd.AddressLine
                         };
            foreach (var item in query2)
            {
                Console.WriteLine($"Name:{item.EmployeeName},Address:{item.Address}");
            }

            Console.ReadLine();
        }
    }
}

3.注意事项

如果有运用到左连接,一定要注意右表可能为空的判断。

个人猜测,其实empadd是代表右表:

 

 

 4.延伸知识

如果要判断一个Employee是否有地址?HasAddress

   //例子三:判断一个Employee是否有地址?
            Console.WriteLine("例子三");
            var query3 = from emp in Employee.GetAllEmployees()
                         join add in Address.GetAddress() on emp.AddressId equals add.ID into EmpAddGroup
                         from empadd in EmpAddGroup.DefaultIfEmpty()
                         select new
                         {
                             EmployeeName = emp.Name,
                             HasAddress = empadd != null
                         };
            foreach (var item in query3)
            {
                Console.WriteLine($"Name:{item.EmployeeName},HasAddress:{item.HasAddress}");
            }

5.测试结果

 

 

 

参考文献
https://dotnettutorials.net/lesson/left-outer-join-in-linq/

自己写的代码在:CSharpBasic\LINQTutorial

posted @ 2022-08-24 16:37  katesharing  阅读(1755)  评论(0)    收藏  举报