linq to entities 笔记

1、笔记

2、数据库为 Oracle 11g

3、linq to entities 技术

4、实现效果

• join 和 group by组合使用
  
• 多列表字段值合并到一列
  
• 分组，将 “不同值列” 多行值拼接到一行

        public IEnumerable<BookingOrderDetail> GetBookingOrderDetailByNo(string orderNo)
        {
            using (var db = new Entities())
            {
                //连接分组（join——inner join，Group by）
                var query = from bo in db.BOOKING_ORDER
                            join bod in db.BOOKING_ORDER_DETAIL
                            on bo.BOOKING_ORDER_ID equals bod.BOOKING_ORDER_ID
                            where (bo.ORDER_NO.ToUpper() == orderNo.ToUpper())
                            group new { bo, bod } by new { bo.BOOKING_ORDER_ID, bo.LOCAL_COMPANY_NAME_CN, bo.SHIP_NAME_EN, bo.VOYAGE, bo.STATUS, bo.ORDER_NO, bod.CONTA_SIZETYPE_CODE } into g
                            select new
                            {
                                Booking_Order_ID = g.Key.BOOKING_ORDER_ID,
                                Local_Company_Name_CN = g.Key.LOCAL_COMPANY_NAME_CN,
                                Ship_Name_EN = g.Key.SHIP_NAME_EN,
                                Voyage = g.Key.VOYAGE,
                                Status = g.Key.STATUS,
                                Order_No = g.Key.ORDER_NO,
                                CONTA_SIZETYPE_CODE = g.Key.CONTA_SIZETYPE_CODE,
                                Count = g.Select(x => x.bod.CONTA_SIZETYPE_CODE).Count()
                            };
                //例子，表结构如下：
                //student 学生表
                //sid   sname   sage
                //1     张三      18
                //2     李四      19
                //achievement 成绩表
                //aid   ctype(科目id，外键)   sid（外键——学生id）  score(分数)
                //1     1                       1
                //2     2                       1   
                //3     3                       1
                //4     1                       2
                //5     2                       2


                //列合并，将多列的值合并到一列
                var query1 = from i in query.ToList()
                             select new
                             {
                                 Booking_Order_ID = i.Booking_Order_ID,
                                 Local_Company_Name_CN = i.Local_Company_Name_CN,
                                 Ship_Name_EN = i.Ship_Name_EN,
                                 Voyage = i.Voyage,
                                 Status = i.Status,
                                 Order_No = i.Order_No,
                                 CONTA_SIZETYPE_CODE = string.Join("×", i.CONTA_SIZETYPE_CODE, i.Count)
                             };
                //执行query块，结果
                //sid   sname   sage    ctype   ccount(参考总科目数量)
                //1     张三      18      1          1(考了一门语文)
                //1     张三      18      2          1(考了一门数学)
                //1     张三      18      3          1(考了一门英语)


                //行合并，将多行的值用“,”拼接起来
                var query2 = from i in query1.ToList()
                             group i by new { i.Booking_Order_ID, i.Local_Company_Name_CN, i.Ship_Name_EN, i.Voyage, i.Status, i.Order_No } into tempGroup
                             //select new 
                             //{
                             //    Booking_Order_ID = tempGroup.FirstOrDefault().Booking_Order_ID,
                             //    Local_Company_Name_CN = tempGroup.FirstOrDefault().Local_Company_Name_CN,
                             //    Ship_Name_EN = tempGroup.FirstOrDefault().Ship_Name_EN,
                             //    Voyage = tempGroup.FirstOrDefault().Voyage,
                             //    Status = tempGroup.FirstOrDefault().Status,
                             //    Order_No = tempGroup.FirstOrDefault().Order_No,
                             //    CONTA_SIZETYPE_CODE = string.Join(",", (tempGroup.Select(s => s.CONTA_SIZETYPE_CODE)).ToList())
                             //};
                             select new BookingOrderDetail() { //如果返回集合的话，注意不能返回匿名对象，必须如这种写法
                                 Booking_Order_ID = 0
                                 //省略
                             };
                //执行query1块，结果
                //sid   sname   sage        ctype
                //1     张三      18      1 × 1
                //1     张三      18      2 × 1
                //1     张三      18      3 × 1


                return query2;
            }
        }