LINQ入门与标准查询运算符

LINQ的体系结构

查询表达式的完整语法

一、查询表达式必须以from子句开头，以select 或group子句结束。中间可以使用where,orderby ,join,let和其他子句。具有“延迟计算”的特性。

关键字有： from where ,select ,group ,into ,orderby,join,let,in,on,equals,by,ascending,descending等。

二、过滤

过滤是一种操作，以限制结果设定为使得它仅选定元素满足特定的条件。

Where

以下示例返回所有联机订单

from order in   SalesOrderHeaders
where order.OnlineOrderFlag == true
select new
{
    SalesOrderID = order.SalesOrderID,
    OrderDate = order.OrderDate,
    SalesOrderNumber = order.SalesOrderNumber
}

SalesOrderHeaders
   .Where (order => (order.OnlineOrderFlag == True))
   .Select (
      order =>
         new
         {
            SalesOrderID = order.SalesOrderID,
            OrderDate = order.OrderDate,
            SalesOrderNumber = order.SalesOrderNumber
         }
   )

SELECT [t0].[SalesOrderID], [t0].[OrderDate], [t0].[SalesOrderNumber]
FROM [Sales].[SalesOrderHeader] AS [t0]

以下示例声明并初始化 Where…Contains 子句中的数组，以查找 ProductModelID 或 Size 与数组中的值匹配的所有产品。

from p in   Products
, ,  }).Contains(p.ProductModelID) ||
      (new string[] { "L", "XL" }).Contains(p.Size)
select p

Products
   .Where (
      p =>
            (new Int32?, (Int32?), (Int32?) } .Contains (p.ProductModelID) ||
               new String[] { "L", "XL" } .Contains (p.Size)
            )
   )

-- Region Parameters

) = 'L'
) = 'XL'
-- EndRegion
SELECT *
FROM [Production].[Product] AS [t0]
WHERE ([t0].[ProductModelID] IN (@p0, @p1, @p2)) OR ([t0].[Size] IN (@p3, @p4))

三、投影操作

投影是在其中一个对象被变换成仅特定性能一种全新的形式的操作。(Select,SelectMany)

四、排序（Orderby）

排序操作允许基于一个序列中的元素上的单个或多个属性排序。

以下示例使用 orderby… descending以按照从高到低的顺序对价目表排序。

from p in Products
orderby p.ListPrice descending
select p.ListPrice

Products
   .OrderByDescending (p => p.ListPrice)
   .Select (p => p.ListPrice)

以下示例使用 OrderBy 和 ThenBy 以返回先按姓氏后按名字排序的联系人列表。

from product in   Products
orderby product.Name, product.ListPrice descending
select product

Products
   .OrderBy (product => product.Name)
   .ThenByDescending (product => product.ListPrice)

SELECT *
FROM [Production].[Product] AS [t0]
ORDER BY [t0].[Name], [t0].[ListPrice] DESC

五、分组

将数据基于一个共同的共享属性放入一些分组

以下示例返回按邮政编码分组的 Address 对象。

from address in Addresses
group address by address.PostalCode into addressGroup
select new
{
    PostalCode = addressGroup.Key,
    AddressLine = addressGroup
}

Addresses
   .GroupBy (address => address.PostalCode)
   .Select (
      addressGroup =>
         new
         {
            PostalCode = addressGroup.Key,
            AddressLine = addressGroup
         }
   )

SELECT [t0].[PostalCode]
FROM [Person].[Address] AS [t0]
GROUP BY [t0].[PostalCode]
GO

六、对嵌套的对象分组

七、聚合

执行任何类型的所需的聚合，并允许在创建自定义LINQ聚合。

Average平均值

以下示例使用 Average 方法以查找每种样式的产品的平均标价。

from product in Products
group product by product.Style into g
select new
{
    Style = g.Key,
    AverageListPrice = g.Average(product => product.ListPrice)
}

Products
   .GroupBy (product => product.Style)
   .Select (
      g =>
         new
         {
            Style = g.Key,
            AverageListPrice = g.Average (product => product.ListPrice)
         }
   )

SELECT AVG([t0].[ListPrice]) AS [AverageListPrice], [t0].[Style]
FROM [Production].[Product] AS [t0]
GROUP BY [t0].[Style]

八、连接（join和groupjoin。）

联接是指将一个数据源对象与另一个数据源对象进行关联或联合的操作。这两个数据源对象通过一个共同的值或属性进行关联。
　　LINQ的联接操作符将包含可匹配（或相同）关键字的两个或多个数据源中的值进行匹配。

1. join

　　join操作符类似于T-SQL中的inner join，将一个数据源与另一个数据源相联接，根据两个数据源中相等的值进行匹配。

