【LINQ笔记】join案例

实体

    public class Person
    {
        public string Name { get; set; }
        public int CompanyID { get; set; }
    }
    public class Company
    {
        public int ID { get; set; }
    }
　　　　public class JoinResult
　　　　{ 
　　　　　　public int CompanyID { get; set; }
　　　　　　public string PersonName { get; set; }
　　　　}

List<Company> companyList = new List<Company> {
                new Company{ ID=1},
                new Company{ ID=2},
                new Company{ ID=3}
            };
            List<Person> personList = new List<Person> {
                new Person{ CompanyID=1,Name="zhangsan"},
                new Person{ CompanyID=1,Name="lisi"},
                new Person{ CompanyID=2,Name="wangwu"}
            };

内连接

var list1 = from c in companyList
                       join p in personList on c.ID equals p.CompanyID
                       select new { CompanyID = c.ID, PersonName = p.Name };

内连接第二种方法：

var list2 = companyList.Join<Company, Person, int, JoinResult>(personList, c => c.ID, p => p.CompanyID, (c, p) => {
                return new JoinResult { CompanyID = c.ID, PersonName = p.Name };
            }).ToList();

左连接

personList的数据放入临时变量personTempList中，并调用DefaultIfEmpty(),意思是为空就使用默认值

var leftJoinList = from c in companyList
                       join p in personList on c.ID equals p.CompanyID into personTempList
                       from pTemp in personTempList.DefaultIfEmpty()
                       select new { CompanyID = c.ID, PersonName = pTemp?.Name };

也可以指定默认值：

var list1 = from c in companyList
                       join p in personList on c.ID equals p.CompanyID into personTempList
                       from pTemp in personTempList.DefaultIfEmpty(new Person { Name="DefaultName"})
                       select new { CompanyID = c.ID, PersonName = pTemp.Name };

join多个条件

  var a = from m in DbContext.Set<T1>()
                    join q in DbContext.Set<T2>() 
                    on  new { m.ID, Phone=m.Phone1 } equals new { q.ID, Phone=q.Phone2 }
                    select new { m.ID, m.Phone1 };