0

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>
  • Looks like I answered by own question but can't find the delete link anymore. – Peter Aug 5 '13 at 5:28
  • I think you did! Add the last part as an answer, with a short line about why. Then this question will be helpful to others in the future :) – Robert Lindgren Aug 5 '13 at 5:40
0

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>";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.