那天有个小孩跟我说LINQ（四）

1  LINQ TO SQL(代码下载)

      我们以一个酒店管理系统的数据库为例子

        表结构很简单：GuestInfo（客人信息表），Room（房间表），RoomType（房间类型表），HotelInfo（每个月盈利信息）

         整体就是，Room中有个房间类型ID的外键，客人表中有个房间的ID的外键。月盈利信息先不管

 

         准备：新建项目 linq_Ch4控制台程序，新建DB文件夹

          右击DB文件夹，添加→新建项

          

            选择LINQ to SQL类，文件名为 Hotel.dbml,添加

          

           打开服务器资源管理器，我的快捷键是Ctrl+W+L

           

            选中这四张表，往已经打开的Hotel.dbml文件中拖放，然后Ctrl+S保存

            

     1.1 查询单表

            引入命名空间，让我们可以访问dbml文件

            

            我们有个对象叫  dbml文件名+DataContext组成的一个名称的对象，通过它你就可以操作数据库了

           ①查出空房间，还可以住两个人以上的房间的信息;查出的结果先按照 最大住人数降序显示结果再按照床位数降序显示结果（锻炼基本单表查询，多条件排序）

             代码如下：

          HotelDataContext db = new HotelDataContext();
            //2013年4月6日8:04:06 茗洋芳竹
            //1. 查出空房间，还可以住两个人以上的房间的信息;查出的结果先按照 最大住人数降序显示结果，再按照床位数降序显示结果
            var query1 = from o in db.Room
                                 where o.R_Status == 0 && o.R_MaxPNum > 1
                                 orderby o.R_MaxPNum descending,o.R_BedNum descending
                                 select o;

            foreach (var item in query1)
            {
                Console.WriteLine(string.Format("房间ID：{0}\t{1}\t床位数：{2},最多可住{3}人，描述：{4}",item.R_ID,item.R_Status==0?"空闲":"有人",item.R_BedNum,item.R_MaxPNum,item.R_Discription));
            }

         

         效果图：

         

    1.2 嵌套方式查询

        ①按房间类型查询出房间的信息

        效果图：

           

 

       代码如下：

//2.按房间类型查询出房间的信息
            var query2 = from b in db.RoomType
                         select new
                         {
                             RoomTypeName = b.RT_Name,
                             RoomPrice = b.RT_Price,
                             Items = from c in db.Room
                                     where c.R_RT_ID == b.RT_ID
                                     select c
                         };

            foreach (var item in query2)
            {
                Console.WriteLine();
                Console.WriteLine("房间类型：" + item.RoomTypeName + ",价格是" + Math.Round(item.RoomPrice, 2).ToString());
                if (item.Items.Count() > 0)
                {
                    foreach (var r_info in item.Items)
                    {
                        Console.WriteLine(string.Format("{0}({1}):{2}", r_info.R_ID, r_info.R_Status == 0 ? "空闲" : "有人", r_info.R_Discription));
                    }
                }
                else
                {
                    Console.WriteLine("该类型下没有任何房间！");
                }
            }

      

类似的sql语句帮助你理解：

select a.RT_Name,(select top 1 R_ID from dbo.Room b where a.RT_ID=b.R_RT_ID) as RID from dbo.RoomType a

 

1.3 多表联合查询

      ①查出客人的信息，房间的类型信息

   var query3 = from r in db.Room
                                 join guest in db.GuestInfo on r.R_ID equals guest.G_R_ID
                                 join rt in db.RoomType on r.R_RT_ID equals rt.RT_ID
                                 select new
                                 {
                                     personID = guest.G_ID,
                                     personName = guest.G_Name,
                                     personCardId = guest.G_CardId,
                                     roomTName = rt.RT_Name,
                                     roomStatus = r.R_Status,
                                     personStatus=guest.G_Status,//结账状态
                                 };

            foreach (var info in query3)
            {
                Console.WriteLine(string.Format("{0}:{1},身份证：{2},房间类型名称：{3},结账状态：{5}，房间状态：{4}", info.personID, info.personName, info.personCardId, info.roomTName, info.roomStatus == 0 ? "空闲" : "有人", info.personStatus == 0 ? "未结账" : "已结账"));
            }

效果图：

 

