OfType<SPListItem>() says SPListItemCollection does not contain a definition for OfType()

Question

I'm using Linq to SharePoint rather than CAML to query list data from SharePoint. See the code I have written:

SPList productsList = SPContext.Current.Web.Lists["Products"];
IEnumerable<SPListItem> products = productsList.Items.OfType<SPListItem>();

Above code shows error at OfType() says SPListItemCollection does not contain a definition for OfType().

Can somebody resolve this? Am I missing any assemblies?

Answer 1

As Robert pointed out using Linq directly on SharePoint lists is not a very good choice regarding performance.

The reason is that Linq uses the GetEnumerator method of the IEnumerable interface to execute the Linq query. And every time GetEnumerator is used all SPListItems are retrieved from the database. For 100 list items this means 100 roundtrips to the database.

Before SharePoint 2010 the best way to perform this task was in the way Aanchal explained. Create a CAML query and retrieve matching SPListItems from the list. After the query is executed the data is memory and no further roundtrips to the database are required.

[Update with information on Aanchal's comment]: If you provide an empty CAML query this is like requesting all items from a database table (select * from t). This might be appropriate for a small list, but it has am impact on overall system performance. The impact increases the more list items you retrieve. Best advice ist to use CAML and let the database engine do its job. It was built for this.

With SharePoint 2010 you can use Linq to SharePoint (http://msdn.microsoft.com/en-us/library/office/ee535491(v=office.14).aspx). I have not tested how it compares to pure CAML queries but you should be better of than directly using Linq on SPListItemCollections.

Answer 2

I understand where you are coming from Sivakumar, LINQ to SharePoint is a beautiful thing, especially when coming to SharePoint from other development areas EF/Linq to SQL etc.

Unfortunately in my experience it just doesn't work. I have written and deployed solutions using it. Yes it can perform worse than an equivalent CAML query. But the problem I had is that occasionally it doesn't work at all. At random, after a few hours or a few months, it would stop working, requiring an IIS Reset to temporarily resolve. This happened to me at multiple sites and even after multiple support tickets with Microsoft I never found a way to resolve the issue.

So it was back to CAML for me. You can review the CAML generated by your LINQ to SharePoint as a shortcut to learning how it works. I would also suggest one of the available CAML designer programs out there to help you develop and test your queries.

I also always write my CAML queries using LINQ to XML which makes them easier to read/maintain:

SPQuery qry = new SPQuery();
        string now = Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now);
        XElement queryXML = new XElement("Where",
                                                new XElement("And",
                                                   new XElement("Or",
                                                       new XElement("Leq",
                                                           new XElement("FieldRef", new XAttribute("Name", "FIT_StartDate")),
                                                           new XElement("Value", new XAttribute("Type", "DateTime"), now)
                                                       ),
                                                       new XElement("IsNull",
                                                           new XElement("FieldRef", new XAttribute("Name", "FIT_StartDate"))
                                                       )
                                                   ),
                                                   new XElement("Or",
                                                       new XElement("Geq",
                                                           new XElement("FieldRef", new XAttribute("Name", "_EndDate")),
                                                           new XElement("Value", new XAttribute("Type", "DateTime"), now)
                                                       ),
                                                       new XElement("IsNull",
                                                           new XElement("FieldRef", new XAttribute("Name", "_EndDate"))
                                                       )
                                                   )
                                                )
                                            );
        qry.Query = queryXML.ToString();
        qry.Query += "<OrderBy><FieldRef Name='FIT_ItemOrder' Ascending='True' /></OrderBy>";
        SPListItemCollection items = theList.GetItems(qry);

Answer 3

You can try following:

List<SPListItem> listItems = list.GetItems(query).Cast<SPListItem>().ToList();

Answer 4

You haven't shown us what assemblies you are using so it is hard to say if you are missing any or not.

That said I just resolved the exact same error by adding:

using System.Linq;

to my code.

Add this to yours if you don't already have it and see if it resolves your issue.