LINQ学习

LINQ

LINQ是.NET Core中提供的简化数据查询的技术。使用LINQ技术，可以用几行代码就实现复杂的数据查询。LINQ不仅可以对普通的.NET集合进行查询，而且在Entity Frameword Core中应用广泛，因此必须熟练掌握LINQ。

要想熟悉掌握LINQ就的熟悉掌握委托——>Lambda——>LINQ

委托的用法
委托类型规定了方法的返回值和参数的类型

MyDelegate d1 = SayEnglish;
string s1 = d1(3);
Console.WriteLine(s1); // Hello 3
d1 = SayChinese;
string s2 = d1(10);
Console.WriteLine(s2); // 你好 10
static string SayEnglish(int age)
{
    return $"Hello {age}";
}
static string SayChinese(int age)
{
    return $"你好 {age}";
}
delegate string MyDelegate(int n);

委托类型MyDelegate的变量d1可以指向与MyDelegate类型相容的方法，然后我们就可以像调用方法一样调用委托类型的变量。调用委托变量的时候执行的就是变量指向的方法。

在.NET中定义了最多可达16个参数的泛型类型委托Action(无返回值)和Fnc(有返回值)，因此一般我们不需要自定义委托类型，可以直接使用Action或者Func这两个委托类型。
委托变量不仅可以指向普通方法，也可以指向匿名方法

Func<int,int,string> f1= delegate(int il, int i2)
{
    return $"{il}+{i2}={il + i2}";
};
string s=f1(1,2);
Console.WriteLine(s);

定义匿名方法也可以采用Lambda表达式的语法。

Func<int,int,string> f1= (il, i2) =>
{
    return $"{il}+{i2}={il + i2}";
};

俩段代码中,使用Lambda表达式,去掉了delegate关键字，并且省略了参数的数据类型，因为编译器能根据委托类型推断出参数的类型，然后用=>作为定义方法体的关键字。

但这段代码还可以进一步简化这些代码。如果=>之后方法体只有一行代码，并且方法有返回值，那么还可以省略方法体的花括号及return关键字。

Func<int,int,string> f1=(il,i2)=>$"{il}+{i2}={il + i2}";

方法没有返回值，方法体只有一个代码可：

Action<int,string> a1=(age,name)=>Console.WriteLine($"年龄{age},姓名{name}");

如果只有一个方法只有一个参数可：

Func<int,int> f1=il=>il*2;

Lambda表达式例子

int[] arrays={2,8,29,19,12,13,99,89,105,108,81};
var nums2=MyWhere(arrays,n=>n>30);
Console.WriteLine(string.Join(",",nums2));
var nums3 = MyWhere(arrays, n => n % 2 == 0);
Console.WriteLine(string.Join(",",nums3));
static IEnumerable<int> MyWhere(IEnumerable<int> nums, Func<int, bool> filter)
{
    foreach (int n in nums)
    {
        if (filter(n)) yield return n;
    }
}

如果不用Lambda表达式，用匿名方法的写法：

Func<int,bool> f1= delegate(int n)
{
    return n > 30;
};
var nums2=MyWhere(arrays,f1);

可见Lambda表达式让我们编写匿名方法更简单。

LINQ 常用扩展方法

LINQ中提供类似Where的扩展方法，简化数据处理。

Where方法

Where方法：每一项数据都会经过predicate的测试，如果针对一个元素，predicate执行的返回值为true,那么这个元素就会放到返回值中。
Where参数是一个lambda表达式格式的匿名方法，方法的参数e表示当前判断的元素对象。参数的名字不一定非要叫e，不过一般lambda表达式中的变量名长度都不长。

List<Employee> employees = new List<Employee>();
employees.Add(new Employee { Id = 1, Name = "Tim", Age = 28, Gender = true, Salary = 10000 });
employees.Add(new Employee { Id = 2, Name = "Tim", Age = 18, Gender = false, Salary = 20000 });
employees.Add(new Employee { Id = 3, Name = "Tim", Age = 20, Gender = true, Salary = 30000 });
employees.Add(new Employee { Id = 4, Name = "Tim", Age = 22, Gender = true, Salary = 40000 });
employees.Add(new Employee { Id = 5, Name = "Tim", Age = 24, Gender = false, Salary = 50000 });
employees.Add(new Employee { Id = 6, Name = "Tim", Age = 26, Gender = true, Salary = 60000 });
employees.Add(new Employee { Id = 7, Name = "Tim", Age = 27, Gender = false, Salary = 70000 });
employees.Add(new Employee { Id = 8, Name = "Tim", Age = 19, Gender = true, Salary = 80000 });
IEnumerable<Employee> items1 = employees.Where(e => e.Age > 25);
foreach (Employee e in items1)
{
    Console.WriteLine(e);
}
class Employee
{
    public long Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public bool Gender { get; set; }
    public int Salary { get; set; }
    public override string ToString()
    {
        return $"Id:{Id},Name:{Name},Age:{Age},Gender={Gender},Salary={Salary}";
    }
}

