那天有个小孩跟我说LINQ(四)

时间:2013年04月07日 | 作者 : aaronyang | 分类 : 那天有个小孩跟我说LINQ | 浏览: 3446次 | 评论 0

1  LINQ TO SQL(代码下载)

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

        表结构很简单:GuestInfo(客人信息表),Room(房间表),RoomType(房间类型表),HotelInfo(每个月盈利信息)

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

 

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

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

image.png         


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

         image.png

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

           image.png

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

           image.png

     1.1 查询单表

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

           image.png

            我们有个对象叫  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));
  }

         效果图:

       image.png

    1.2 嵌套方式查询

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

        效果图:

          image.png

 

       代码如下:

      //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 ? "未结账" : "已结账"));
  }

效果图

image.png

 

1.4 group多个条件分组

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

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

   image.png

     统计查询效果图:

   image.png

    代码如下:

//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子句

     下面两种写法是错误的:

      image.png

     使用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分")+"入住我旅馆");
            }

image.png

   

   ②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分") + "入住我旅馆");
  }


   效果图:

     image.png

   说明:

      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.房间说明));
  }

效果图如下:

 image.png

 

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.已经为您盈利));
     }

效果图:

image.png

 

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

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

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

      ③Sum操作:统计总额,累计

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

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

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

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

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

            image.png

        代码如下:

  //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文件中

image.png

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.客人姓名);
        }

效果图:

image.png

主要说明的意思是:

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

 

 

 

2.1 使用存储过程查询

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

拖放后,在dbml文件右侧

image.png

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));
       }


效果图:

image.png

主要说明的意思是:

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

 

 

2.2 使用存储过程操作

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

拖放后,在dbml文件右侧

image.png

  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("添加成功!");


效果图:

image.png

 

接下来我们将

image.png

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);
}


效果图:

image.png

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

 

 


由于该数据库不适合某些知识点的讲解,考虑到内容不能太多,否则学习很累,还有我明天还要上班,不早了。所以特在此分篇,将还剩的一些关于LINQ to SQL知识放在下篇博客讲解。


推荐您阅读更多有关于“LINQ,”的文章

猜你喜欢

额 本文暂时没人评论 来添加一个吧

发表评论

必填

选填

选填

必填

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

  查看权限

合肥科大智能常年招聘C# .NET CORE,vue前端,JAVA开发,有想换工作的私聊我AY唯一QQ:875556003和AY交流

抖音号:wpfui 工作是wpf,不接活,目前主要折腾maui

AYUI8社区版Github地址:前往获取

杨洋(AaronYang简称AY,安徽六安人)目前是个人,还没公司AY唯一QQ:875556003和AY交流

高中学历,2010年开始web开发,2015年1月17日开始,兴趣学习研究WPF

声明:AYUI7个人与商用免费,源码可购买。部分DEMO不免费.AY主要靠卖技术服务挣钱

不是从我处购买的ayui7源码,我不提供任何技术服务,如果你举报从哪里买的,我可以帮你转正为我的客户,并送demo

查看捐赠

AYUI7.X MVC教程 更新如下:

第一课 第二课 程序加密教程

vs2015 企业版密钥HM6NR-QXX7C-DFW2Y-8B82K-WTYJV

vs2017 企业版密钥NJVYC-BMHX2-G77MM-4XJMR-6Q8QF

标签列表