Linq join right join left join

代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;

namespace TestConsole
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var classify_list = new List<Alliance_FAQ_Classify>
            {
                new Alliance_FAQ_Classify{ClassifyID =1,ClassifyName ="酒店"},
                new Alliance_FAQ_Classify{ClassifyID =2,ClassifyName ="机票"},
                new Alliance_FAQ_Classify{ClassifyID =3,ClassifyName ="团购"},
                new Alliance_FAQ_Classify{ClassifyID =5,ClassifyName ="旅游"},
                new Alliance_FAQ_Classify{ClassifyID =8,ClassifyName ="Other"}
            };

            var info_list = new List<Alliance_FAQ_Info>
            {
                new Alliance_FAQ_Info{FAQID =1,ClassifyID =1},
                new Alliance_FAQ_Info{FAQID =2,ClassifyID =1},
                new Alliance_FAQ_Info{FAQID =3,ClassifyID =1},
                new Alliance_FAQ_Info{FAQID =4,ClassifyID =1},
                new Alliance_FAQ_Info{FAQID =5,ClassifyID =1},
                new Alliance_FAQ_Info{FAQID =6,ClassifyID =1},

                new Alliance_FAQ_Info{FAQID =7,ClassifyID =2},
                new Alliance_FAQ_Info{FAQID =8,ClassifyID =2},
                new Alliance_FAQ_Info{FAQID =9,ClassifyID =2},

                new Alliance_FAQ_Info{FAQID =10,ClassifyID =3},
                new Alliance_FAQ_Info{FAQID =11,ClassifyID =3},
                new Alliance_FAQ_Info{FAQID =12,ClassifyID =3},
                new Alliance_FAQ_Info{FAQID =13,ClassifyID =3},
                new Alliance_FAQ_Info{FAQID =14,ClassifyID =3},
                new Alliance_FAQ_Info{FAQID =15,ClassifyID =3},
                new Alliance_FAQ_Info{FAQID =16,ClassifyID =3},
                new Alliance_FAQ_Info{FAQID =17,ClassifyID =3},
                new Alliance_FAQ_Info{FAQID =18,ClassifyID =3},

                new Alliance_FAQ_Info{FAQID =19,ClassifyID =5},
                new Alliance_FAQ_Info{FAQID =22,ClassifyID =5}
            };

            var info_group = from a in info_list
                             group a by a.ClassifyID into g
                             select new
                             {
                                 ClassifyID=g.Key,
                                 TotalCount=g.Count()
                             };
            foreach (var g in info_group)
            {
                Console.WriteLine("ClassifyID:" + g.ClassifyID + "     TotalCount:" + g.TotalCount);
            }
            Console.WriteLine("=================================================");
            Console.WriteLine("Left Join:");
            var left_list = from a in info_group
                       join b in classify_list
                       on a.ClassifyID equals b.ClassifyID into joinData
                       from c in joinData.DefaultIfEmpty()
                       select new
                       {
                           c.ClassifyID,
                           Count = a != null ? a.TotalCount : 0,
                           c.ClassifyName
                       };
            foreach (var l in left_list)
            {
                Console.WriteLine("ClassifyID:" + l.ClassifyID + "     Count:" + l.Count + "     ClassifyName:" + l.ClassifyName);
            }
            Console.WriteLine("=================================================");
            Console.WriteLine("Right Join:");
            var right_list = from a in classify_list
                       join b in info_group
                       on a.ClassifyID equals b.ClassifyID into joinData
                       from c in joinData.DefaultIfEmpty()
                       select new
                       {
                           a.ClassifyID,
                           Count = c != null ? c.TotalCount : 0,
                           a.ClassifyName 
                       };
            foreach (var r in right_list)
            {
                Console.WriteLine("ClassifyID:" + r.ClassifyID + "     Count:" + r.Count + "     ClassifyName:" + r.ClassifyName);
            }
            Console.ReadLine();
        }
    }

    public class Alliance_FAQ_Info
    {
        public int FAQID { get; set; }

        public int ClassifyID { get; set; }
    }

    public class Alliance_FAQ_Classify
    {
        public int ClassifyID { get; set; }

        public string ClassifyName { get; set; }
    }
}

 可以发现第一个就是满表

http://www.cnblogs.com/redmondfan/archive/2013/08/23/3276811.html

http://www.cnblogs.com/shenqiboy/p/3260105.html

 

 DataTable dtA = new DataTable();
            dtA.Columns.Add("id", typeof(int));
            dtA.Columns.Add("price", typeof(string));
            dtA.Rows.Add(1, "100");
            dtA.Rows.Add(2, "100");

            DataTable dtB = dtA.Clone();
            dtB.Rows.Add(1, "100");
            dtB.Rows.Add(3, "100");

            DataTable dtC = dtA.Clone();
            dtC.Columns.Add("price_excel");

            var leftData = from a in dtA.AsEnumerable()
                           join b in dtB.AsEnumerable()
                           on a.Field<int>("id") equals b.Field<int>("id") into g
                           from b in g.DefaultIfEmpty()
                           select new
                           {
                               id = a.Field<int>("id"),
                               price = a.Field<string>("price"),
                               price_excel = b == null ? "Null" : b.Field<string>("price")
                           };
            var rightData = from b in dtB.AsEnumerable()
                            where !dtA.AsEnumerable().Select(a => a.Field<int>("id")).Contains(b.Field<int>("id"))
                            select new
                            {
                                id = b.Field<int>("id"),
                                price = "Null",
                                price_excel = b.Field<string>("price")
                            };

            var fullJoinData = leftData.Union(rightData);
            fullJoinData.ToList().ForEach(q => dtC.Rows.Add(q.id, q.price, q.price_excel));
            for(var i=0;i<dtC.Rows.Count ;i++)
            {
                var r = dtC.Rows[i];
                Console.WriteLine("id:" + r["id"] + "     price:" + r["price"] + "     price_excel:" + r["price_excel"]);
            }

 full join

http://blog.csdn.net/q107770540/article/details/7282005