from order in SalesOrderHeaders
join detail in SalesOrderDetails
on order.SalesOrderID equals detail.SalesOrderID
where order.OnlineOrderFlag == true
&& order.OrderDate.Month ==
select new
{
    SalesOrderID = order.SalesOrderID,
    SalesOrderDetailID = detail.SalesOrderDetailID,
    OrderDate = order.OrderDate,
    ProductID = detail.ProductID
}

SalesOrderHeaders
   .Join (
      SalesOrderDetails,
      order => order.SalesOrderID,
      detail => detail.SalesOrderID,
      (order, detail) =>
         new
         {
            order = order,
            detail = detail
         }
   )
   .Where (temp0 => ((temp0.order.OnlineOrderFlag == True) && (temp0.order.OrderDate.Month == )))
   .Select (
      temp0 =>
         new
         {
            SalesOrderID = temp0.order.SalesOrderID,
            SalesOrderDetailID = temp0.detail.SalesOrderDetailID,
            OrderDate = temp0.order.OrderDate,
            ProductID = temp0.detail.ProductID
         }
   )

GroupJoin

　　GroupJoin操作符常应用于返回“主键对象-外键对象集合”形式的查询，例如“产品类别-此类别下的所有产品”。

实例1：以下示例针对 SalesOrderHeader 表和 SalesOrderDetail 表执行 GroupJoin 以查找每个客户的订单数。组联接等效于左外部联接，它返回第一个（左侧）数据源的每个元素（即使其他数据源中没有关联元素）

var query =
from order in SalesOrderHeaders
join detail in SalesOrderDetails
on order.SalesOrderID equals detail.SalesOrderID into orderGroup
select new
{
    CustomerID = order.SalesOrderID,
    OrderCount = orderGroup.Count(),
    Detail = orderGroup
};

foreach (var group in query)
{
    Console.WriteLine("{0}", group.CustomerID);
    Console.WriteLine(" {0}", group.OrderCount);
    foreach (var orderInfo in group.Detail)
    {
        Console.WriteLine("---{0}", orderInfo.SalesOrderDetailID);
    }
    Console.WriteLine("");
}

SalesOrderHeaders
   .GroupJoin (
      SalesOrderDetails,
      order => order.SalesOrderID,
      detail => detail.SalesOrderID,
      (order, orderGroup) =>
         new
         {
            CustomerID = order.SalesOrderID,
            OrderCount = orderGroup.Count (),
            Detail = orderGroup
         }
   )

九、集合操作

在四个操作符的集合操作中，每个产生基于不同的标准的结果。

十、分区

把输入序列分为两个独立的部分，而不重新排列序列中的元素，然后返回其中一个。

十一、转换

运算符改变输入对象的类型，并在多种不同的应用范围中使用

十二、生成

新序列的值是由生成运算符创建的。

十三、量词操作

返回一个布尔值，即真或当一个序列中的部分或全部元素满足特定条件的假。

十四、元素操作