1.4 group多个条件分组

  ①查出客户信息，按照房间号，结账状态分组，统计每种状态的押金数和已收入的钱财数

    为了测试真实性，我又添加了一些数据，表中数据如下：

    

     统计查询效果图：

    

    代码如下：

  //4.按照房间号，结账状态分组，房间号第一条件，状态第二条件，结算已收入的押金数和收入数信息
            var query4 = from a in db.GuestInfo
                         group a by new { a.G_R_ID, a.G_Status } into g  //按照房间号和客人结账状态分组
                         select new
                         {
                             房间号 = g.Key.G_R_ID,
                             房间结账状态 = g.Key.G_Status == 0 ? "未结账" : "已结账",
                             押金数 = g.Sum(i => i.G_Deposit),
                             收入数 = g.Sum(i => i.G_Pay),
                             统计数=g.Count()
                         };

            foreach (var item in query4)
            {
                Console.WriteLine(string.Format("房间号(统计了{4}间)：{0}\t结账状态：{1}\t押金数:{2}\t收入数:{3}", item.房间号, item.房间结账状态, item.押金数, item.收入数, item.统计数));
            }

 

说明：

     LINQ查询表达式的语法既不能接受group子句中的，也不允许一个查询中包含多个group子句

     下面两种写法是错误的：

      ①

     

    ②

      

     使用LINQ to SQL进行数据分组查询时效率会有一定的损失。因为LINQ语句最终会被解释为SQL语句执行，但是LINQ语句解释为SQL时会有一定的效率损失，可以使用视图等数据库对象构建高效的数据查询。

 

 

1.5 条件方式查询

     ①用Where

         代码如下：

//5.条件查询
           var query5_1 = db.GuestInfo.Where(p => p.G_Name == "***");
           foreach (var item in query5_1)
           {
               Console.WriteLine(item.G_Name+"在"+item.G_RentDate.ToString("yyyy年MM月dd日 hh点mm分")+"入住我旅馆");
           }

        

   

   ②Enumerable类的Where方法如下所示：

       public static IEnumerable<TSource> Where<TSource>(this IEnumerable<TSource> source,Func<TSource,bool> predicate)

       其中predicate，用于测试每个元素是否满足条件的一个函数。Where操作符接受的参数类型为Func<TSource,bool>。该类型是具有一个参数并返回布尔值的委托。

       接下*，我们*改写一下Where，自定义一个查询方法，叫ConditionQuery<TSource>

    public static IEnumerable<TSource> ConditionQuery<TSource>(IEnumerable<TSource> source, Func<TSource, bool> predicate)
        {
            return source.Where(predicate);
        }

     使用如下：

//自定义查询,替代where
            var query5_2 = ConditionQuery<GuestInfo>(db.GuestInfo,item=>item.G_Name=="***");
            Console.WriteLine("第二种方式");
            foreach (var item in query5_2)
            {
                Console.WriteLine(item.G_Name + "在" + item.G_RentDate.ToString("yyyy年MM月dd日 hh点mm分") + "入住我旅馆");
            }

   效果图：

         

   说明：

      LINQ to SQL中使用where自居筛选数据时，效率较高。基本上所有LINQ to SQL查询中含有的where自居都会被解释为SQL语句中的where子句部分。

 

1.6 类似于数据库中的Not Exists的语句

       查出一次都没有租出去的房子的信息，contains方法加上！号，效果等同于not exists

        //6.   查出一次都没有租出去的房子的信息
            var query6 = from r in db.Room
                         where !(from g in db.GuestInfo
                                 select g.G_R_ID
                                  ).Contains(r.R_ID)
                         select new
                         {
                             房间编号 = r.R_ID,
                             房间说明 = r.R_Discription,
                             床位数 = r.R_BedNum,
                             最多可住人数 = r.R_MaxPNum
                         };

            foreach (var item in query6)
            {
                Console.WriteLine(string.Format("房间编号：{0};床位数：{1};最多可住人数：{2};房间说明：{3}",item.房间编号,item.床位数,item.最多可住人数,item.房间说明));
            }

效果图如下：

    

 

1.7 分组联接 GroupJoin

       表A.GroupJoin（表B，表A外键，表B外键，处理方法）

        表A中数据全显示，表B关联上的就显示，否则为空，类似于左查询

代码如下：

           //7.分组联接 GroupJoin
            var query7 = db.Room.GroupJoin(
                                    db.GuestInfo,
                                    m => m.R_ID,
                                    n => n.G_R_ID,
                                    (m, n) => new
                                    {
                                        房间ID=m.R_ID,
                                        房间状态 = m.R_Status == 0 ? "空闲" : "有人",
                                        已经为您盈利=n.Sum(k=>k.G_Pay)
                                    }
                                 );
            foreach (var item in query7)
            {
                Console.WriteLine(string.Format("房间编号：{0}；\t{1}\t已经为您盈利{2:C}", item.房间ID, item.房间状态, item.已经为您盈利));
            }

 

效果图：

 

