延申阅读:
linq 分为查询方式和方法语法
查询方式,LINQ查询语法以from关键字开头,以select关键字结尾。
var query=from a in b
where 1=1
select a;
方法语法,方法语法(也称为连贯语法)使用Enumerable 或 Queryable静态类中包含的扩展方法,这是一种链式写法
var query=b.Where(1=1);
测试数据源如下:
public class Student : IEquatable<Student> //实现比较接口,才能取差值
{
public string Name { get; set; }
public int Age { get; set; }
public List<TestScore> TestScores { get; set; }
public bool Equals(Student other)
{
return this.Name == other.Name;
}
}
public class TestScore
{
public string Course { get; set; }
public decimal Score { get; set; }
public DateTime TestDate { get; set; }
}
var students = new List<Student>() {
new Student(){
Name ="张三",
Age =18,
TestScores=new List<TestScore>()
{
new TestScore { Course="语文",Score=86m,TestDate=new DateTime(2022,8,1) },
new TestScore { Course="数学",Score=90m,TestDate=new DateTime(2022,8,2) },
new TestScore { Course="英语",Score=67m,TestDate=new DateTime(2022,8,3) }
}
},
new Student (){
Name ="李四",
Age =19,
TestScores=new List<TestScore>()
{
new TestScore { Course="语文",Score=80m,TestDate=new DateTime(2020,8,1) },
new TestScore { Course="数学",Score=75m,TestDate=new DateTime(2021,8,1) },
new TestScore { Course="英语",Score=99m,TestDate=new DateTime(2021,8,1) }
}
},
new Student(){
Name ="张三",
Age =28,
TestScores=new List<TestScore>()
{
new TestScore { Course="语文",Score=76m,TestDate=new DateTime(2022,7,7) },
new TestScore { Course="数学",Score=70m,TestDate=new DateTime(2022,7,7) },
new TestScore { Course="物理",Score=88m,TestDate=new DateTime(2022,7,7) }
}
}
};
let 定义临时变量
var lst = (from a in students
let b = "张三"
where a.Name == b
select a).ToList();
Console.WriteLine(lst[0].Name); //张三
条件 Where
一、查询带下标
string[] arr = { "d", "f", "a", "g", "t" };
var arrResult = arr.Where((item, index) => index < 2).ToList(); //item表示项目 index表示下标 arrResult:d f
二、多个Where Select 相当于多次筛选,等同于Where(a.Name == "张三" && a.Age > 20))
var query = students.Where(a => a.Name == "张三")
.Select(a => a)
.Where(b => b.Age > 20)
.Select(b => b);
foreach (var item in query)
{
Console.WriteLine(item.Name "-" item.Age); //
}
投影 Select
一、转成字典
int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
string[] strings = { "zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine" };
var transDict = (from a in numbers
select new { Key = strings[a], Value = a }
).ToDictionary(x => x.Key);
//five-5 four-4 one-1...
foreach(var item in transDict)
{
Console.WriteLine($"{item.Key}-{transDict[item.Key].Value}");
}
二、匿名对象
var lst = (from a in students
select new { StudName = a.Name, Age = a.Age }
).ToList();
Console.WriteLine(lst[0].StudName); //张三
三、投影元组
var lst = (from a in students
select (StudName: a.Name, Age: a.Age)
).ToList();
Console.WriteLine(lst[0].StudName); //张三
四、输出下标
var lst = students.Select((p, index) => new { StudName = p.Name, RowNum = index 1 }).ToList();
//index:1 - 张三 index:2 - 李四
foreach (var item in lst)
{
Console.WriteLine($"index:{item.RowNum} - {item.StudName}");
}
五、笛卡尔积
int[] numbersA = { 0, 2, 4 };
int[] numbersB = { 1, 3 };
var pairs = from a in numbersA
from b in numbersB
where a < b
select (a, b);
//结果 (0,1) (0,3) (2,3)
foreach (var item in pairs)
{
Console.WriteLine(item);
}
六 、降维操作 SelectMany
string[] arr1 = { "1", "2", "3" };
string[] arr2 = { "a", "b", "c", "d" };
//输出 1a,1b,1c...3d
var arrResult = arr1.SelectMany(x => arr2.Select(y => $"{x}{y}"));
var manyResult = students.SelectMany(x => x.TestScores.Select(y => new { x.Name, y.Course, y.Score }));
//输出 学生:张三 - 课程:语文 - 成绩:86
foreach (var item in manyResult)
{
Console.WriteLine($"学生:{item.Name} - 课程:{item.Course} - 成绩:{item.Score}");
}
7、嵌套查询
var groupQuery = from a in students
from b in a.TestScores
select new { a.Name, b.Score } into tempA
group tempA by tempA.Name into tempB
select new
{
Name = tempB.Key,
MaxScore = tempB.Max(p => p.Score) //求最高分
};
var difficultQuery = from a in students
from b in a.TestScores
select new
{
a.Name,
a.Age,
MaxScore = ( //嵌套查询
from t in groupQuery
where t.Name == a.Name
select t.MaxScore
).FirstOrDefault()
};
foreach (var item in difficultQuery)
{
Console.WriteLine($"学生:{item.Name} - 年龄:{item.Age} - 最高分:{item.MaxScore}");
}
分区
一、Take 取前N个数据;TakeWhile 取序中直到条件为真的数据
int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
var firstNumbersLessThan6 = numbers.TakeWhile(n => n < 6); //结果5,4,1,3 虽然后面还有比6小的值,但排除了
二、Skip 跳过一段序列;SkipWhile 跳过直到满足条件
int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
var skipNumbers = numbers.SkipWhile(n => n<6); //结果9, 8, 6, 7, 2, 0
排序
一、OrderBy...ThenBy
二、自定义比较排序
var arr = new string[] { "a1", "A1", "B1", "b1" };
var newArr = arr.OrderBy(p => p, new CaseInsensitiveComparer());
//自定义不区分大小写的比较器
public class CaseInsensitiveComparer : IComparer<string>
{
public int Compare(string x, string y) =>
string.Compare(x, y, StringComparison.OrdinalIgnoreCase);
}
三、Reverse 翻转
int[] numbers = { 1,2,3 };
var newNumbers = numbers.Reverse(); // {3,2,1}
分组
一、Group
string[] words = { "blueberry", "chimpanzee", "abacus", "banana", "apple", "cheese" };
var wordGroups = from w in words
group w by w[0] into g
select new { FirstLetter = g.Key, Cols = g }; //key分组的依据,g被分组的元素集合
foreach (var item in wordGroups)
{
Console.WriteLine("Letter:" item.FirstLetter); //分组的关键字
foreach (var item2 in item.Cols)
Console.WriteLine(item2); //被分组的元素
}
//多表多字段分组
var query=from a in MES_Products
join b in SYS_Codes.Where(x=>x.Code_type=="pt_type") on a.Pt_type equals b.Code_code
group new {a.Pt_type,b.Code_name,a.Create_time} by new {a.Pt_type,b.Code_name} into g
select new {
g.Key.Pt_type,
g.Key.Code_name,
min_date=g.Min(x=>x.Create_time)
};
二、partition by
var query = from a in students
from b in a.TestScores
select new { a.Name, b.Course, b.Score };
var partitionResult = query.ToList()
.OrderByDescending(p => p.Score)
.GroupBy(p => p.Course)
.Select(g => new { g, count = g.Count() })
.SelectMany(t => t.g.Select(a => a).Zip(Enumerable.Range(1, t.count), (a, b) => new { a.Name, a.Course, a.Score, rn = b }));
foreach (var item in partitionResult.ToList())
Console.WriteLine($"学生:{item.Name} - 课程:{item.Course} - 分数:{item.Score} - 序号:{item.rn}");
var maxResult = partitionResult.Where(p => p.rn == 1).ToList();
foreach (var item in maxResult.ToList())
Console.WriteLine($"学生:{item.Name} - 课程:{item.Course} - 最分数:{item.Score} - 序号:{item.rn}");
集合运算
一、去重 Distinct
var arr = new string[] { "a", "b", "c", "a", "c" }.Distinct(); //a,b,c
var lst = students.Distinct(new StudentEqual()).ToList(); //同名同年龄的只保留一个
二、合并 Union 会去掉重复的
var lst = new int[] { 1, 2, 3 }.Union(new int[] { 9, 1, 2 }); //结果:1,2,3,9
三、交叉 Intersect
var lst = new int[] { 1, 2, 3, 4, 5 }.Intersect(new int[] { 5, 6, 9, 1, 2 }); //1 2 5
四、差值 Except 从a排除b存在的项
var lst = new int[] { 1, 2, 3, 4, 5 }.Except(new int[] { 5, 6, 9, 1, 2 }); //3,4
转换
一、ToList()
立即执行,返回一个List列表
二、ToArray()
立即执行,返回一个数组
三、ToDictionary
var scoreRecords = new[] {
new {Name = "Alice", Score = 50},
new {Name = "Bob" , Score = 40},
new {Name = "Cathy", Score = 45}
};
var scoreRecordsDict = scoreRecords.ToDictionary(sr => sr.Name);
//key:Alice - value:50 key:Bob - value:40...
foreach (var item in scoreRecordsDict)
Console.WriteLine($"key:{item.Key}-value:{scoreRecordsDict[item.Key].Score}");
元素操作
一、First(), FirstOrDefault()
Default()表示如果没有匹配项,返回null,否则报错
二、Last(),LastOrDefault()
取最后一个元素
三、Single(),SingleOrDefault()
Single要求结果是一个元素,而不是数组、多列
var wangwu = students.Where(p => p.Name == "王五").First(); //报错
var wangwuDefault = students.Where(p => p.Name == "王五").FirstOrDefault(); //null
var single1 = students.Single(); //报错,因为结果有2条记录
var single2 = students.Where(p => p.Name == "张三").Single().Age; //18
四、OfType 取指定的类型的数据
object[] numbers = { null, 1.0, "two", 3, "four", 5, "six", 7.0 };
var doubles = numbers.OfType<double>(); //1.0 7.0 只取类型是double的数据
生成
一、Range
var lst = Enumerable.Range(100, 50); //生成增长数序列,100-149共50个数据
二、Repeat
var lst = Enumerable.Repeat(7, 10); //生成重复数序列,7,7,7,7,7,7,7,7,7,7 共10个7
量词
一、Any 类似是否存在,只要有一个匹配即可
var anyResult = students.Any(p=>p.Name=="张三"); //true
二、All 全匹配,所有元素都必须满足条件
var allResult = new int[] { 1, 2, 3, 4, 5 }.All(p => p > 0); //true
聚合
一、Count 统计行数/记录数
二、Sum 求和
三、Min 最小值
四、Max 最大值
五、Average 求平均
六、自定义聚合
double[] doubles = { 1.7, 2.3, 1.9, 4.1, 2.9 };
double product = doubles.Aggregate((a, b) => a * b); //88.33081 所有的乘积
string[] arr = { "a","b","c","d"};
string str = arr.Aggregate((a, b) => a b); //abcd
次序操作
一、SequenceEqual
var wordsA = new string[] { "cherry", "apple", "blueberry" };
var wordsB = new string[] { "cherry", "apple", "blueberry" };
bool match = wordsA.SequenceEqual(wordsB); //True 比较两个对象是否相同,包括次序
二、Concat 类型Union,区别在于Union会去重,而Contact不会
三、ZIP 像拉链一样组合
int[] vectorA = { 0, 2, 4, 5, 6 };
int[] vectorB = { 1, 3, 5, 7, 8 };
//0*1 2*3 4*5 5*7 6*8 = 109
int dotProduct = vectorA.Zip(vectorB, (a, b) => a * b).Sum();
关联
一、Join 类似于SQL的 inner join
二、left join 类似于SQL的 left join
三、cross join
from a in lst1
from b in lst2
where a.bid=b.id
JArray操作
//在JArray中判断空值 JTokenType.Null
jarray.Where(x => x[wost].Type != JTokenType.Null && x[wost].ToString() == arrLevel[i].ToString())
参考:https://www.nhooo.com/linq/linq-tutorial.html
示例主要来自微微的Demo,下载Demo