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.