使用 LINQ 对数据进行排序

2018-11-28 08:00:00 · 飞浪

解决的问题使用select、groupby和where方法获得所需的准确结果集后，您仍然面临将数据按正确顺序排列的问题。在T-SQL中，我们通过在末尾添加ORDERBY语句来实现此目的

解决的问题

使用select、groupby和where方法获得所需的准确结果集后，您仍然面临将数据按正确顺序排列的问题。在 T-SQL 中，我们通过在末尾添加 ORDER BY 语句来实现此目的：

      SELECT * FROM Employees ORDER BY GroupCode
    

LINQ 使orderby语句和 OrderBy() 方法可用来实现这一点。

我们的场景

在我们列出某个地点重复员工的子报告中，我们希望按员工姓氏对列表进行排序。为了使我们的查询有意义，我们将向测试集添加一名新员工：

      new Employee { Id = 1, FirstName = "Chris", LastName = "Behrens", GroupCode = "AA123", Addresses = new List<string>{"7814 Vandalia Avenue Apt 3b Los Angeles CA 91610"}, Salary = 100m },
    new Employee { Id = 2, FirstName = "Sam", LastName = "Smith", GroupCode = "AA128", Addresses = new List<string>{ "7814 Vandalia Avenue Apt 3b Los Angeles CA 91610" }, Salary = 150m },
    new Employee { Id = 3, FirstName = "Gill", LastName = "Bates", GroupCode = "ZZ867", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m },
    new Employee { Id = 4, FirstName = "Jane", LastName = "Anderson", GroupCode = "AA125", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m }
    

如果我们针对测试集执行当前查询：

      from e in employees
where e.GroupCode.ToUpper().Substring(0, 2) == "AA"
select e;
    

我们得到以下结果：

1 克里斯·贝伦斯

2 山姆·史密斯

4 简·安德森

前两条记录是按姓氏排序的，这纯属偶然。但最后一条记录的姓氏为“Anderson”，应该出现在列表中的第一个位置。为了实现这一点，我们将在查询中添加orderby子句：

      from e in employees
where e.GroupCode.ToUpper().Substring(0, 2) == "AA"
orderby e.LastName
select e;
    

对我们的测试数据集执行查询后，我们得到以下结果：

4 简·安德森

1 克里斯·贝伦斯

2 山姆·史密斯

正如我们想要的那样。在与老板交谈时，我们提到查询已完成，而您的老板说：“嗯……已婚人士的姓氏呢？那该怎么办？”您想了一会儿，然后回答说：“我猜他们会按姓氏排序，但我不确定。我会确保他们也按名字排序。”

我们将添加另一个测试用户到我们的集合中来测试这一点：

      new Employee { Id = 1, FirstName = "Chris", LastName = "Behrens", GroupCode = "AA123", Addresses = new List<string>{"7814 Vandalia Avenue Apt 3b Los Angeles CA 91610"}, Salary = 100m },
    new Employee { Id = 2, FirstName = "Sam", LastName = "Smith", GroupCode = "AA128", Addresses = new List<string>{ "7814 Vandalia Avenue Apt 3b Los Angeles CA 91610" }, Salary = 150m },
    new Employee { Id = 3, FirstName = "Gill", LastName = "Bates", GroupCode = "ZZ867", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m },
    new Employee { Id = 4, FirstName = "Jane", LastName = "Anderson", GroupCode = "AA124", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m },
    new Employee { Id = 5, FirstName = "Allison", LastName = "Behrens", GroupCode = "AA124", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m }
    

Allison Behrens，– 与 Chris Behrens 姓氏相同，但名字在 Chris 之前。此记录位于集合末尾，因此，即使结果按姓氏排序，它也可能排在 Chris Behrens 之后，除非我们修改查询来解决这个问题。为了实现这一点，我们将添加第二个 orderby 参数：

      var whereEmployees = from e in employees
where e.GroupCode.ToUpper().Substring(0, 2) == "AA"
orderby e.LastName, e.FirstName
select e;
    

