第十节：Linq用法大全(一)

 从该节开始，连着三节都讲介绍Linq的各种用法，分类如下图，主要围绕下图进行讲解：

参考文档：

　　101个例子：https://github.com/lupino3/101-linq-samples-core

　　官方API：https://docs.microsoft.com/zh-cn/dotnet/api/system.linq.queryable.cast?view=netcore-3.1

一. where用法

1.作用：实现过滤查询。

2. 案例：

(1). 筛选2020 年1月22日或之后雇用的雇员：

var q = from e in db.Employees
        where e.HireDate >= new DateTime(2020, 1, 22)
        select e;

二. First/Last/Single/ElementAt

通过一张表格对比它们用法： 

 　　总结：以First和FirstOrDefault为例，First当没有元素符合条件，则抛异常；而FirstOrDefault没有元素符合条件，则返回默认值，如：引用类型的默认值为null，int的默认值为0等等。当确信序列中一定有满足条件的元素时，使用First方法，取到元素后，无需再判断是否为null。Last和LastOrDefault、Single和SingleOrDefault均同理。

1. First/FirstOrDefault

(1). 作用：返回集合中符合条件的第一个元素，其实质就是在SQL语句中加TOP (1)

(2). 案例：

A. 获取顾客表中的第一个顾客。

var c = db.Customers.First();

B. 获取运费大于 10.00 的第一个订单。

Order ord = db.Orders.First(o => o.Freight > 10.00M);
或
Order ord = db.Orders.Where(o => o.Freight > 10.00M).First();

2. Last/LastOrDefault

(1). 作用：

　　返回集合中符合条件的最后一个元素

3.Single/SingleOrDefault

(1). Single: 返回序列中的唯一记录，如果没有或返回多条记录，则引发异常。

(2). SingleOrDefault: 回序列中的唯一记录；如果该序列为空，则返回默认值；如果该序列包含多个元素，则引发异常。

4. ElementAt/ElementAtOrDefault

(1). ElementAt：返回指定索引的元素，如果索引超过集合长度，则抛出异常。

(2). ElemnetAtOrDefault：返回指定索引的元素，如果索引超过集合长度，则返回元素的默认值，不抛出异常。

