CAML query many-to-many relationship

Question

I have a case where a customer can have many contacts and a contact can be assigned to many customers.

Currently I have a page for each customer, on load I get all the contacts (a publishing page for each contact) for the customer. Currently I have this code which works but performs badly:

var lookups = new SPFieldLookupValueCollection();
foreach (var customer in customers)
{
    var contactLookups = new SPFieldLookupValueCollection(customer["Contacts"].ToString());
    lookups.AddRange(contactLookups);
}

var contacts = web.Lists["Pages"].Items.Cast<SPListItem>()
    .Where(a => lookups.Exists(value => value.LookupId == a.ID);

It performs badly because the Pages library contains the PublishingPageContent column which is a lot of HTML sent over the wire for each contact.

I need to convert this piece of code to a CAML query and it works fine if I have a SPFieldLookupValue.LookupId but now I can have multiple, i.e. a SPFieldLookupValueCollection.

How can I use a condition like this: .Where(a => lookups.Exists(value => value.LookupId == a.ID); in a CAML query?

Do I need to use <In> and append a string with value for each id in the lookup collection, i.e. build like so:

 <In>
    <FieldRef Name='ID' />
    <Values>
       <Value Type='Counter'>354</Value>
       <Value Type='Counter'>353</Value>
    </Values>
 </In>

Answer 1

Ok so it looks like I answered by own question, I changed it to use a CAML query like so:

StringBuilder sb = new StringBuilder();

foreach (var lookup in lookups)
{
    sb.AppendLine(string.Format("<Value Type='Counter'>{0}</Value>", lookup.LookupId.ToString()));
}

SPQuery query = new SPQuery();
query.Query = @"<Where>
                <And>
                    <And>
                    <IsNotNull>
                        <FieldRef Name='Office' />
                    </IsNotNull>
                    <Eq>
                        <FieldRef Name='_ModerationStatus' />
                        <Value Type='ModStat'>0</Value>
                    </Eq>
                    </And>
                    <In>
                        <FieldRef Name='ID' />
                        <Values>"
                            + sb.ToString() +
                        @"</Values>
                    </In>
                </And>
            </Where>";