连接查询，三表查询，let子句

 

           //Linq语句
            NorthwindEntities db = new NorthwindEntities();

            //多表查询   一对多：实体框架集生成的对象具有主外键导航功能
            //使用join连接和等号连接效果一样
            var query = from q in db.Customers
                        from qq in q.Orders
                        where q.CustomerID == qq.CustomerID
                        select new { q.CustomerID,qq.OrderID};

            var query1=from q in db.Customers
                       join qq in db.Orders
                       on q.CustomerID equals qq.CustomerID
                       select new { q.CustomerID, qq.OrderID };

            //连接查询,多表之间，使用连接查询更合适，包括多对多关系的表
            //注意：此处有个分组功能
            var query2 = from q in db.Customers
                         join qq in db.Orders
                         on q.CustomerID equals qq.CustomerID
                         into orderCount
                         select new {q.CustomerID,count=orderCount.Count()>5 };

            //注意三表连接查询，中间临时变量变得不可见
            var query3 = from q in db.Customers
                         join qq in db.Orders
                         on q.CustomerID equals qq.CustomerID
                         into tmporder
                         join e in db.Employees
                         on q.City equals e.City
                         into tmpemp
                         select new { q.CompanyName, tmporderCount = tmporder.Count(), tmpempCount = tmpemp.Count() };
         
            //let字句,其后面还可以接from子句
            var query4 = from q in db.Customers
                         join qq in db.Orders
                         on q.CustomerID equals qq.CustomerID
                         into qqq
                         let qqqq = q.ContactName + "__yoyo"
                         from qq in qqq
                         select new {q.ContactName,qq.OrderID,qqqq };

            foreach (var tmp in query4)
            {
                Console.WriteLine(tmp.OrderID + "\t" + tmp.ProductID + "\t" + tmp.UnitPrice);
            }
                Console.ReadKey();
        }

        static string SetString(string kk)
        {
            return kk + "__yoyo";
        }