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

namespace ConsoleApplication1
{
    class Person
    {
        public int CityID { set; get; }
        public string Name { set; get; }
    }

    class City
    {
        public int ID { set; get; }
        public string CityName { set; get; }
    }


    public class Program
    {
        public static void Main(string[] args)
        {
            Person[] persons = new Person[]
            {
                new Person {CityID = 1, Name = "ABC"},
                new Person {CityID = 1, Name = "EFG"},
                new Person {CityID = 2, Name = "HIJ"},
                new Person {CityID = 3, Name = "KLM"},
                new Person {CityID = 3, Name = "NOP"},
                new Person {CityID = 4, Name = "QRS"},
                new Person {CityID = 5, Name = "TUV"}
            };
            City[] cities = new City[]
            {
                new City {ID = 1, CityName = "Guangzhou"},
                new City {ID = 2, CityName = "Shenzhen"},
                new City {ID = 3, CityName = "Beijing"},
                new City {ID = 4, CityName = "Shanghai"}
            };

            //例子1：内联
            var result0 = from p in persons
                join c in cities
                    on p.CityID equals c.ID
                select new {PersonName = p.Name, Citys = c.CityName};
            Console.Write("-----inner join linq--------");
            Console.WriteLine();
            foreach (var item in result0)
            {
                Console.Write(item.PersonName);
                Console.Write(":");
                Console.Write(item.Citys);
                Console.WriteLine();
            }

            Console.WriteLine();

            //例子2：左联 linq ，这里使用了into，如果cities在persons没有匹配的项，则返回Citys是null,返回的Citys是list
            var result01 = from p in persons
                  join c in cities on p.CityID equals c.ID into cs
                  select new { PersonName = p.Name, Citys = cs };
            Console.Write("------left join linq  list-------");
            Console.WriteLine();
            foreach (var item in result01)
            {
                Console.Write(item.PersonName);
                Console.Write(":");
                foreach (var city in item.Citys)
                {
                    Console.Write(city.CityName);
                }
                Console.WriteLine();
            }

            //例子3：左联 linq,这里使用了into，并且多了from t2 in cs.DefaultIfEmpty()，然后在返回的时候Citys进行了为null判断，不然result是没有内容的
            var result = from p in persons
                join c in cities
                    on p.CityID equals c.ID into cs
                from t2 in cs.DefaultIfEmpty()
                select new {PersonName = p.Name, Citys = t2 == null ? "" : t2.CityName};
            Console.Write("------left join linq-------");
            Console.WriteLine();
            foreach (var item in result)
            {
                Console.Write(item.PersonName);
                Console.Write(":");
                Console.Write(item.Citys);
                Console.WriteLine();
            }

            Console.WriteLine();


            //例子4（和例子2是同样的效果）：左连 lamda 返回的Citys是list
            var result2 = persons.GroupJoin(cities, p => p.CityID, c => c.ID,
                (p, cs) => new {PersonName = p.Name, Citys = cs});
            Console.Write("------left join lamda GroupJoin list-------");
            Console.WriteLine();
            foreach (var item in result2)
            {
                Console.Write(item.PersonName);
                Console.Write(":");
                foreach (var city in item.Citys)
                {
                    Console.Write(city.CityName);
                }
                Console.WriteLine();
            }

            Console.WriteLine();

            //例子5（和例子3是同样的效果）：左连 lamda
            var result3 = persons.GroupJoin(cities, p => p.CityID, c => c.ID, (p, cs) => new {p, cs})
                .SelectMany(p2 => p2.cs.DefaultIfEmpty(),
                    (p2, s2) => new {PersonName = p2.p.Name, Citys = s2 == null ? "" : s2.CityName});
            Console.Write("------left join lamda GroupJoin DefaultIfEmpty--2-----");
            Console.WriteLine();
            foreach (var item in result3)
            {
                Console.Write(item.PersonName);
                Console.Write(":");
                Console.Write(item.Citys);
                Console.WriteLine();
            }

            Console.WriteLine();

            Console.ReadKey();
        }
    }
}