Having a list containing more than 100.000 items provided us with some challenges, apparently it is not possible to retrieve the ItemCount based on certain criteria using the object model.
The problem is that we actually had to retrieve those items.
So a colleague came up with a rather dirty but very useful fix for that.
It’s a small piece of code that makes a View based on a desired query, and sort this on the Author (all the items are created by the same account). By setting the Collapse property on true SharePoint renders a HTML Header containing the total amount of items. By actually reading that piece of HTML he found a quick&dirty way to retrieve those totals.
Below you can find the code, and we were wondering if any of you out there would know another way without using the Search or talking directly to the Content Database.
/// <summary>
/// This function will return the count of the items found by the query, using the RenderAsHTML() method of the SPView object
/// IMPORTANT: It asumes that all Items are created using the same account (Author), if this is not the case,
/// please find or provide an other property that is equal for all ListItems
/// NOTE: This function has no error-handling inside.
/// </summary>
/// <param name="list">SPList object that holds the items to count</param>
/// <param name="query">CAML query-string</param>
/// <returns>-1 on error, otherwise number of items found</returns>
private static int GetItemCount(SPList list, string query)
{
return GetItemCount(list, query, "Author");
}
private static int GetItemCount(SPList list, string query, string GroupByProperty)
{
//Since files are added by the system, author will be the same for all
//adding GroupBy and setting the Collapse to true, the view will be rendered collapsed and show only the itemcount
query = String.Format("<GroupBy Collapse=\"TRUE\" GroupLimit=\"1\"><FieldRef Name=\"{0}\" /></GroupBy>{1}",GroupByProperty, query);
//create temp view
list.ParentWeb.AllowUnsafeUpdates = true;
string TempViewName = "TempViewForItemCount";
SPView newview = list.Views.Add(TempViewName, new StringCollection() {"Soep1", "Author" }, query, 10000000, false, false);
newview.Update();
list.Update();
//RenderAsHtml creates a small piece of HTML that contains the itemcount
string html = list.Views[newview.ID].RenderAsHtml();
//remove the temp view
list.Views.Delete(newview.ID);
list.ParentWeb.AllowUnsafeUpdates = false;
//init a counter
int count = 0;
//grab the count which is in brackets right after the '‎'-character
if (html.IndexOf("‎(") <= 0)
return -1;
html = html.Substring(html.IndexOf("‎(") + 8, html.IndexOf(")", html.IndexOf("‎(")) - (html.IndexOf("‎(") + 8));
//try to parse the count
if (!int.TryParse(html, out count))
return -1;
return count;
}
//testfunction
private void Test()
{
using (SPWeb myweb = new SPSite("http://somedomain.com").RootWeb)
{
SPList SomeList = myweb.Lists["SomeList"];
Console.WriteLine("End loading web at: " + DateTime.Now.ToLongTimeString());
string q = "<Where><Eq><FieldRef Name=\"SomeField\" /><Value Type=\"Text\">SomeValue</Value></Eq></Where>";
int count = GetItemCount(MenuVanDeDag, q);
}
}