【LINQ笔记】join、let、into案例

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 };

let、into

let：在linq中定义变量，可以重复使用

var lowercaseStudentNames = from s in studentList
let lowercaseStudentName = s.StudentName.ToLower()
where lowercaseStudentName.StartsWith("r")
select lowercaseStudentName;
//取出单词首字母 a d g j m p s v y
List<string> strList = new List<string> { 
            "abc def ghi",
            "jkl mno pqr",
            "stu vwx yz"
            };
            var firstLetters = from line in strList
                               let words = line.Split(' ')
                               from word in words
                               select word.Substring(0, 1);

into：创建一个临时标识符，该标识符可以存储group、join、select子句结果

var teenAgerStudents = from s in studentList
where s.age > 12 && s.age < 20
select s
into teenStudents
where teenStudents.StudentName.StartsWith("B")
select teenStudents;
var l = from p in personList
                    group p by p.CompanyID into g
                    where g.Count()==2
                    select new { Key = g.Key,Count=g.Count(),Values=g.ToList()};