Find all list items with multiple values used
I was working with Russ today and an interesting question came up: If a user had a list with a multivalue column, how do we find all the list items with more than 1 value selected?
After much trial and error, LINQ was the simplest to implement. Here's the list we used:
Testcol is a multi value column and thus our expected result was to return the Recipe #2 and Recipe #4 items. Returning #3 or #1 was a fail.
On the backend, SharePoint splits it's values with a #; combination. We can see this using the u2u CAML builder:
So the resultant code was a LINQ query with a split on the ;# values:
using (SPSite oSite = new SPSite("https://VM-SP2010/"))
{
using (SPWeb oWeb = oSite.RootWeb)
{
SPList oList = oWeb.Lists["TestList"];
List<SPListItem> items = (from l in oList.Items.OfType<SPListItem>() where l["Testcol"].ToString().Split(new[] { ";#" }, StringSplitOptions.None).Length > 3 select l).ToList<SPListItem>();
foreach (SPListItem item in items)
{
Console.WriteLine(item.DisplayName);
}
}
}
Console.WriteLine("All done!");
Console.ReadLine();
And the final result: