C# Linq的简单运用

　　简单地记录一下Linq的常见用法。下面用几个简单例子来展示。

准备测试数据

public class User
{
    public int ID { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public int Money { get; set; }
    public int RoleID { get; set; }
    public Role Role { get; set; }
}

public class Role
{
    public int ID { get; set; }
    public string Name { get; set; }
}

List<User> list = new List<User>();
list.Add(new() { ID = 1, Name = "张三", Age = 15, Money = 100, RoleID = 1 });
list.Add(new() { ID = 2, Name = "李四", Age = 16, Money = 200, RoleID = 1 });
list.Add(new() { ID = 3, Name = "王五", Age = 17, Money = 400, RoleID = 2 });
list.Add(new() { ID = 4, Name = "宋六", Age = 17, Money = 250, RoleID = 2 });
list.Add(new() { ID = 5, Name = "龙七", Age = 17, Money = 250, RoleID = 2 });

List<Role> roleList = new List<Role>();
roleList.Add(new() { ID = 1, Name = "普通用户" });
roleList.Add(new() { ID = 2, Name = "超级用户" });

一般查询

//类sql
var ddd = from a in list
            select a;

//方法链lambda
var ddd2 = list;

//sql
string sql = "select * from user";

带where查询

//类sql
var ddd = from a in list
            where a.Age > 15
            select a;

//方法链lambda
var ddd2 = list.Where(x => x.Age > 15);

//sql
string sql = "select * from user where age>15";

简单的函数计算（max、min、count、sum）

//类sql
////获取最大的年龄
//var ddd = (from a in list
//           select a).Max(x => x.Age);
////获取最小的年龄
//var ddd = (from a in list
//           select a).Min(x => x.Age);
////获取总数
//var ddd = (from a in list
//           select a).Count();
//获取总和
var ddd = (from a in list
            select a).Sum(x => x.Age);

//方法链lambda
//var ddd2 = list.Max(x=>x.Age);
//var ddd2 = list.Min(x => x.Age);
//var ddd2 = list.Count();
var ddd2 = list.Sum(x => x.Age);

//sql
//string sql = "select max(age) from user";
//string sql = "select min(age) from user";
//string sql = "select count(1) from user";
string sql = "select sum(age) from user";

排序

//类sql
////正序
//var ddd = from a in list
//          orderby a.Age ascending
//          select a;
//倒序
var ddd = from a in list
            orderby a.Age descending
            select a;

//方法链lambda
////正序
//var ddd2 = list.OrderBy(x => x.Age);
//倒序
var ddd2 = list.OrderByDescending(x => x.Age);

//sql
////正序
//string sql = "select * from user order by age";
//倒序
string sql = "select * from user order by age desc";

top 1

//类sql
var ddd = (from a in list
            select a).FirstOrDefault();

//方法链lambda
var ddd2 = list.FirstOrDefault();

//sql
string sql = "select top 1 from user";

跳过前面多少条数据，取剩下的数据

//类sql
var ddd = (from a in list
            orderby a.Age descending
            select a).Skip(1);//跳过第一条

//方法链lambda
var ddd2 = list.OrderByDescending(x => x.Age).Skip(1);//跳过第一条

//sql
string sql = "select * from (select ROW_NUMBER() over(order by age desc) as rowNum,* from user) t where rowNum>1";

分页查询

//类sql
var ddd = (from a in list
            orderby a.Age descending
            select a).Skip(1).Take(3);//跳过第一条，取两条，即取第二条到第四条

//方法链lambda
var ddd2 = list.OrderByDescending(x => x.Age).Skip(1).Take(3);//跳过第一条，取两条，即取第二条到第四条

//sql
string sql = "select * from (select ROW_NUMBER() over(order by age desc) as rowNum,* from user) t where rowNum>1 and rowNum<=4";

包含，相当于like

//类sql
var ddd = from a in list
            where a.Name.Contains("李")
            select a;

//方法链lambda
var ddd2 = list.Where(x=>x.Name.Contains("李"));

//sql
string sql = "select * from user where name like '%李%'";

分组group by

//类sql
////写法一
//var ddd = from a in list
//          group a by a.Age into n
//          select n;
//foreach (var t in ddd)
//{
//    Console.WriteLine("min：" + t.Key + "--" + t.Min(p => p.Money));
//    Console.WriteLine("max：" + t.Key + "--" + t.max(p => p.Money));
//}
//写法二
var ddd = from a in list
            group a by a.Age into n
            select new
            {
                n.Key,
                minMoney = n.Min(x => x.Money),
                maxMoney = n.Max(x => x.Money)
            };
foreach (var t in ddd)
{
    Console.WriteLine("min：" + t.Key + "--" + t.minMoney);
    Console.WriteLine("max：" + t.Key + "--" + t.maxMoney);
}

//方法链lambda
var ddd2 = list.GroupBy(x => x.Age);
foreach (var t in ddd2)
{
    Console.WriteLine(t.Key + "--" + t.Min(p => p.Money));
}

//sql
string sql = "select age,min(money),max(money),sum(money) from user group by age";


///////////////////多字段

//类sql
var ddd = from a in list
            group a by new { a.Age,a.Money} into n
            select new
            {
                n.Key.Age,
                n.Key.Money,
                count = n.Count()
            };

//方法链lambda
var ddd2 = list.GroupBy(x => new { x.Age, x.Money }).Select(x => new
{
    x.Key.Age,
    x.Key.Money,
    count = x.Count()
});

//sql
string sql = "select age,money from user group by age,money";

联表查询

//类sql
var ddd = from a in list
            join b in roleList on a.RoleID equals b.ID
            select new User
            {
                ID = a.ID,
                Name = a.Name,
                Age = a.Age,
                Money = a.Money,
                RoleID = a.RoleID,
                Role = b
            };

//方法链lambda
var ddd2 = list.Join(roleList,//主表
    a => a.RoleID, //主表join值
    b => b.ID, //次表join值
    (a, b) => new User {//主表次表数据整合
        ID = a.ID,
        Name = a.Name,
        Age = a.Age,
        Money = a.Money,
        RoleID = a.RoleID,
        Role = b
    });

//sql
string sql = "select * from user a left join roleList on a.roleID = b.id";

sql中的in

//类sql
var ddd = from a in list
            where (new int[] { 15, 16 }).Contains(a.Age)
            select a;

//方法链lambda
var ddd2 = list.Where(x=>(new int[] { 15, 16 }).Contains(x.Age));

//sql
string sql = "select * from user where age in (15,16)";

 

总结

掌握上面这几个基础的linq使用，基本就能解决大部分查询问题。