Connect – Microsoft Dynamics 365

 

LINQ filter: How to filter a list using another list with LINQ


Sep 16, 2019

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.

LINQ filter: How to filter a list using another list with LINQ

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.

LINQ filter: How to filter a list using another list with LINQ

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.

LINQ filter: How to filter a list using another list with LINQ

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.

Comments

*User Name field is required.

(will not be published)

*Real Name field is required.

(will not be published)

*A valid email is required.

*Company field is required.

*Comment field is required.
Does your team need help with CRM? Contact us:
LINQ Blog
Learn how to use LINQ to bulk update D365
Read the blog