Count()方法：获取数据条数

List<Employee> employees = new List<Employee>();
employees.Add(new Employee { Id = 1, Name = "Tim", Age = 28, Gender = true, Salary = 10000 });
employees.Add(new Employee { Id = 2, Name = "Tim", Age = 18, Gender = false, Salary = 20000 });
employees.Add(new Employee { Id = 3, Name = "Tim", Age = 20, Gender = true, Salary = 30000 });
employees.Add(new Employee { Id = 4, Name = "Tim", Age = 22, Gender = true, Salary = 40000 });
employees.Add(new Employee { Id = 5, Name = "Tim", Age = 24, Gender = false, Salary = 50000 });
employees.Add(new Employee { Id = 6, Name = "Tim", Age = 26, Gender = true, Salary = 60000 });
employees.Add(new Employee { Id = 7, Name = "Tim", Age = 27, Gender = false, Salary = 70000 });
employees.Add(new Employee { Id = 8, Name = "Tim", Age = 19, Gender = true, Salary = 80000 });
IEnumerable<Employee> items1 = employees.Where(e => e.Age > 25);
foreach (Employee e in items1)
{
    Console.WriteLine(e);
}

Console.WriteLine(employees.Count());
Console.WriteLine(employees.Count(e=>e.Age>25&&e.Salary>50000));

class Employee
{
    public long Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public bool Gender { get; set; }
    public int Salary { get; set; }
    public override string ToString()
    {
        return $"Id:{Id},Name:{Name},Age:{Age},Gender={Gender},Salary={Salary}";
    }
}

Any()方法：是否至少有一条数据

满足条件返回true,不满足返回false

Console.WriteLine(employees.Any(e=>e.Salary>80000))
Console.WriteLine(employees.Any(e=>e.Salary<10000))

获取一条数据 LINQ中有4组获取一条数据的方法，分别是Single、SingleOrDefault、First和FirstOrDefault。

Single:如果确认有且只有一条满足要求的数据，那么就用Single方法。如果没有满足条件的数据，或条件的数据多于一条，Single方法就会抛出异常。

SingleOrDefault:如果确认最多只有一条满足要求的数据，那么就用SingleOrDefault方法。如果没有满足条件的数据，SingleOrDefault方法就会抛出异常。

First:如果满足条件的数据有一条或多条，First方法就会返回第一条数据；如果没有满足条件的数据，First方法就会抛出异常。

FirstOrDefault:如果满足条件的数据有一条或者多条，FirstOrDefault方法就会返回第一条数据；如果没有满足条件的数据，FirstOrDefault方法就会返回类型的默认值。

List<Employee> employees = new List<Employee>();
employees.Add(new Employee { Id = 1, Name = "Tim", Age = 28, Gender = true, Salary = 10000 });
employees.Add(new Employee { Id = 2, Name = "Tim", Age = 18, Gender = false, Salary = 20000 });
employees.Add(new Employee { Id = 3, Name = "Tim", Age = 20, Gender = true, Salary = 30000 });
employees.Add(new Employee { Id = 4, Name = "Tim", Age = 22, Gender = true, Salary = 40000 });
employees.Add(new Employee { Id = 5, Name = "Tim", Age = 24, Gender = false, Salary = 50000 });
employees.Add(new Employee { Id = 6, Name = "Tim", Age = 26, Gender = true, Salary = 60000 });
employees.Add(new Employee { Id = 7, Name = "Tim", Age = 27, Gender = false, Salary = 70000 });
employees.Add(new Employee { Id = 8, Name = "Tim", Age = 19, Gender = true, Salary = 80000 });

Employee e1 = employees.Single(e => e.Id == 6);
Console.WriteLine(e1);
Employee? e2 = employees.SingleOrDefault(e => e.Id == 9);
if(e2==null) Console.WriteLine("没有Id==9的数据");
else Console.WriteLine(e2);
Employee e3 = employees.First(e => e.Age > 24);
Console.WriteLine(e3);
Employee? e4 = employees.FirstOrDefault(e => e.Age > 30);
if (e4 == null) Console.WriteLine("没有大于30岁的数据");
else Console.WriteLine(e4);
Employee e5 = employees.First(e => e.Salary > 99999);

class Employee
{
    public long Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public bool Gender { get; set; }
    public int Salary { get; set; }
    public override string ToString()
    {
        return $"Id:{Id},Name:{Name},Age:{Age},Gender={Gender},Salary={Salary}";
    }
}

排序

OrderBy方法可以对数据进行正向排序，而OrderByDescending方法则可以对数据继续逆向排序

List<Employee> employees = new List<Employee>();
employees.Add(new Employee { Id = 1, Name = "Tim", Age = 28, Gender = true, Salary = 10000 });
employees.Add(new Employee { Id = 2, Name = "Tim", Age = 18, Gender = false, Salary = 20000 });
employees.Add(new Employee { Id = 3, Name = "Tim", Age = 20, Gender = true, Salary = 30000 });
employees.Add(new Employee { Id = 4, Name = "Tim", Age = 22, Gender = true, Salary = 40000 });
employees.Add(new Employee { Id = 5, Name = "Tim", Age = 24, Gender = false, Salary = 50000 });
employees.Add(new Employee { Id = 6, Name = "Tim", Age = 26, Gender = true, Salary = 60000 });
employees.Add(new Employee { Id = 7, Name = "Tim", Age = 27, Gender = false, Salary = 70000 });
employees.Add(new Employee { Id = 8, Name = "Tim", Age = 19, Gender = true, Salary = 80000 });

Console.WriteLine("--------按照年龄正序排列---------");
var orderedItems1 = employees.OrderBy(e => e.Age);
foreach (var item in orderedItems1)
{
    Console.WriteLine(item);
}
Console.WriteLine("--------安照工资倒序排列---------");
var orderedIdems2 = employees.OrderByDescending(e => e.Salary);
foreach (var item in orderedIdems2)
{
    Console.WriteLine(item);
}

class Employee
{
    public long Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public bool Gender { get; set; }
    public int Salary { get; set; }
    public override string ToString()
    {
        return $"Id:{Id},Name:{Name},Age:{Age},Gender={Gender},Salary={Salary}";
    }
}

限制结果集

限制结果集用来从集合中获取部分数据，其主要应用场景是分页查询，比如从第二页开始获取3条数据。
Skip(n)方法用于跳过n条数据，Take(n)方法用于获取n条数据。

Skip、Take方法也可以单独使用，employees.Skip(2)用于跳过2条数据,直到获取最后一条数据，反之employees.Take(2)获取前2条数据。

List<Employee> employees = new List<Employee>();
employees.Add(new Employee { Id = 1, Name = "Tim", Age = 28, Gender = true, Salary = 10000 });
employees.Add(new Employee { Id = 2, Name = "Tim", Age = 18, Gender = false, Salary = 20000 });
employees.Add(new Employee { Id = 3, Name = "Tim", Age = 20, Gender = true, Salary = 30000 });
employees.Add(new Employee { Id = 4, Name = "Tim", Age = 22, Gender = true, Salary = 40000 });
employees.Add(new Employee { Id = 5, Name = "Tim", Age = 24, Gender = false, Salary = 50000 });
employees.Add(new Employee { Id = 6, Name = "Tim", Age = 26, Gender = true, Salary = 60000 });
employees.Add(new Employee { Id = 7, Name = "Tim", Age = 27, Gender = false, Salary = 70000 });
employees.Add(new Employee { Id = 8, Name = "Tim", Age = 19, Gender = true, Salary = 80000 });

var orderedItems3 = employees.Skip(2);
foreach (var item in orderedItems3)
{
    Console.WriteLine(item);
}

var orderdItems4 = employees.Take(3);
foreach (var item in orderdItems4)
{
    Console.WriteLine(item);
}

var orderdItems5 = employees.Skip(2).Take(3);
foreach (var item in orderdItems5)
{
    Console.WriteLine(item);
}

class Employee
{
    public long Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public bool Gender { get; set; }
    public int Salary { get; set; }
    public override string ToString()
    {
        return $"Id:{Id},Name:{Name},Age:{Age},Gender={Gender},Salary={Salary}";
    }
}

聚合函数

在SQL中Max、Min、Avg、Sum、Count的聚合函数。LINQ中也有对应的方法，它们的名字分别是Max、Min、Average、Sum、Count,这些方法也可以和Where、Skip、Take等方法一起使用。

List<Employee> employees = new List<Employee>();
employees.Add(new Employee { Id = 1, Name = "Tim", Age = 28, Gender = true, Salary = 10000 });
employees.Add(new Employee { Id = 2, Name = "Tim", Age = 18, Gender = false, Salary = 20000 });
employees.Add(new Employee { Id = 3, Name = "Tim", Age = 20, Gender = true, Salary = 30000 });
employees.Add(new Employee { Id = 4, Name = "Tim", Age = 22, Gender = true, Salary = 40000 });
employees.Add(new Employee { Id = 5, Name = "Tim", Age = 24, Gender = false, Salary = 50000 });
employees.Add(new Employee { Id = 6, Name = "Tim", Age = 26, Gender = true, Salary = 60000 });
employees.Add(new Employee { Id = 7, Name = "Tim", Age = 27, Gender = false, Salary = 70000 });
employees.Add(new Employee { Id = 8, Name = "Tim", Age = 19, Gender = true, Salary = 80000 });

int maxAge = employees.Max(e => e.Age);
Console.WriteLine($"最大年龄：{maxAge}");
long minId = employees.Min(e => e.Id);
Console.WriteLine($"最小Id：{minId}");
double avgSalary = employees.Average(e => e.Salary);
Console.WriteLine($"平均工资：{avgSalary}");
double sumSalary = employees.Sum(e => e.Salary);
Console.WriteLine($"工资总和：{sumSalary}");
int count = employees.Count();
Console.WriteLine($"总条数：{count}");
int minSalary2 = employees.Where(e => e.Age > 30).Min(e => e.Salary);
Console.WriteLine($"大于30岁的人群中的最低工资：{minSalary2}");

class Employee
{
    public long Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public bool Gender { get; set; }
    public int Salary { get; set; }
    public override string ToString()
    {
        return $"Id:{Id},Name:{Name},Age:{Age},Gender={Gender},Salary={Salary}";
    }
}

如果集合是int等值类型和集合，我们也可以使用没有参数的聚合函数。

int[] scores = { 61, 90, 100, 99, 18, 22, 38, 66, 80, 93, 55, 50, 89 };
int minScore = scores.Min();
Console.WriteLine($"最低成绩：{minScore}");
double avgScore1 = scores.Where(i => i > 60).Average();
Console.WriteLine($"合格成绩中的平局水平：{avgSalary}");

分组

LINQ中支持类似于SQL中的GroupBy实现分组操作。GroupBy方法用来进行分组。
对序列中的元素进行分组。

<IGrouping<TKey, TSource>> GroupBy<TSource, TKey>(this IEnumerable<TSource> source, Func<TSource, TKey> keySelector);

GroupBy方法的参数keySelector是分组条件表达式，GroupBy方法的返回值为IGrouping<TKey, TSource>类型的泛型IEnumerable。IGrouping是一个继承自IEnumerable的接口，IGrouping中唯一的成员就是Key属性，表示这一组的数据项。

List<Employee> employees = new List<Employee>();
employees.Add(new Employee { Id = 1, Name = "Tim", Age = 28, Gender = true, Salary = 10000 });
employees.Add(new Employee { Id = 2, Name = "Tim", Age = 18, Gender = false, Salary = 20000 });
employees.Add(new Employee { Id = 3, Name = "Tim", Age = 20, Gender = true, Salary = 30000 });
employees.Add(new Employee { Id = 4, Name = "Tim", Age = 22, Gender = true, Salary = 40000 });
employees.Add(new Employee { Id = 5, Name = "Tim", Age = 24, Gender = false, Salary = 50000 });
employees.Add(new Employee { Id = 6, Name = "Tim", Age = 26, Gender = true, Salary = 60000 });
employees.Add(new Employee { Id = 7, Name = "Tim", Age = 27, Gender = false, Salary = 70000 });
employees.Add(new Employee { Id = 8, Name = "Tim", Age = 19, Gender = true, Salary = 80000 });

