How to filter a list with another list using LINQ
Language-integrated query (LINQ) can help programmers retrieve data from multiple sources and shape their results with less coding.
LINQ filters data to avoid searching unnecessary data sets and to reduce the amount of data that needs to be transferred or handled. Developers like it because they don’t have to learn a new query language for every type of data source or data format. And it’s type safe.
Comparing values
Usually, analysts need to compare one or two values in a database against a list of many. Here’s an example, using movie listings:
The genre column stores values as a pipe-delimited list of strings. The genres would typically be stored in a separate table, but this case also involved a M:M table, so it was easier to leverage the genre column instead of dealing with the joins.
You can search the movie listing for several genres by manually typing them or by selecting them from a CheckBoxList:
The selected genres will be returned as a string array, which need to be compared against the genre column. Or you could use a LINQ filter.
Using a LINQ filter
To use a LINQ filter to compare the data, create a query like this:
var filter = new[] { "Action", "Animation", "Comedy" };
GetMovies()
.Where(movie =>
movie.Genre.Split('|')
.Select(arrayElement => arrayElement.Trim())
.Any(value => filter.Contains(value)))
.Dump();
The query includes a filter for the selected genres (a string array), and the GetMovies() method returns a list of movies. In this example, only a small collection of data was used, but it could have been retrieved from a database.
Next, the genre from the list of movies is converted to an array. Split the values on the delimited character, trim the elements to remove the spaces, and then compare the results to the filter to see if the genres match.
In this query, the results are displayed using the Dump() method and the following movies would be returned:
The filter could be a string (var filter = "Action Animation Comedy";). The ‘Contains’ method would also work, if you prefer to manually type the genres you want to search for.
While this LINQ query method works great — and simplifies filtering — it does occur on the client. With large data sets, it may be more efficient to filter at the database-level.
How Wipfli can help
We love sharing IT tips that make your life easier and your data more useful. Our IT and digital consultants have decades of experience, plus passion and imagination. If you have an IT question or problem, we’re excited to solve it. Contact us today or read more about our digital services.
Related content:
