LINQ 基本子句之二 join

Join子句据说可以实现3中连接关系。

1.内部连接——元素的连接关系必须同时满足被连接的两个数据源

2.分组连接

3.左外连接

1.最基本的，内部连接，类似于sql中inner join。

   由于student类和phone类只有一个匹配的连接，因此，只返回一个id,1.

 public class student
     {
     public   int id;
        string name;
        public student(int id, string name)
        {
            this.id = id;
            this.name = name;
        }
     }
     public class phone
     {
      public   int id;
         string number;
         public phone(int i, string number)
         {
             this.id = i;
             this.number = number;
         }

     }

    class Program
    {
        static void Main(string[] args)
        {
          List<student> stu=new List<student>();
          List<phone> ph=new List<phone>();
          student st1=new student (1,"bob");
          student st2 = new student(2, "tony");
          phone p1 = new phone(1, "2333333");
            stu.Add(st1);
            stu.Add(st2);
            ph.Add(p1);
            var result = from x in ph
                               join s in stu on x.id equals s.id
                              select x;
            foreach(var r in result)
            Console.WriteLine(r.id);
            Console.ReadLine();
        }
    }

2.分组连接 类似于full join

   注意其中ToList的用法。我还不很明白，Mark之。

     public class student
     {
       public   int id;
       public    string name;
        public student(int id, string name)
        {
            this.id = id;
            this.name = name;
        }
     }
     public class phone
     {
        public   int id;
        public string number;
         public phone(int i, string number)
         {
             this.id = i;
             this.number = number;
         }

     }

    class Program
    {
        static void Main(string[] args)
        {
          List<student> stu=new List<student>();
          List<phone> ph=new List<phone>();
          student st1=new student (1,"bob");
          student st2 = new student(2, "tony");
          phone p1 = new phone(1, "2333333");
            stu.Add(st1);
            stu.Add(st2);
            ph.Add(p1);
            var result = from s in stu
                         join p in ph on s.id equals p.id into g
                         select new
                         {
                             id = s.id,
                             name = s.name,
                             phone = g.ToList()
                         };
            foreach (var r in result)
            {
                Console.WriteLine(r.id);
                Console.WriteLine(r.name);
                if (r.phone.Count > 0)
                    Console.WriteLine(r.phone[0].number);
                else
                    Console.WriteLine("xxxx");
            }
            Console.ReadLine();
        }
    }

结果如图

3.类似Left join,往往与DefaultIfEmpty()结合使用，若第一个集合中的元素没有找到相关元素，DefaultIfEmpty()可以指定该元素的相关元素的默认元素。

   讲phone作为第一个元素

  class Program
    {
        static void Main(string[] args)
        {
          List<student> stu=new List<student>();
          List<phone> ph=new List<phone>();
          student st1=new student (1,"bob");
          student st2 = new student(2, "tony");
          phone p1 = new phone(1, "2333333");
            stu.Add(st1);
            stu.Add(st2);
            ph.Add(p1);
            var result = from p in ph
                         join s in stu on p.id equals s.id into g
                         from pc in g.DefaultIfEmpty()
                         select new
                         {
                             id = p.id,
                             num = p.number,
                             name = g.ToList()
                         };
            foreach (var r in result)
            {
                Console.WriteLine(r.id);
                Console.WriteLine(r.num);
                if (r.name.Count > 0)
                    Console.WriteLine(r.name[0].name);
                else
                    Console.WriteLine("xxxx");
            }
            Console.ReadLine();
        }

结果如图

通常，若要生成两个集合的左外部连接，可以分两步实现，

其一，使用分组连接执行内部连接。

其二，对分组连接中每个匹配元素序列调用DefaultIfEmpty().