C# 7.0 LINQ

LINQ

LINQ(Language Integrated Query)使用C#语言可以轻松的在不同数据源之间使用相同的语法进行查询。
如果熟悉使用MSS数据库的话，就会感觉LINQ和SQL一样简单实用。

准备数据

我准备了一组数据，用于测试LINQ.
定义一个Person类，定义了几个简单的属性信息，name/age/gender

 public class Person
    {
        private readonly string _personName;
        private readonly int _age;
        private readonly Gender _gender;
        public string PersonName
        {
            get => _personName;
        }

        public int Age
        {
            get => _age;
        }

        public Gender Gender
        {
            get => _gender;
        }

        public Person(string personName, int age, Gender gender)
        {
            _personName = personName;
            _age = age;
            _gender = gender;
        }
    }

    public enum Gender
    {
        男,
        女
    }

初始数据

初始一堆数据，这些数据也可以从数据库获取，我这里通过简单增加几个数据来进行模拟。

 public static List<Person> GetPersons()
        {
            List<Person> persons=new List<Person>();

            persons.Add(new Person("张三",20,Gender.男));
            persons.Add(new Person("张四", 22, Gender.女));
            persons.Add(new Person("张五", 23, Gender.女));
            persons.Add(new Person("张六", 24, Gender.女));
            persons.Add(new Person("张七", 49, Gender.男));
            persons.Add(new Person("张八", 12, Gender.男));
            persons.Add(new Person("张九", 3, Gender.男));
            persons.Add(new Person("张十", 10, Gender.女));
            persons.Add(new Person("李四", 32, Gender.男));
            persons.Add(new Person("李五", 190, Gender.女));
            persons.Add(new Person("李六", 28, Gender.男));
            persons.Add(new Person("力气", 57, Gender.女));
            persons.Add(new Person("李八", 89, Gender.男));
            persons.Add(new Person("李九", 21, Gender.女));
            persons.Add(new Person("力士", 40, Gender.男));
            persons.Add(new Person("历史以", 70, Gender.男));
            return persons;

        }

查询

比如说，要查询年龄大于30的女性有哪些，可以这样写

  var person = GetPersons();
            var p1 = from c in person
                where c.Gender == Gender.女 && c.Age > 30
                orderby c.PersonName descending
                select new
                {
                    c.PersonName,
                    c.Gender
                };

再查那些年龄大于50的人，按照年龄降序排序

 var p1 = from c in person
                where c.Age > 50
                orderby c.Age descending
                select new
                {
                    c.PersonName,
                    c.Gender
                };

对于返回的值，可以自己组织匿名类
如下，可以对某些返回列做一些特殊处理。

select new
                {
                    c.PersonName,
                    c.Gender
                };

Group

查看30岁以上的男女更有多少人

 var p1 = from c in person
                where c.Age > 30
                group c by c.Gender
                into g
                where g.Count() > 0
                select new
                {
                    gender = g.Key,
                    Count = g.Count()
                };
            ;

            foreach (var p in p1)
            {
                Console.WriteLine($"{p.Count}{p.gender}");
            }

使用内部变量

let count=g.Count()

var p1 = from c in person
                where c.Age > 30
                group c by c.Gender
                into g
                let count=g.Count()
                where count > 0
                select new
                {
                    gender = g.Key,
                    Count = count
                };
            ;

也可以使用lambda表达式来写
结果也是和上面一样。这里也是用了内置变量

  var p2 = person.Where(r => r.Age > 30)
                .GroupBy(r => r.Gender)
                .Select(g => new {Group = g, Count = g.Count()})
                .OrderByDescending(g => g.Count)
                .ThenBy(g => g.Group.Key)
                .Where(g => g.Count >= 1)
                .Select(r => new
                {
                    gender = r.Group.Key,
                    count = r.Count
                });

组织返回值

不光返回人数，也要返回人名

 var p1 = from c in person
                group c by c.Gender
                into g
                let count = g.Count()
                orderby count descending, g.Key
                where count >= 1
                select new
                {
                    Gender = g.Key,
                    count = count,
                    persons = from r1 in g
                        orderby r1.PersonName
                        select r1.PersonName
                };
            foreach (var p in p1)
            {
                Console.WriteLine($"{p.count}{p.Gender}");
                foreach (var pp1 in p.persons)
                {
                    Console.Write(pp1+" ");
                }
                Console.WriteLine();
            }

join

再创建一个family类，用于关联person类

public class Family
    {
        public string Brother { get; set; }
        public string Sister { get; set; }
    }

 public static List<Family> GetFamilies()
        {
            List<Family> families=new List<Family>();
            families.Add(new Family {Brother = "张三",Sister = "田一"});
            families.Add(new Family { Brother = "张四", Sister = "田二" });
            families.Add(new Family { Brother = "张三", Sister = "田三" });
            families.Add(new Family { Brother = "张五", Sister = "田四" });
            families.Add(new Family { Brother = "张三", Sister = "田刘" });
            return families;
        }

获取有妹妹的哥哥名称

var person = GetPersons();


            var family = GetFamilies();

            var p2 = (from p in person
                    join f in family on p.PersonName equals f.Brother
                    select new
                    {
                        p.PersonName,
                        f.Sister
                    }
                ).Take(10);
                

还有复杂的join类型可以查看源码实现。