1.8 聚合操作系列(常用于分组后处理，对比用法可参考1.4例子)

      ①Count操作：统计次数，例子很简单不写了

      ②LongCount操作：跟Count一样，就是返回值是Int64类型的

      ③Sum操作：统计总额，累计

      ④Max操作：取一组值中的最大值

      ⑤Min操作：取一组值中的最小值

      ⑥Average操作：取一组值中的平均值

      ⑦Aggregate操作：用于对集合中的元素进行自定义的聚合计算

         例如：统计今年的总盈利额，我们操作HotelInfo表

                  效果图：

        代码如下：

   //8. Aggregate操作例子
            decimal[] amountArray = db.HotelInfo.Select(item => item.earnMoney).ToArray<decimal>();
            double amountSum=Convert.ToDouble(amountArray.Aggregate((a, b) =>a+b));
            Console.WriteLine("一共" + amountSum+"元");

        等同于：

         decimal amountSum = db.HotelInfo.Sum(item=>item.earnMoney);

 

1.9 使用视图查询

      准备工作：打开Hotel.dbml文件，打开服务器资源管理器，将视图文件夹的视图拖放到dbml文件中

V_SelectRoom只是查询Room和RoomType的一个视图，我们操作的时候把它当做一张表去处理就行了。

代码如下：

   var query9 = from vi in db.V_SelectRoom
                         join g in db.GuestInfo
                         on vi.R_ID equals g.G_R_ID
                         where g.G_Status==0
                         select new
                         {
                             房间ID = vi.R_ID,
                             客人姓名 = g.G_Name
                         };
            foreach (var item in query9)
            {
                Console.WriteLine(item.房间ID+"  "+item.客人姓名);
            }

 

效果图：

主要说明的意思是：

使用视图筛选数据，可以极大的提高LINQ to SQL执行查询效率。使用LINQ to SQL查询多表数据时，会带*一定的效率确实，所以先把复杂数据筛选编译为视图，使用LINQ to SQL直接查询视图数据可显著提高数据查询效率。

 

 

 

2.1 使用存储过程查询

  准备工作：打开Hotel.dbml文件，打开服务器资源管理器，将存储过程文件夹下的sp_select_roomByRT拖放到dbml文件中，该存储过程是根据房间类型ID获得房间信息

拖放后，在dbml文件右侧

ALTER proc [dbo].[sp_select_roomByRT]
@RT int
as
select R_BedNum,R_ID,R_MaxPNum,R_Status=case when R_Status=1 then '有人' else '空闲' end,R_Discription,R_Count from Room where R_RT_ID=@RT

使用如下，也非常简单：

    //10.使用存储过程查询
            var query10 = db.sp_select_roomByRT(1);
            foreach (var item in query10)
            {
                Console.WriteLine(string.Format("房间编号：{0},房间状态：{1},房间描述：{2}", item.R_ID,item.R_Status,item.R_Discription));
            }

效果图：

主要说明的意思是：

进行复杂的数据统计、查询、多表联合查询时，可以使用存储过程封装复杂查询*简化LINQ to SQL类的操作，从而提高数据操作效率。当使用DataContext对象的存储过程生成的方法查询数据时，所有数据都会被存储至ISingleResult<T>泛型集合中。

 

 

2.2 使用存储过程操作

       准备工作：打开Hotel.dbml文件，打开服务器资源管理器，将存储过程文件夹下的sp_Insert_Room拖放到dbml文件中，该存储过程是创建一个新房间

拖放后，在dbml文件右侧

ALTER proc sp_Insert_Room
@R_RT_ID int,
@R_Discription varchar(50),
@R_bedNum int,
@R_MaxPNum int
as
begin
insert Room(R_RT_ID,R_Discription,R_BedNum,R_MaxPNum) values(@R_RT_ID,@R_Discription,@R_bedNum,@R_MaxPNum)
end

使用如下，也非常简单：

db.sp_Insert_Room(1, "这是一间于2013年4月7日0:33:43添加进去的新房间，床有9个，最多可住15个人", 9, 15);
           Console.WriteLine("添加成功！");

效果图：

 

接下*我们将sp_Insert_RoomReturnInsertID存储过程也拖放到dbml文件中去

存储过程代码：

ALTER proc [dbo].[sp_Insert_RoomReturnInsertID]
@R_RT_ID int,
@R_Discription varchar(50),
@R_bedNum int,
@R_MaxPNum int
as
begin
insert Room(R_RT_ID,R_Discription,R_BedNum,R_MaxPNum) values(@R_RT_ID,@R_Discription,@R_bedNum,@R_MaxPNum)
select @@identity;
end

代码如下：

    var queryId= db.sp_Insert_RoomReturnInsertID(1, "这是一间于2013年4月7日0:33:43添加进去的新房间，床有9个，最多可住15个人", 9, 15);
          foreach (var item in queryId)
          {
              Console.WriteLine("添加成功！插入成功后的主键ID为"+item.Column1);
          }

效果图：

同理关于delete，update相关的存储过程操作，我就不写了。