IEnumerable<IGrouping<int, Employee>> items = employees.GroupBy(e => e.Age);
foreach (IGrouping<int, Employee> item in items)
{
    int age = item.Key;
    int count = item.Count();
    int maxSalary = item.Max(e => e.Salary);
    double avgSalary = item.Average(e => e.Salary);
    Console.WriteLine($"年龄：{item.Key},人数：{count},最高工资：{maxSalary},平均工资：{avgSalary}");
}

class Employee
{
    public long Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public bool Gender { get; set; }
    public int Salary { get; set; }
    public override string ToString()
    {
        return $"Id:{Id},Name:{Name},Age:{Age},Gender={Gender},Salary={Salary}";
    }
}

根据性别进行分组

var items = employees.GroupBy(e => e.Gender);
foreach (var item in items)
{
    bool gender = item.Key;
    int count = item.Count();
    double avgSalary = item.Average(e => e.Salary);
    int minAge = item.Min(e => e.Age);
    Console.WriteLine($"性别：{gender},人数：{count},平均值：{avgSalary:F},最下年龄：{minAge}");
}

由于分组条件表达式用的bool类型的Gender属性，intem.Key就是bool类型。

投影

可以对集合使用Select方法进行投影操作，通俗来说就是把集合中的每一项逐项转换为另外一种类型。

List<Employee> employees = new List<Employee>();
employees.Add(new Employee { Id = 1, Name = "Tim", Age = 28, Gender = true, Salary = 10000 });
employees.Add(new Employee { Id = 2, Name = "Tim", Age = 18, Gender = false, Salary = 20000 });
employees.Add(new Employee { Id = 3, Name = "Tim", Age = 20, Gender = true, Salary = 30000 });
employees.Add(new Employee { Id = 4, Name = "Tim", Age = 22, Gender = true, Salary = 40000 });
employees.Add(new Employee { Id = 5, Name = "Tim", Age = 24, Gender = false, Salary = 50000 });
employees.Add(new Employee { Id = 6, Name = "Tim", Age = 26, Gender = true, Salary = 60000 });
employees.Add(new Employee { Id = 7, Name = "Tim", Age = 27, Gender = false, Salary = 70000 });
employees.Add(new Employee { Id = 8, Name = "Tim", Age = 19, Gender = true, Salary = 80000 });

IEnumerable<int> ages = employees.Select(e => e.Age);
Console.WriteLine(String.Join(",",ages));
IEnumerable<string> names = employees.Select(e => e.Gender ? "男" : "女");
Console.WriteLine(string.Join(",",names));

class Employee
{
    public long Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public bool Gender { get; set; }
    public int Salary { get; set; }
    public override string ToString()
    {
        return $"Id:{Id},Name:{Name},Age:{Age},Gender={Gender},Salary={Salary}";
    }
}

Select方法中使用匿名类型
使用Select方法从employees的每一项提取出Name、Age属性的值，并且把Gender转换为字符串,Select方法的返回值是一个匿名类型的IEnumerable类型，因此我们必须用var声明变量类型。

var items = employees.Select(e => new {e.Name, e.Age, XingBie = e.Gender ? "男" : "女"});
foreach (var item in items)
{
    string name = item.Name;
    int age = item.Age;
    string xingbie = item.XingBie;
    Console.WriteLine($"名字={name},年龄={age},性别={xingbie}");
}

集合转换

集合操作的扩展方法的返回值大部分都是IEnumnerable<T>类型，但是有一些地方需要数组类型或者List<T>类型的变量，我们可以用ToArray和ToList方法分别把IEnumerable<T>转换为数组类型和List<T>类型。

Employee[] items1 = employees.Where(e => e.Salary > 30000).ToArray();
List<Employee> items2 = employees.Where(e => e.Salary > 3000).ToList();

LINQ扩展方法的链式调用

var items = employees.Where(e => e.Id > 2).GroupBy(e => e.Age).OrderBy(g => g.Key).Take(3)
    .Select(g => new { Age = g.Key, Count = g.Count(), AvgSalary = g.Average(e => e.Salary) });
foreach (var item in items)
{
    Console.WriteLine($"年龄：{item.Age},人数：{item.Count},平均工资：{item.AvgSalary}");
}

查询语句

var items1=employees.Where(e => e.Salary > 30000).OrderBy(e=>e.Age)
    .Select(e => new {e.Name, e.Age, Gender = e.Gender ? "男" : "女"});

使用查询语法：

var items2 = from e in employees
    where e.Salary > 3000
    orderby e.Age
    select new { e.Name, e.Age, Gender = e.Gender ? "男" : "女" };