Select Where In with LINQ

Posted Tuesday, July 1, 2008 5:14 PM by CoreyRoth

I can't talk about SharePoint all the time, so I thought I would talk about how to perform a type of query with LINQ.  In T-SQL you might have wrote something like this at one point.

SELECT Title, Id FROM Table1 WHERE Id IN (SELECT Id FROM Table2)

Basically, I am looking for all rows in Table1 where there is a matching Id in Table2.  Effectively I want a contains or exists type comparison between tables or lists.  I recently ran into a scenario where I wanted to do this and the syntax wasn't immediately obvious to me so I thought I would post something on it.  Let's start by defining a simple class.

public class MyClass

{

    public string Title

    {

        get;

        set;

    }

 

    public int Id

    {

        get;

        set;

    }

}

We'll then start by adding some test data to a list of this class.

List<MyClass> myClassList = new List<MyClass>();

myClassList.Add(new MyClass() { Title = "Title 1", Id = 1 });

myClassList.Add(new MyClass() { Title = "Title 2", Id = 2 });

myClassList.Add(new MyClass() { Title = "Title 14", Id = 14 });

In this case I want to compare it to a list of integers to find which items of MyClass match.

List<int> subQueryList = new List<int> { 1, 14, 97, 3, 11 };

Now, to perform the LINQ query.  The key to this kind of query makes use of the contains extension method on the list.

var filteredList = from myClass in myClassList

                    where subQueryList.Contains(myClass.Id)

                    select myClass;

Enumerating this query would return MyClass objects with an Id of 1 and 14.  This works well given a simple list of integers but what if we have two different lists of MyClass?  Here is our second list.

List<MyClass> myClassList2 = new List<MyClass>();

myClassList2.Add(new MyClass() { Title = "Title 5", Id = 5 });

myClassList2.Add(new MyClass() { Title = "Title 2", Id = 2 });

myClassList2.Add(new MyClass() { Title = "Title 14", Id = 14 });

One way that comes to mind to handle this is to write a LINQ query to get a list of the Ids for the second list and then query in a similar manner.

// get a list of ids from the other list of classes

var idList = from myClass in myClassList2

            select myClass.Id;

 

// subquery using the idList

var filteredList2 = from myClass in myClassList

                   where idList.Contains(myClass.Id)

                   select myClass;

Enumerating filteredList2 would return MyClass objects with an Id of 2 and 14.  Instead of using a subquery to get a list of Ids, what about something like this?

var filteredList3 = from myClass in myClassList

                    where myClassList2.Contains(myClass.Id)

                    select myClass;

This will compile just fine, but as expected it returns no results.  Although the myClass in each list with Ids of 2 and 14 have identical values, they are different objects.  If you wanted to exert some additional effort, you could get this to work, but I am not going to cover that today.

Filed under:

Comments

# re: Select Where In with LINQ

Wednesday, July 2, 2008 8:54 AM by JamesCurran

wouldn't

var filteredList =

   from myClass in myClassList

   join otherClass in myClassList2

   on myClass.Id equals otherClass.Id

   select myClass;

do the same thing?

# re: Select Where In with LINQ

Thursday, July 3, 2008 9:58 AM by CoreyRoth

That is correct.  This is equivalent to a join.  I should have focused more on the other case of finding cases where items in one list are not in the other such as the following example.

       var filteredList = from myClass in myClassList

                          where !subQueryList.Contains(myClass.Id)

                          select myClass;

# re: Select Where In with LINQ

Monday, April 23, 2012 8:37 PM by 刘静

var filteredList3 = from myClass in myClassList

                   where myClassList2.Contains(myClass.Id)

                   select myClass;

not do。

Leave a Comment

(required) 
(required) 
(optional)
(required)