using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static System.Console;

namespace ConsoleApp33
{
    class MartialArtsMaster
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string Menpai { get; set; }
        public string Kongfu { get; set; }
        public int Level { get; set; }

        public override string ToString()
        {
            return string.Format("Id: {0}, Name: {1}, Age: {2}, Menpai: {3}, Kongfu: {4}, Level: {5}", Id, Name, Age, Menpai, Kongfu, Level);
        }
    }
    class Kongfu
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Power { get; set; }

        public override string ToString()
        {
            return string.Format("Id: {0}, Name: {1}, Power: {2}", Id, Name, Power);
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            var masterList = new List<MartialArtsMaster>()
            {
                new MartialArtsMaster() {Id = 1, Name = "黃蓉", Age = 18, Menpai = "丐幫", Kongfu = "打狗棒法", Level = 9},
                new MartialArtsMaster() {Id = 2, Name = "洪七公", Age = 70, Menpai = "丐幫", Kongfu = "打狗棒法", Level = 10},
                new MartialArtsMaster() {Id = 3, Name = "郭靖", Age = 22, Menpai = "丐幫", Kongfu = "降龍十八掌", Level = 10},
                new MartialArtsMaster() {Id = 4, Name = "任我行", Age = 50, Menpai = "明教", Kongfu = "葵花寶典", Level = 1},
                new MartialArtsMaster() {Id = 5, Name = "東方不敗", Age = 35, Menpai = "明教", Kongfu = "葵花寶典", Level = 10},
                new MartialArtsMaster() {Id = 6, Name = "林平之", Age = 23, Menpai = "華山", Kongfu = "葵花寶典", Level = 7},
                new MartialArtsMaster() {Id = 7, Name = "岳不群", Age = 50, Menpai = "華山", Kongfu = "葵花寶典", Level = 8},
                new MartialArtsMaster() {Id = 8, Name = "令狐沖", Age = 23, Menpai = "華山", Kongfu = "獨(dú)孤九劍", Level = 10},
                new MartialArtsMaster() {Id = 9, Name = "梅超風(fēng)", Age = 23, Menpai = "桃花島", Kongfu = "九陰真經(jīng)", Level = 8},
                new MartialArtsMaster() {Id = 10, Name = "黃藥師", Age = 23, Menpai = "梅花島", Kongfu = "彈指神通", Level = 10},
                new MartialArtsMaster() {Id = 11, Name = "風(fēng)清揚(yáng)", Age = 23, Menpai = "華山", Kongfu = "獨(dú)孤九劍", Level = 10}
            };
            //初始化武學(xué)
            var kongfuList = new List<Kongfu>()
            {
                new Kongfu() {Id = 1, Name = "打狗棒法", Power = 90},
                new Kongfu() {Id = 2, Name = "降龍十八掌", Power = 95},
                new Kongfu() {Id = 3, Name = "葵花寶典", Power = 100},
                new Kongfu() {Id = 4, Name = "獨(dú)孤九劍", Power = 100},
                new Kongfu() {Id = 5, Name = "九陰真經(jīng)", Power = 100},
                new Kongfu() {Id = 6, Name = "彈指神通", Power = 100}
            };
            var res = from m in masterList
                          //from后面設(shè)置查詢的集合
                      where m.Level > 8 && m.Menpai == "丐幫"
                      //where后面跟上查詢的條件
                      select m;//表示m的結(jié)果結(jié)合返回
            foreach(var i in res)
            {
                WriteLine(i);
            }
            WriteLine("---------------");
            var res1 = from m in masterList
                      from k in kongfuList
                      where m.Kongfu == k.Name && k.Power > 90
                      select m;
            foreach (var i in res1)
            {
                WriteLine(i);
            }
            WriteLine("---------------");
            var res2 = from m in masterList
                      where m.Level > 8 && m.Menpai == "丐幫"
                      //orderby m.Age descending  // 默認(rèn)從小到大，加上descending從大到小
                      orderby m.Level, m.Age //按照多個(gè)字段進(jìn)行排序，如果字段的屬性相同，就按照第二個(gè)屬性排序
                      select m;//表示m的結(jié)果結(jié)合返回
            foreach (var i in res2)
            {
                WriteLine(i);
            }
            WriteLine("---------------");
            var res3 = from m in masterList
                          // join...in... 表示要連接的表，on后面為連接條件，等于要用equals，不能用==
                      join k in kongfuList on m.Kongfu equals k.Name
                      where k.Power > 90
                      select new { master = m, kongfu = k.Name,k.Power };
            WriteLine("聯(lián)合查詢");
            foreach (var i in res3)
            {
                WriteLine(i);
            }
            WriteLine("---------------");
            var res4 = from k in kongfuList
                      join m in masterList on k.Name equals m.Kongfu
                      into groups   //分組
                      orderby groups.Count()  // 這個(gè)可以獲得數(shù)量
                      select new { kongfu = k, count = groups.Count() };
            WriteLine(7777777);
            foreach (var i in res4)
            {
                WriteLine(i);
            }
            WriteLine("---------------");
            var res5 = from m in masterList
                      group m by m.Kongfu
                          into g
                      select new { count = g.Count(), key = g.Key };//g.Key Key表示是按照那個(gè)屬性分的組
            foreach (var i in res5)
            {
                WriteLine(i);
            }
            WriteLine("---------------");

            ReadKey();
        }
    }
}