Linq to SQL Records where child ID in list

Question

I have a site where I have a database of persons and each person can have multiple intrests which is handled through a one to many relationship between a persons table and an interests table. On the site I want to have a serach where you can have a multiple select box of interests and the user can pick as many as they want. What I want to return is a record set of all persons who have any interests contained in the selected options. My code is C# asp.net and looks something like this so far:

personResults = (from p in data.Persons
                 orderby p.lastName, p.firstName
                 select p);

And I want to add something like this although I know the last line in the following code is totally bogus. Assume that interests is my multiple select box ID.

List<int> interestIdList = new List<int>();
    if (interest.GetSelectedIndices().Length > 0) {
        foreach(int selectedIndex in interest.GetSelectedIndices()){
        interestIdList.Add(int.Parse(interest.Items[selectedIndex].Value));
    }
    personResults = personResults.Where(x => interestIdList.Contains(x.Interests[].interestID));
}

The problem is that last line of code. Because x.Interests is a collection of interest objects out of the database I can't access their interestID (how does the code now which interest items ID to access if a person has 5 interests). If it will help I can use my list of interest ID's to build a list of interest objects but I still cant figure out how to build the query.

Again to state my goal I want any person who has an interest where the interest ID is in the interestIdList array. They do not all have to match; as long as there is at least one common value I want the record.

Answer 1

If the appropriate relations from the persons table to the "personsWithInterests" table to the interests table is set up in the DBML, you can try this:

ArrayList ids = new ArrayList();
foreach (int index in interest.GetSelectedIndices())
{
    ids.Add(interest.Items[index].Value);
}
string[] idArray = (string[])ids.ToArray(typeof(string));
var personsWithInterests = (from pi in data.PersonInterests where idArray.Contains(pi.Interest.Id.ToString()) select pi.Person).Distinct();