how to use "where...in" in subsonicproject

I am using subsonicproject and the activerecord pattern.

I have a database with three tables: Cities, Concerthalls, Concerts. Each Concerthall has a foreign-key relationship to a City. Each Concert has a foreign-key relationsh开发者_JAVA百科ip to a Concerthall.

I would like to retrieve all concerts within a given city. So I need to get all the Concerthall-ids for a city and pass them to subsonicproject. In "normal" sql I would use the "where ... in"-statement, but how do I do it with subsonicproject?

I am thinking about something like (pseudocode): List concertHallsId=xxxxxxx; Concerts.Find(concert.ConcertHallId in concertHallIds);

thanks Thomas

You should be able to do something like:

var concertHallIds = new List<int>() { 1, 2, 3 };
var concerts = from c in db.concerts
               where concertHallIds.Contains(c.ConcertHallId)
               select c;

At least, that is the Linq version. Basically though, you should be ale to use .Contains() on your collection of concert hall ids, and SS puts those values into an in clause in the SQL.