PS：索引从0开始。

                List<Product> pList = new List<Product>()
                {
                  new Product(){pId="01",pName="ypf01"},
                  new Product(){pId="02",pName="ypf02"},
                  new Product(){pId="03",pName="ypf03"}
                 };
                //获取索引为1的信息，这里为：pId="02",pName="ypf02"
                var d1 = pList.ElementAt(1);
                //超出索引报错
                try
                {
                    var d2 = pList.ElementAt(3);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                //超出索引，不报错，返回默认值，这里为null
                var d3 = pList.ElementAtOrDefault(3);

最后总结：

    1、当集合中只有一个元素时，可以使用Single。

    2、当集合中不包含任何元素但需要返回默认值时，可以使用SingleOrDefault。

    3、当集合中包含多个元素并想抛出异常时，可以使用Single或SingleOrDefault。

    4、无论集合中是否有元素，我们都想要返回一个记录时，可以使用First或FirstOrDefault。

    5、当集合中不包含任何元素但需要返回默认值时，可以使用FirstOrDefault。

三. Select用法

1. 说明

　　用于查询。包括：简单用法、匿名类型(2种)、指定类型、嵌套形式(2种)、调用本地方法 几种用法。

2. 简单用法

(1). 获取所有顾客的联系人姓名

var q = from c in db.Customers
        select c.ContactName;

3. 匿名类型

(1). 获取顾客的姓名和电话。

var q = from e in db.Customers
        select new
        {
          e.Name,
          e.Phone
        };  

指定名称的写法：

var q = from e in db.Customers
        select new
        {
           myName= e.Name,
           myPhone = e.Phone
        };   

(2). 获取产品ID和对应价格的一半。（可以对原有值进行一系列运算）

var q =
    from p in db.Products
    select new
    {
        p.ProductID,
        HalfPrice = p.UnitPrice / 2
    };

4. 指定类型

获取顾客的姓名

var q = from e in db.Customers
        select new myName
        {
           firstName= e.firstName,
           lastName = e.lastName
        };  
PS:其中myName类中包括firstName和lastName两个属性.

5. 嵌套类型

(1). 匿名对象中的属性也是个匿名对象. 

var q =
    from c in db.Customers
    select new {
        c.CustomerID,
        CompanyInfo = new {c.CompanyName, c.City, c.Country},
        ContactInfo = new {c.ContactName, c.ContactTitle}
    };

(2). 匿名对象中的属性包含一个集合.

var q =
    from o in db.Orders
    select new {
        o.OrderID,
        DiscountedProducts =
            from od in o.OrderDetails
            where od.Discount > 0.0
            select od,
        FreeShippingDiscount = o.Freight
    };

6. 调用本地方法

 获取电话等一系列信息,并把电话转换成国际格式.

var q = from c in db.Customers
         where c.Country == "UK" || c.Country == "USA"
         select new
         {
             c.CustomerID,
             c.CompanyName,
             Phone = c.Phone,
             InternationalPhone = 
             PhoneNumberConverter(c.Country, c.Phone)
         };

 转换电话的方法.

public string PhoneNumberConverter(string Country, string Phone)
{
    Phone = Phone.Replace(" ", "").Replace(")", ")-");
    switch (Country)
    {
        case "USA":
            return "1-" + Phone;
        case "UK":
            return "44-" + Phone;
        default:
            return Phone;
    }
}

四. Distinct/Count/Sum/Min/Max/Average

 1. Distinct去重

例: 查询顾客覆盖的国家.

var q= (from c in db.Customers
        select c.city)
        .Distinct();
等价于SQL:SELECT DISTINCT [City] FROM [Customers]

2. Count求个数

　　对应的SQL语句: select count(*) from xxx

(1). 简单形式

例:求顾客的数量

var count= db.Customers.Count();

(2).带条件形式

例:求年龄大约20的顾客数量

var count=db.Customers.Count(c=>c.age>20);
等价于
var count=db.Customers.Where.(c=>c.age>20).Count();

PS:

　　LongCount说明：返回集合中的元素个数，返回LONG类型；不延迟。对于元素个数较多的集合可视情况可以选用LongCount来统计元素个数，它返回long类型，比较精确。生成SQL语句为：SELECT COUNT_BIG(*) FROM .

3. Sum求和

　　对应的SQL语句: select sum(*) from xxx

(1). 简单形式

 例如：求所有订单的总运费。

var q=db.Product.Select(o=>o.Freight).Sum();

(2).映射形式

 例如：求所有订单的总运费。

var q=db.Product.Sum(o=>o.Freight);

(3).元素

例如: 求每个城市中订单的销售总额.

var q=from o in db.Orders
      group o by o.city into g
      select new {
            CityName=g.key,
            TotalMoney=g.Sum(p=>p.unitPrice);
      };

4. Min求最小值

　　对应的SQL语句: select min(*) from xxx

(1). 简单形式

 例如：查找产品的最低单价。

var q=db.Products.Select(p=>p.unitPrice).Min();

(2).映射形式

 例如：查找产品的最低单价。

var q=db.Products.Min(p=>p.unitPrice);

(3).元素

例如：查找每个类别中单价最低的产品的相关信息。

var productDetails=
      from p in db.Products
      group p by p.CategoryID into g
      select new {
             CategoryID = g.Key,
             CheapestProducts=
                   from q in g
                   where q.UnitPrice==g.Min(m=>m.UnitPrice)
                   select q
      };

5.Max求最大值

　　对应的SQL语句: select max(*) from xxx

(1). 简单形式

 例如：查找产品的最高单价。

var q=db.Products.Select(p=>p.unitPrice).Max();

(2).映射形式

 例如：查找产品的最高单价。

var q=db.Products.Max(p=>p.unitPrice);

(3).元素

例如：查找每个类别中单价最高的产品的相关信息。

var productDetails=
      from p in db.Products
      group p by p.CategoryID into g
      select new {
             CategoryID = g.Key,
             HighestProducts=
                   from q in g
                   where q.UnitPrice==g.Max(m=>m.UnitPrice)
                   select q
      };

6. Average求平均值

　　对应的SQL语句: select avg(*) from xxx

(1). 简单形式

 例如：查找所有产品的平均价。

var q=db.Products.Select(p=>p.unitPrice).Average();

(2).映射形式

 例如：查找产品的平均价。

var q=db.Products.Average(p=>p.unitPrice);

(3).元素

例如：查找每个类别中单价高于平均价的产品的相关信息。

var productDetails=
      from p in db.Products
      group p by p.CategoryID into g
      select new {
             CategoryID = g.Key,
             myProducts=
                   from q in g
                   where q.UnitPrice > g.Average(m=>m.UnitPrice)
                   select q
      };

五. 关联查询(Join)

　　用到的用户表和用户登录记录表如下：

  

　　这里类比SQL语句里的查询,查询包括内连接和外连接,其中，

1.内连接分为：隐式内连接和显示内连接.

特点：二者只是写法不同,查询出来的结果都是多表交叉共有的。

(1).隐式内连接: 多个from并联拼接

(2).显示内连接: join-in-on拼接，注意没有into哦！加上into就成外连接了。

PS：这里的内连接相当于sql中的等值连接inner join。

2.外连接分为：左外连接和右外连接.

(1).左外连接：查询出JOIN左边表的全部数据，JOIN右边的表不匹配的数据用NULL来填充。

(2).右外连接：查询出JOIN右边表的全部数据，JOIN左边的表不匹配的数据用NULL来填充。

PS：linq中没有sql中的left/right join, 只有join，左外连接和右外连接通过颠倒数据的顺序来实现。

　　注：外连接join后必须有into，然后可以加上XX.DefaultIfEmpty()，表示对于引用类型将返回null,而对于值类型则返回0。对于结构体类型，则会根据其成员类型将它们相应地初始化为null(引用类型)或0(值类型)，

如果仅需要统计右表的个数或者其它属性，可以省略XX.DefaultIfEmpty, 但如果需要点出来右表的字段，则不能省。

3. 分析几个场景，一对一，一对多，而且还要统计个数的案例

(1).用户表-用户详情表(一对一)：用内连接

(2).用户表-用户登录记录表(一对零,一对多)：用左外连接，用户表为左，如果统计个数需要用Distinct()去重.

                //4.查询账号中含有admin的所有用户的用户昵称、账号、和登录信息
                //4.1 隐式内连接(匿名类且不指定名称)
                Console.WriteLine("---------------04-多表关联查询--------------------");
                Console.WriteLine("---------------4.1 隐式内连接(匿名类且不指定名称)--------------------");
                var uList1 = (from a in db.Sys_UserInfor
                              from b in db.LoginRecords
                              where a.id == b.userId 
                              select new
                              {
                                  a.userName,
                                  a.userAccount,
                                  b.loginCity,
                                  b.loginIp,
                                  b.loginTime
                              }).ToList();
                foreach (var item in uList1)
                {
                    Console.WriteLine("姓名：{0}，账号：{1}，登录城市：{2}，登录IP：{3}，登录时间：{4}", item.userName, item.userAccount, item.loginCity, item.loginIp, item.loginTime);
                }
                //4.2 显式内链接(匿名类 且部分列指定名称)  
                Console.WriteLine("---------------4.2 显式内链接(匿名类 且部分列指定名称)  --------------------");
                var uList2 = (from a in db.Sys_UserInfor
                              join b in db.LoginRecords on a.id equals b.userId                         
                              select new
                              {
                                  UserName = a.userName,
                                  UserAccount = a.userAccount,
                                  b.loginCity,
                                  b.loginIp,
                                  b.loginTime
                              }).ToList();
                foreach (var item in uList2)
                {
                    Console.WriteLine("姓名：{0}，账号：{1}，登录城市：{2}，登录IP：{3}，登录时间：{4}", item.UserName, item.UserAccount, item.loginCity, item.loginIp, item.loginTime);
                }
                //4.3 查询所有用户的登录信息（左外连接的方式）
                //join时必须将join后的表into到一个新的变量XX中，然后要用XX.DefaultIfEmpty()表示外连接。
                //DefaultIfEmpty使用了泛型中的default关键字。default关键字对于引用类型将返回null,而对于值类型则返回0。对于结构体类型，则会根据其成员类型将它们相应地初始化为null(引用类型)或0(值类型)
                Console.WriteLine("-----------------------4.3 查询所有用户的登录信息（左外连接的方式）----------------------------");
                var uList3 = (from a in db.Sys_UserInfor
                              join b in db.LoginRecords on a.id equals b.userId into fk
                              from c in fk.DefaultIfEmpty()
                              select new
                              {
                                  UserName = a.userName,
                                  UserAccount = a.userAccount,
                                  c.loginCity,
                                  c.loginIp,
                                  c.loginTime
                              }).ToList();
                foreach (var item in uList3)
                {
                    Console.WriteLine("姓名：{0}，账号：{1}，登录城市：{2}，登录IP：{3}，登录时间：{4}", item.UserName, item.UserAccount, item.loginCity, item.loginIp, item.loginTime);
                }
                // 4.4 查询所有用户的登录信息（右外连接的方式）
                Console.WriteLine("-----------------------4.4 查询所有用户的登录信息（右外连接的方式）----------------------------");
                var uList4 = (from a in db.LoginRecords
                              join b in db.Sys_UserInfor on a.userId equals b.id into fk
                              from c in fk.DefaultIfEmpty()
                              select new
                              {
                                  UserName = c.userName,
                                  UserAccount = c.userAccount,
                                  a.loginCity,
                                  a.loginIp,
                                  a.loginTime
                              }).ToList();
                foreach (var item in uList4)
                {
                    Console.WriteLine("姓名：{0}，账号：{1}，登录城市：{2}，登录IP：{3}，登录时间：{4}", item.UserName, item.UserAccount, item.loginCity, item.loginIp, item.loginTime);
                }
                //4.5 查询每个用户的登录次数(用且应该用左外连接 )
                //注：这里需要加一个Distinct()去重，否则同一个账号会查出来多条数据重复了
                Console.WriteLine("-----------------------4.5 查询每个用户的登录次数(用且应该用左外连接 )----------------------------");
                var uList5 = (from a in db.Sys_UserInfor
                              join b in db.LoginRecords on a.id equals b.userId into fk
                              select new
                              {
                                  UserName = a.userName,
                                  UserAccount = a.userAccount,
                                  loginCount = fk.Count()
                              }).Distinct().ToList();
                foreach (var item in uList5)
                {
                    Console.WriteLine($"姓名：{item.UserName}，账号：{item.UserAccount}，登录次数：{item.loginCount}");
                }

 运行 结果：

特别注意：

　　上面的linq左外连接，是直接操控数据库，会映射成标准的left join的SQL语句，所以上面写法没问题；但是如果用linq进行做外链接，操作的是查询出来list，则select的时候必须对右表判空！！！

(1). 直接操控数据库

          var uList3 = (from a in db.Sys_UserInfor
                        join b in db.LoginRecords on a.id equals b.userId into fk
                        from c in fk.DefaultIfEmpty()
                        select new
                        {
                             UserName = a.userName,
                             UserAccount = a.userAccount,
                             c.loginCity,
                             c.loginIp,
                             c.loginTime
                         }).ToList();

(2).操控已经查询出来的list(必须对右表进行判空处理)

   var sList=db.Sys_UserInfor.ToList();
   var rList=db.LoginRecords.ToList();        
   var uList3 = (from a in db.Sys_UserInfor
                 join b in db.LoginRecords on a.id equals b.userId into fk
                 from c in fk.DefaultIfEmpty()
                 select new
                 {
                    UserName = a.userName,
                    UserAccount = a.userAccount,
                    loginCity = c==null?"":c.loginCity,
                    loginIp = c==null?"":c.loginIp,
                    loginTime = c==null?"":c.loginTime
                 }).ToList();

!

• 作       者 : Yaopengfei(姚鹏飞)
• 博客地址 : http://www.cnblogs.com/yaopengfei/
• 声     明1 : 如有错误，欢迎讨论，请勿谩骂^_^。
• 声     明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址，否则保留追究法律责任的权利。