SharePoint, List.Items and List.GetItems(Query) and Linq

Question

Following on from suggestions, I am trying to use List.GetItems(Query) to retrieve my initial data subset rather than the entire list contents via List.Items. However, whereas List.Items.Cast() results in a usable IEnumerable for Linq, List.GetItems(Query).Cast() does not.

Working Code:

IEnumerable<SPListItem> results = SPContext.Current.Web.Lists[ListName].Items.Cast<SPListItem>().Where(item => item["Date"] != null).Where(item => DateTime.Parse(item["Date"].ToString()) >= StartDate).Where(item => DateTime.Parse(item["Date"].ToString()) <= EndDate);
MessageLine = results.Count().ToString();

Non-working Code:

string SPStartDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(this.StartDate);
string SPEndDate =   SPUtility.CreateISO8601DateTimeFromSystemDateTime(this.EndDate);

SPQuery MyQuery = new SPQuery();
MyQuery.Query = "<Where><And><And><Geq><FieldRef Name='Date'/><Value Type='DateTime'>" + SPStartDate + "</Value></Geq><Leq><FieldRef Name='Date'/><Value Type='DateTime'>" + SPEndDate + "</Value></Leq></And></Where>";

IEnumerable<SPListItem> results = SPContext.Current.Web.Lists[ListName].GetItems(MyQuery).Cast<SPListItem>();

MessageLine = results.Count().ToString();

The List.GetItems(Query).Cast() method produces the following Exception on the .Count() line:

Microsoft.SharePoint.SPException: Cannot complete this action. Please try again. ---> System.Runtime.InteropServices.COMException (0x80004005): Cannot complete this action. Please try again. at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListItemDataWithCallback(String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pSchemaCallback) at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback(String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pSchemaCallback) --- End of inner exception stack trace --- at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback(String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pSchemaCallback) at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData() at Microsoft.SharePoint.SPListItemCollection.Undirty() at Microsoft.SharePoint.SPBaseCollection.System.Collections.IEnumerable.GetEnumerator() at System.Linq.Enumerable.d__aa1.MoveNext() at System.Linq.Enumerable.Count[TSource](IEnumerable1 source) at Test.GetTransactionsInPeriod() at Test.CreateChildControls()

Can anyone suggest anything?

Answer 1

From the error message it looks like the CAML Query is wrong. You may want to run it through something like U2U's CAML Query Builder to double check. The error message is thrown by SharePoint before the requested casts. Glancing over it, I think you have an extra <And> at the beginning (<Where><And><And>)

By the way: Don't use SPWeb.Lists[Name]. This will load every list in the SPWeb (including Metadata), which is rather resource intensive. One of the SPWeb.GetList or SPWeb.Lists.GetList methods is better.

Answer 2

thanks Michael.. your answer really helped me regain my hair otherwise i'd have gone bald in frustration.. there was trouble my CAML query as well :) Thanks again

-Hardik