linq学习之join

using factory;

namespace MyWebSiteTest
{
    public partial class linqtest : System.Web.UI.Page
    {
        static List<Customer> customers;
        static List<Product> products;
        static List<Order> orders;
        public static void CreateEntities()
        {
            customers = new List<Customer>()
            {
                new Customer(){ CustomerId = 1, Name = "CA", Age=13},
                new Customer(){ CustomerId = 2, Name = "CB", Age=13},
                new Customer(){ CustomerId = 3, Name = "CC", Age=13},
                new Customer(){ CustomerId = 4, Name = "CD", Age=13}
            };
            products = new List<Product>()
            {
                new Product(){ ProductId = 1, Name = "PA", Origin="P1" },
                new Product(){ ProductId = 2, Name = "PB", Origin="P2" },
                new Product(){ ProductId = 3, Name = "PC", Origin="P1" },
                new Product(){ ProductId = 4, Name = "PD", Origin="P3" }
            };
            orders = new List<Order>()
            {
                new Order(){ OrderId = 1 , CustomerId =1, 
                    Products = new List<Product>{ 
                        new Product(){ ProductId = 2, Name = "PB", Origin="P2" },
                        new Product(){ ProductId = 3, Name = "PC", Origin="P1" }
                    }},
                new Order(){ OrderId = 2 , CustomerId =1, 
                    Products = new List<Product>{ 
                        new Product(){ ProductId = 3, Name = "PC", Origin="P1" },
                        new Product(){ ProductId = 4, Name = "PD", Origin="P3" }
                    }},
                new Order(){ OrderId = 3 , CustomerId =3, 
                    Products = new List<Product>{ 
                        new Product(){ ProductId = 4, Name = "PD", Origin="P3" }
                    }},
                new Order(){ OrderId = 4 , CustomerId =2, 
                    Products = new List<Product>{ 
                        new Product(){ ProductId = 1, Name = "PA", Origin="P1" },
                        new Product(){ ProductId = 4, Name = "PD", Origin="P3" }
                    }}
            };
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            System.Text.StringBuilder outhtml = new System.Text.StringBuilder();
            CreateEntities();

            /*常见的内连接:
             * (1).连接条件：c.CustomerId equals o.CustomerId 只能使用 equals 不能用 =,==,等于 等表示。
             * (2).条件顺序：c.CustomerId equals o.CustomerId ,range variable: c 和c之前的顺序不能颠倒。
             */
            var join_query = from c in customers
                             join o in orders on c.CustomerId equals o.CustomerId
                             where o.OrderId == 2
                             select c;
            join_query.ToList().ForEach(x => Response.Write(string.Format("Id:{0}, Name:{1}", x.CustomerId, x.Name) + " "));
            Response.Write("<hr /><br />");

            /*简单的分组*/
            var group_query = from c in customers
                              join o in orders on c.CustomerId equals o.CustomerId into os
                              select new { c, os };
            group_query.ToList().ForEach(
                x =>
                {
                    outhtml.AppendLine(string.Format("<strong>Id:</strong>{0}, <strong>Name:</strong>{1}", x.c.CustomerId, x.c.Name) + "<br />");
                    x.os.ToList().ForEach(ch => { outhtml.AppendLine(string.Format(" --Order Id:{0}", ch.OrderId) + "<br />"); });
                    //可以再次筛选或其它处理，如下：
                    //(from tt in x.os select tt).ToList().ForEach(ch => { outhtml.AppendLine(string.Format(" --Order Id:{0}", ch.OrderId) + "<br />"); });
                }
            );
            Response.Write(outhtml);
            Response.Write("<br />");
            
            /*
             * Left Join 我们在SQL里经常用到，让我们来看看LINQ里怎么实现它：
             */
            var lftjon_quer = from c in customers
                              join o in orders on c.CustomerId equals o.CustomerId into os
                              from newtag in os.DefaultIfEmpty(
                                new Order { OrderId = 0, CustomerId = 0, Products = new List<Product>() }
                              )
                              select new { c, newtag };
            lftjon_quer.ToList().ForEach(x => Response.Write(string.Format("Customer Id:{0}, Name:{1}--Order Id:{0}<br />", x.c.CustomerId, x.newtag.OrderId)));
            Response.Write("<br />");
        }
    }
}

namespace factory
{
    class Customer
    {
        public int CustomerId { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }
    class Product
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public string Origin { get; set; }
    }
    class Order
    {
        public int OrderId { get; set; }
        public int CustomerId { get; set; }
        public List<Product> Products { get; set; }
    }
}