By Rod McBride
Usually, when filtering data from a database or a collection, you are comparing one or two values to a list of many. However, with a recent application, I found myself needing to filter a list of items using another list.
The movie listing in Figure 1 below provides a good example.
Figure 1. Sample Movie Listing
Notice that the genre column in the listing above stores the values as a pipe-delimited list of strings. The genres would typically be stored in a separate table, but in my case, it also involved a M:M table, so it was easier to leverage the genre column instead of dealing with the joins.
If you need to search the movie listing, you may want to search for several genres by selecting them from a CheckBoxList, as shown in Figure 2.
Figure 2. Search Genres
The selected genre[s] in Figure 2 would be returned as a string array, which would then need to be compared to the genre column in Figure 1. In some cases, you may just want to type in the genres of interest instead of using a CheckBoxList, but either way, the array generated from Figure 1 or the strings manually entered still need to be compared to the table column.
Using a LINQ filter
LINQ [language-integrated query] makes querying and filtering a lot easier in most cases, so I decided to try that first using LINQPad. I created the query below:
varfilter = new[] { "Action", "Animation", "Comedy"};
GetMovies[]
.Where[movie =>
movie.Genre.Split['|']
.Select[arrayElement => arrayElement.Trim[]]
.Any[value => filter.Contains[value]]]
.Dump[];
Listing 1. LINQ query to filter the movies by selected genres
First, I added a filter for the selected genres [a string array] in Figure 2. The GetMovies[] method returns a list of movies. In this case, it’s a just a small collection created within the method, but it could have been retrieved from a database.
Next, the genre from the list of movies is converted to an array by splitting the values on the delimited character, then trimming the elements to remove the spaces and then comparing to the filter to see if any of the genres match those I am interested in.
Finally, the results are displayed using the Dump[] method. Using the sample data in Figure 1 and the genres selected in Figure 2, the following movies would be returned.
Figure 3. Filtered movie listing
The filter could be just a string like this [varfilter = "Action Animation Comedy";] and that would work as well given the use of the ‘Contains’ method. It’s an example of manually typing in the genres to search for.
While the above LINQ query worked great in my case [it was a secondary filter] and simplified things, the filtering does occur on the client. If you are dealing with large sets of data, it’s more efficient to do the filtering at the database-level.
If you have any questions about LINQ queries, contact Wipfli. You can also read more technology-focused articles here.
Before LINQ came out with C# 3.0, we wrote a LOT of code like this:
1public MyEmployeeList Method[] 2{ 3 MyEmployeeList aaEmployees = new MyEmployeeList[]; 4 5 foreach[var record in records] 6 { 7 if [record.Fields["GroupType"].Substring[0, 2] == "AA"] 8 { 9 var newEmployee = new Employee[]; 10 newEmployee.Id = int.Parse[record.Fields["employee_id"].ToString[]]; 11 newEmployee.FirstName = record.Fields["first_name"]; 12 newEmployee.LastName = record.Fields["last_name"]; 13 14 aaEmployees.AddEmployee[newEmployee]; 15 } 16 } 17 18 return aaEmployees; 19}
Here's what's happening here:
- We're creating an empty list of employees named aaEmployees.
- We're looping through a collection of records, that may be returned from a database or another data source.
- We're determining whether the record's group type field begins with "AA" – otherwise, we want to skip the record.
- If it did match "AA", we create a new employee record and set its properties to match the values in the record.
- Then we add the new employee object to our Employee list.
- Once we're looping, we return the collection of employee objects, filtered to only have employees with a GroupType that starts with "AA".
Compare that code to this:
1return from record in records 2 where record.Fields["GroupType"].Substring[0, 2] == "AA" 3 select new Employee 4 { 5 Id = int.Parse[record.Fields["employee_id"].ToString[]], 6 FirstName = record.Fields["first_name"], 7 LastName = record.Fields["last_name"] 8 };
A picky reader will observe that the return type is different between the two - the first returns a MyEmployeeList and the second an IEnumerable - but it's clear that life is better working with the second, more fluent syntax. I have a grammar checker which is smart enough to tell code from English - except with LINQ, where it attempts to fix my grammar, mistaking it for plain English. All of our code should be so comprehensible.
LINQ is a set manipulation query language - it automates much of the grunt work and code that is common to software by making set operations generic.
We have been tasked with writing a business layer that will filter and organize a set of results returned from a query. The results have certain elements that need to be transformed, filtered, ordered, and grouped. It's important to understand that with LINQ, the results may have come from a database, they may have come from the file system, or by listening on a network socket, or they may be hard-coded. How the results got to us is largely immaterial - LINQ is designed to be utterly generic in how it performs the kind of set operations we're going to work with.
To begin with, we have a set of Employee objects, like the results of the query we looked at in the first section, but not filtered for having a group type beginning with AA. So our Employee object looks like this:
1public class Employee 2{ 3 public string FirstName { get; set; } 4 public string LastName { get; set; } 5 6 public int Id { get; set; } 7 8 public string GroupCode { get; set; } 9 10 public List Addresses { get; set; } 11}
Very simple. For the purposes of this guide, we're creating a set of three Employees with this code:
1List employees = new List 2{ 3 new Employee { Id = 1, FirstName = "Chris", LastName = "Behrens", GroupCode = "AA123", Addresses = new List{"7814 Vandalia Avenue Apt 3b Los Angeles CA 91610"} }, 4 new Employee { Id = 2, FirstName = "Sam", LastName = "Smith", GroupCode = "AA128", Addresses = new List{ "7814 Vandalia Avenue Apt 3b Los Angeles CA 91610" } }, 5 new Employee { Id = 3, FirstName = "Gill", LastName = "Bates", GroupCode = "ZZ867", Addresses = new List{"2810 Pisces Ct Albuequerque, NM 87144"} } 6};
And this set is contained in a List variable called Employees. We need an array of the employee IDs - we have a set of employee IDs that we want to filter from the results. This statement returns the entire set as an array:
1var employeeArray = [from e in employees 2select e].ToArray[];
We always begin our LINQ statements with from - this defines the set we're working with and the variable we'll use for our set operations. Here, we're selecting the entire employee set, defining the set variable as , and selecting it, that is, returning the value for the query. Finally, we wrap the entire query and transform it into an array with ToArray[].
This is great, but now we have an array of Employee objects - we want a simpler array of ints containing only the ID. To narrow the scope of what is being selected, we select the ID property on our set variable :
1var employeeIdArray = [from e in employees 2 select e.Id].ToArray[];
For the purposes of this guide, we're going to focus on the _fluent _syntax - the syntax which is close to T-SQL and English:
1var employeeIdArray = [from e in employees 2 select e.Id].ToArray[];
In addition to the fluent syntax, you can also invoke LINQ as function calls, which are made available to set types as extension methods:
1var employeeIdArray = employees.Select[e => e.Id].ToArray[];
In this case, we're expressing our set variable for our Select call as a lambda expression:
1.Select[e => e.Id]
Just like with the fluent syntax you get Intellisense for the members of the set variable as you type. You can mix these two syntaxes as well:
1var employeesAddresses = from e in employees select e.Addresses.First[];
The .First[] method does just what you think it does – it gets the first element in the list. This would return an IEnumerable of type string – the type of the addresses, and it would contain the first address listed for every employee in the employee set.