Ordering Data with LINQ
The Problem This Solves
Once you have obtained exactly the resultset you want with the select, groupby, and where methods, you're still faced with the problem of getting the data into the proper order. In T-SQL, we achieve this by tacking an ORDER BY statement on at the end:
1SELECT * FROM Employees ORDER BY GroupCodeLINQ makes the orderby statement and the OrderBy() method available to accomplish this.
Our Scenario
In our sub-report where we list the duplicated employees for a location, we want to sort the list by the last name of the employee. To make our query meaningful, we'll add a new employee to our test set:
1new Employee { Id = 1, FirstName = "Chris", LastName = "Behrens", GroupCode = "AA123", Addresses = new List<string>{"7814 Vandalia Avenue Apt 3b Los Angeles CA 91610"}, Salary = 100m },
2 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 },
3 new Employee { Id = 3, FirstName = "Gill", LastName = "Bates", GroupCode = "ZZ867", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m },
4 new Employee { Id = 4, FirstName = "Jane", LastName = "Anderson", GroupCode = "AA125", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m }If we execute our current query against the test set:
1from e in employees
2where e.GroupCode.ToUpper().Substring(0, 2) == "AA"
3select e;- we receive the following results:
1 Chris Behrens
2 Sam Smith
4 Jane Anderson
The first two records are ordered by last name, by accident. But the final record, with the last name of "Anderson", should appear first in the list. To make this happen, we'll add an orderby clause to our query:
1from e in employees
2where e.GroupCode.ToUpper().Substring(0, 2) == "AA"
3orderby e.LastName
4select e;After executing our query against our test data set, we get the following results:
4 Jane Anderson
1 Chris Behrens
2 Sam Smith
Just like we want. In talking with our boss, we mention that the query is complete and your boss says, "Hmmm…what about married people with the last name? What happens with that?" You think about it for a minute and reply, "I guess they would be ordered by last name and then I'm not sure. I'll make sure that they're ordered by first name as well."
We'll add another test user to our set to test this out:
1new Employee { Id = 1, FirstName = "Chris", LastName = "Behrens", GroupCode = "AA123", Addresses = new List<string>{"7814 Vandalia Avenue Apt 3b Los Angeles CA 91610"}, Salary = 100m },
2 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 },
3 new Employee { Id = 3, FirstName = "Gill", LastName = "Bates", GroupCode = "ZZ867", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m },
4 new Employee { Id = 4, FirstName = "Jane", LastName = "Anderson", GroupCode = "AA124", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m },
5 new Employee { Id = 5, FirstName = "Allison", LastName = "Behrens", GroupCode = "AA124", Addresses = new List<string>{"2810 Pisces Ct Albuequerque, NM 87144"}, Salary = 100m }Allison Behrens,– the same last name as Chris Behrens, but a first name which precedes his first name, Chris. This record is at the end of the set, so, even though the results are ordered by last name, it will likely end up after Chris Behrens unless we modify the query to fix that. To make this happen, we'll add a second orderby argument:
1var whereEmployees = from e in employees
2where e.GroupCode.ToUpper().Substring(0, 2) == "AA"
3orderby e.LastName, e.FirstName
4select e;This produces these results:
4 Jane Anderson
4 Allison Behrens
1 Chris Behrens
2 Sam Smith
If we were working in query syntax, it would look like this:
1employees.Where(e => e.GroupCode.ToUpper().Substring(0, 2) == "AA").OrderBy(e => e.LastName).ThenBy(e => e.FirstName);OrderByDescending
Our breakdown report with the locations and the count of employees at each location is great but, when it's executed against the real dataset, it has hundreds of rows. Because the report is focused on showing duplicates, much of the data at the top of the report is locations with only a few duplicates. The more duplicates a location has, the more relevant it is to the purpose of the report, so we've been tasked with updating the report so that it displays the data in descending order of duplicate count. To make this happen, we simply add the keyword descending to our query:
1from e in employees
2group e by e.GroupCode.ToUpper().Substring(0, 2) into g
3where g.Count() > 1
4orderby g.Count() descending
5select new { Location = g.Key, Total = g.Count() };This query would filter the results to only have locations with duplicates and then it would present the locations with the greatest number of duplicates first, followed by locations with fewer duplicates. In the query syntax, it's even simpler: call the OrderByDescending, instead of the OrderBy method. Here is our ordered query from above sorted by LastName descending _instead of ascending, and then by FirstName _ascending:
1employees.Where(e => e.GroupCode.ToUpper().Substring(0, 2) == "AA").OrderByDescending(e => e.LastName).ThenBy(e => e.FirstName);To sort it descending on both fields, call ThenByDescending:
1employees.Where(e => e.GroupCode.ToUpper().Substring(0, 2) == "AA").OrderBy(e => e.LastName).ThenByDescending(e => e.FirstName);