除了DefaultIfEmpty，其余八种标准查询元素运算符从集合返回单个元素。

 //1.筛选
            //找出赢得至少15场比赛的英国和奥地利赛车手。
            var query1 = from r in Formual.GetChampions()
                         where (r.Country == "UK" || r.Country == "Austria") && r.Wins > 15
                         select r;
            //1.1索引筛选
            //找出姓氏以A开头、索引为偶数的赛车手
            var query2 = Formual.GetChampions().Where((r, index) => r.LastName.StartsWith("A") && index % 2 != 0);
            //1.2类型筛选
            //取出下面所有string类型的参数
            object[] data = { "1", 2, "3", 4, "5", 6, "7", 8, "9", 10 };
            var query3 = data.OfType<string>();
            //2.复合的From子句
            //筛选驾驶法拉利的所有冠军
            var query4 = from r in Formual.GetChampions()
                         from c in r.Cars
                         where c == "Ferrari"
                         orderby r.LastName
                         select r.FirstName + " " + r.LastName;
            //2.1 使用SelectManay方式替换掉上面的写法
            var query5 = Formual.GetChampions().SelectMany(r => r.Cars, (r, c) => new { Racer = r, Car = c })
                .Where(r => r.Car == "Ferrari")
                .OrderBy(r => r.Racer.LastName)
                .Select(r => r.Racer.FirstName + " " + r.Racer.LastName);
            //3.排序
            //来自英国的赛车手按照赢得比赛的次数进行降序排序
            var query6 = from r in Formual.GetChampions() where r.Country == "UK" orderby r.Wins descending select r;
            //3.1使用ThenBy进行二次排序
            var query7 = Formual.GetChampions().Where(r => r.Country == "UK").OrderBy(r => r.Wins).ThenBy(r => r.FirstName).Take(10);
            //4.分组(group m by m.Country into g)
            //列出每个国家的冠军数
            var query8 = from r in Formual.GetChampions()
                         group r by r.Country into g
                         orderby g.Count() descending, g.Key
                         where g.Count() > 1
                         select new
                         {
                             Country = g.Key,
                             Count = g.Count()
                         };
            //4.1根据国家分组另一种写法
            var query9 = Formual.GetChampions()
                  .GroupBy(r => r.Country)
                  .OrderByDescending(g => g.Count())
                  .ThenBy(g => g.Key)
                  .Where(g => g.Count() > 1)
                  .Select(g => new { Country = g.Key, Count = g.Count() });
            //5.LINQ查询中的变量
            //上面的分组查询Count方法调用了多次。使用let子句可以改变这种形式
            var query10 = from r in Formual.GetChampions()
                          group r by r.Country into g
                          let count = g.Count()
                          orderby count descending, g.Key
                          where count > 1
                          select new
                          {
                              Country = g.Key,
                              Count = count
                          };
            //5.1使用Select方法创建匿名类型
            var query11 = Formual.GetChampions()
                .GroupBy(r => r.Country)
                .Select(g => new { Group = g, Count = g.Count() })
                .OrderByDescending(g => g.Count)
                .ThenBy(g => g.Group.Key)
                .Where(g => g.Count > 1)
                .Select(g => new
                {
                    Country = g.Group.Key,
                    Count = g.Count
                });
            //6.嵌套对象分组
            var query12 = from r in Formual.GetChampions()
                          group r by r.Country into g
                          let count = g.Count()
                          orderby count descending, g.Key
                          where count > 1
                          select new
                          {
                              Country = g.Key,
                              Count = count,
                              Racers = from r1 in g
                                       orderby r1.LastName
                                       select r1.FirstName + " " + r1.LastName
                          };
            foreach (var item in query12)
            {
                Console.WriteLine($"国家：{item.Country}，冠军数量：{item.Count}");
                foreach (var info in item.Racers)
                {
                    Console.WriteLine(info);
                }
            }
            //7.内连接
            //查出每年赛车手冠军和车队冠军
            var query13 = from r in Formual.GetChampions()
                          from yr in r.Years
                          select new
                          {
                              Year = yr,
                              Name = r.FirstName + " " + r.LastName
                          } into s1
                          join s2 in
                          from t in Formual.GetContructorChampions()
                          from yt in t.Years
                          select new
                          {
                              Year = yt,
                              Name = t.Name
                          }
                          on s1.Year equals s2.Year
                          orderby s2.Year
                          select new
                          {
                              Year = s1.Year,
                              Racer = s1.Name,
                              Team = s2.Name
                          };
            //另一种方式的写法
            var query14 = Formual.GetChampions()
                .SelectMany(m => m.Years, (m, y) => new { Racer = m, Year = y })
                .Join(Formual.GetContructorChampions()
                .SelectMany(m => m.Years, (m, y) => new { Team = m, Year = y })
                , m => m.Year, m1 => m1.Year
                , (m, m1) => new
                {
                    Year = m.Year,
                    Racer = m.Racer.FirstName + " " + m.Racer.LastName,
                    Team = m1.Team.Name
                })
                .OrderBy(m => m.Year);
            //8.左外连接(DefaultIfEmpty)
            var query15 = from r in Formual.GetChampions()
                          from yr in r.Years
                          select new
                          {
                              Year = yr,
                              Name = r.FirstName + " " + r.LastName
                          }
                          into s1
                          join s2 in from t in Formual.GetContructorChampions()
                                     from yt in t.Years
                                     select new
                                     {
                                         Year = yt,
                                         Name = t.Name
                                     }
                                     on s1.Year equals s2.Year into rt
                          from s2 in rt.DefaultIfEmpty()
                          orderby s1.Year
                          select new
                          {
                              Year = s1.Year,
                              Champion = s1.Name,
                              Constructor = s2 == null ? "no constructor championship" : s2.Name
                          };
            //9.组连接
            //就是join 关联不止是一个也可能包含多个
            var query16 = Formual.GetChampionships()
                .SelectMany(cs => new List<RacerInfo>()
                {
                    new RacerInfo{
                        Year =cs.Year,
                        Position = 1,
                        FirstName =cs.First.FirstName(),//定义的扩展方法
                        LastName = cs.First.LastName()//定义的扩展方法
                    },
                     new RacerInfo{
                        Year =cs.Year,
                        Position = 2,
                        FirstName =cs.Second.FirstName(),//定义的扩展方法
                        LastName = cs.Second.LastName()//定义的扩展方法
                    },
                      new RacerInfo{
                        Year =cs.Year,
                        Position = 3,
                        FirstName =cs.Third.FirstName(),//定义的扩展方法
                        LastName = cs.Third.LastName()//定义的扩展方法
                    }
                });
            var query17 = from r in Formual.GetChampions()
                          join r2 in query16 on
                          new
                          {
                              FirstName = r.FirstName,
                              LastName = r.LastName
                          }
                          equals
                          new
                          {
                              FirstName = r2.FirstName,
                              LastName = r2.LastName
                          }
                          into yearResults
                          select new
                          {
                              FirstName = r.FirstName,
                              LastName = r.LastName,
                              Wins = r.Wins,
                              Starts = r.Starts,
                              Results = yearResults
                          };
            foreach (var item in query17)
            {
                Console.WriteLine($"FirstName:{item.FirstName};LastName:{item.LastName};Wins:{item.Wins};Starts:{item.Starts}");
                foreach (var info in item.Results)
                {
                    Console.WriteLine(info.LastName);
                }
            }
            //10.集合操作(Distinct()、Union()、Intersect()和Except())
            //交集Intersect()
            Func<string, IEnumerable<Racer>> func = car => from r in Formual.GetChampions() from c in r.Cars where c == car orderby r.LastName select r;
            foreach (var item in func("Ferrari").Intersect(func("Mercedes")))
            {
                Console.WriteLine(item);
            }
            //11.合并(Zip(集合，委托（第一个参数和第二个参数合并）))
            var query18 = from r in Formual.GetChampions()
                          where r.Country == "Italy"
                          orderby r.Wins descending
                          select new
                          {
                              Name = r.FirstName + " " + r.LastName
                          };
            var query19 = from r in Formual.GetChampions()
                          where r.Country == "Argentina"
                          orderby r.Wins descending
                          select new
                          {
                              LastName = r.LastName,
                              Starts = r.Starts
                          };
            var query20 = query18.Zip(query19, (f, s) => f.Name + ",Starts:" + s.Starts);
            //12.分区(Take()、Skip())
            int pageSize = 5;
            int pageNum = (int)Math.Ceiling(Formual.GetChampions().Count() / (double)pageSize);
            Func<int, int, IEnumerable<Racer>> res = (x, y) => (from r in Formual.GetChampions() orderby r.LastName ascending, r.FirstName ascending select r).Skip(x * y).Take(y);
            for (int i = 0; i < pageNum; i++)
            {
                Console.WriteLine($"Page {i}");
                foreach (var item in res(i, pageSize))
                {
                    Console.WriteLine(item);
                }
            }
            //13.聚合操作(Count、Sum、Min、Max、Average和Aggregate操作符)
            //案例如下,其他聚合函数类似
            var query21 = from r in Formual.GetChampions()
                          group r by r.Country into c
                          select new
                          {
                              Country = c.Key,
                              Wins = (from r1 in c select r1.Wins).Sum()
                          };
            //14.转换操作符(ToList()、ToLookUp())
            var query22 = (from r in Formual.GetChampions()
                           from c in r.Cars
                           select new
                           {
                               Car = c,
                               Racer = r
                           }).ToLookup(cr => cr.Car, cr => cr.Racer);
            foreach (var item in query22["Ferrari"])
            {
                Console.WriteLine(item);
            }
            //15.生成操作符(Range()、Empty()、Repeat())
            var query23 = Enumerable.Range(1, 20).Select(n => n * 5);
            foreach (var item in query23)
            {
                Console.WriteLine(item);
            }
            //16.并行查询
            var query24 = from r in Formual.GetChampions().AsParallel() select r;
            //17.分区器
            var query25 = from r in Partitioner.Create(Formual.GetChampions(), true).AsParallel() select r;
            //18.取消
            var cts = new CancellationTokenSource();
            var query26 = from r in Formual.GetChampions().AsParallel().WithCancellation(cts.Token) select r;
            string input = Console.ReadLine();
            if (input.ToLower().Equals("y"))
            {
                cts.Cancel();
            }

巴特西

LINQ入门与标准查询运算符

LINQ的体系结构

查询表达式的完整语法

二、过滤

Where

三、投影操作

四、排序（Orderby）

五、分组

六、对嵌套的对象分组

七、聚合

Average平均值

八、连接（join和groupjoin。）

1. join

GroupJoin

九、集合操作

十、分区

十一、转换

十二、生成

十三、量词操作

十四、元素操作

最新文章

热门文章

巴特西

LINQ入门与标准查询运算符

LINQ的体系结构

查询表达式的完整语法

二、过滤

Where

三、投影操作

四、排序（Orderby）

五、分组

六、 对嵌套的对象分组

七、聚合

Average平均值

八、连接（join和groupjoin。）

1. join

GroupJoin

九、集合操作

十、分区

十一、转换

十二、生成

十三、量词操作

十四、 元素操作

最新文章

热门文章

六、对嵌套的对象分组

十四、元素操作