得出的结果如下：

4 简·安德森

4 艾莉森·贝伦斯

1 克里斯·贝伦斯

2 山姆·史密斯

如果我们使用查询语法，它看起来会像这样：

      employees.Where(e => e.GroupCode.ToUpper().Substring(0, 2) == "AA").OrderBy(e => e.LastName).ThenBy(e => e.FirstName);
    

按降序排序

我们的细分报告包含位置和每个位置的员工数量，非常棒，但是，当针对实际数据集执行时，它有数百行。由于报告侧重于显示重复项，因此报告顶部的大部分数据都是只有少数重复项的位置。位置的重复项越多，它与报告目的的相关性就越高，因此我们的任务是更新报告，以便它按重复计数的降序显示数据。为了实现这一点，我们只需在查询中添加关键字 descending：

      from e in employees
group e by e.GroupCode.ToUpper().Substring(0, 2) into g
where g.Count() > 1
orderby g.Count() descending
select new { Location = g.Key, Total = g.Count() };
    

此查询将过滤结果以仅显示具有重复项的位置，然后它将首先显示重复项数量最多的位置，然后显示重复项较少的位置。在查询语法中，它甚至更简单：调用 OrderByDescending 而不是 OrderBy 方法。这是我们上面的有序查询，按 LastName 降序排序而不是升序排序，然后按 FirstName升序排序：

      employees.Where(e => e.GroupCode.ToUpper().Substring(0, 2) == "AA").OrderByDescending(e => e.LastName).ThenBy(e => e.FirstName);
    

要根据两个字段按降序排序，请调用 ThenByDescending：

      employees.Where(e => e.GroupCode.ToUpper().Substring(0, 2) == "AA").OrderBy(e => e.LastName).ThenByDescending(e => e.FirstName);
    

_{免责声明：本内容来源于第三方作者授权、网友推荐或互联网整理，旨在为广大用户提供学习与参考之用。所有文本和图片版权归原创网站或作者本人所有，其观点并不代表本站立场。如有任何版权侵犯或转载不当之情况，请与我们取得联系，我们将尽快进行相关处理与修改。感谢您的理解与支持！}

_查看原文

技术指南

阅读全文

使用 LINQ 对数据进行排序

杭州电子商务研究院

6年前 · 面向社会、服务行业、政产学研结合、整合资源、和谐发展

new Employee { Id = 1, FirstName = "Chris", LastName = "Behrens", GroupCode = "AA123", Addresses = new List<string>{"7814 Vandalia Avenue Apt 3b Los Angeles CA 91610"}, Salary = 100m }, new Employee { Id = 2, FirstName = "Sam", LastName = "Smith", GroupCode = "AA128", Addresses = new List<string>{ "7814 Vandalia Avenue Apt 3b Los Angeles CA 91610" }, Salary = 150m }, new Employee { Id = 3, FirstName = "Gill", LastName = "Bates", GroupCode = "ZZ867", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m }, new Employee { Id = 4, FirstName = "Jane", LastName = "Anderson", GroupCode = "AA125", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m }

new Employee { Id = 1, FirstName = "Chris", LastName = "Behrens", GroupCode = "AA123", Addresses = new List<string>{"7814 Vandalia Avenue Apt 3b Los Angeles CA 91610"}, Salary = 100m }, new Employee { Id = 2, FirstName = "Sam", LastName = "Smith", GroupCode = "AA128", Addresses = new List<string>{ "7814 Vandalia Avenue Apt 3b Los Angeles CA 91610" }, Salary = 150m }, new Employee { Id = 3, FirstName = "Gill", LastName = "Bates", GroupCode = "ZZ867", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m }, new Employee { Id = 4, FirstName = "Jane", LastName = "Anderson", GroupCode = "AA124", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m }, new Employee { Id = 5, FirstName = "Allison", LastName = "Behrens", GroupCode = "AA124", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m }