in

Dot Net Mafia

Group site for developer blogs dealing with (usually) .NET, SharePoint, Office 365, Mobile Development, and other Microsoft products, as well as some discussion of general programming related concepts.

This Blog

Syndication

Archives

Corey Roth [MVP]

A SharePoint MVP bringing you the latest time saving tips for SharePoint 2013, Office 365 / SharePoint Online and Visual Studio 2013.

Select Where In with LINQ

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.

Published Jul 01 2008, 05:14 PM by CoreyRoth
Filed under:

Comments

 

JamesCurran said:

wouldn't

var filteredList =

   from myClass in myClassList

   join otherClass in myClassList2

   on myClass.Id equals otherClass.Id

   select myClass;

do the same thing?

July 2, 2008 8:54 AM
 

CoreyRoth said:

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;

July 3, 2008 9:58 AM
 

刘静 said:

var filteredList3 = from myClass in myClassList

                   where myClassList2.Contains(myClass.Id)

                   select myClass;

not do。

April 23, 2012 8:37 PM

Leave a Comment

(required)  
(optional)
(required)  
Add

About CoreyRoth

Corey Roth is an independent SharePoint consultant specializing in ECM, Apps, and Search.
2015 dotnetmafia.
Powered by Community Server (Non-Commercial Edition), by Telligent Systems