none
Querying SPListItemcollection?

    Question

  • I have a query on a sharepoint list that populates an SPListItemCollection using SPList.GetItems(query).  I would like to in turn query this item collection to narrow down results further, but don't see a way to do so, as there is not GetItems method for SPListItemCollection.  What is the proper way to query the results of my previous query using these sharepoint data structures?  I would like to query the original query results many times.  Thanks in advance!
    Monday, June 13, 2011 2:00 PM

Answers

  • Normally I would use a lamda expression like :

    itemCol.Cast<SPListItem>.Where(i => item["Title"] == "Hello You!")

    However if there is alot of items in the collection it will be more effecient to use a CAML query. Any SPListItemCollection holds a reference to its SPList. You could execute your CAML like :

    itemCol.List.GetItems(SPQuery query)

    I wouldn't recommend LINQ to SharePoint for performance issues. However you can still use LINQ and lamda expressions.

    • Marked as answer by Chris Seewald Tuesday, June 14, 2011 1:32 PM
    Tuesday, June 14, 2011 11:08 AM

All replies

  • Hi,

    I would suggest to use Linq to SharePoint. Please take a look at this msdn page http://msdn.microsoft.com/en-us/library/ee535491.aspx

    Also please find here a few examples of using Linq and SPListItemCollections http://goldytech.wordpress.com/tag/splistitemcollection/


    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts
    Monday, June 13, 2011 8:27 PM
  • You can use LINQ..

     

    var listItems=from SPListItem c in list.Items

                        where (c["Title"]=="abc")

                        select c).ToList();

     

    Here list is the SharePoint List and it is queried for the Title="abc"

    Similarly you can query on any column.

    Monday, June 13, 2011 11:26 PM
  • use the index or column name in an IF LOOP and fetch whatever u require..
    Tuesday, June 14, 2011 7:05 AM
  • Normally I would use a lamda expression like :

    itemCol.Cast<SPListItem>.Where(i => item["Title"] == "Hello You!")

    However if there is alot of items in the collection it will be more effecient to use a CAML query. Any SPListItemCollection holds a reference to its SPList. You could execute your CAML like :

    itemCol.List.GetItems(SPQuery query)

    I wouldn't recommend LINQ to SharePoint for performance issues. However you can still use LINQ and lamda expressions.

    • Marked as answer by Chris Seewald Tuesday, June 14, 2011 1:32 PM
    Tuesday, June 14, 2011 11:08 AM
  • SyslvanStalker86 .. there is something called Row Limit, this is found in SPQuery.

    example:

    SPQuery query = new SPQuery();

    query.Query = "your query";

    query.RowLimit = 5; //it takes integer.


    Mai Omar Desouki | Software Consultant | Infusion | MCP, MCTS, MCPD, MCITP, MCT Microsoft Certified Trainer & MCC Microsoft Community Contributor | Email: mai_omar_86@live.com
    Tuesday, June 14, 2011 11:17 AM
  • However if there is alot of items in the collection it will be more effecient to use a CAML query. Any SPListItemCollection holds a reference to its SPList. You could execute your CAML like :

    itemCol.List.GetItems(SPQuery query)

     

    IMHO, this is a bad idea. You are not querying the original SPListItemCollection which is what Chris originally wanted. You are only using that object to get a reference to the list and then REQUERYING the list! The returned items are being brought over from the DB server a second time and stored in local memory in a second object. (And with each subsequent query, another trip to the DB and more memory allocated.)

    Chris, I think, had the right idea. If he wants to repeatedly query the original collection, he doesn't want to go back to the DB again and again. But he doesn't need to. The SPListItemCollection is an IEnumerable, so he can just query it with LINQ to Objects (not LINQ to SharePoint).

    The same point applies to Dimitry's suggestion of using LINQ to SharePoint. The only way you could do that would be to ignore the original collection and write a new LINQ-to-SharePoint query that goes back to the DB.

     


    Rick - MSFT
    Tuesday, June 14, 2011 9:59 PM
  • However if there is alot of items in the collection it will be more effecient to use a CAML query. Any SPListItemCollection holds a reference to its SPList. You could execute your CAML like :

    itemCol.List.GetItems(SPQuery query)

     

    IMHO, this is a bad idea. You are not querying the original SPListItemCollection which is what Chris originally wanted. You are only using that object to get a reference to the list and then REQUERYING the list! The returned items are being brought over from the DB server a second time and stored in local memory in a second object. (And with each subsequent query, another trip to the DB and more memory allocated.)

    Chris, I think, had the right idea. If he wants to repeatedly query the original collection, he doesn't want to go back to the DB again and again. But he doesn't need to. The SPListItemCollection is an IEnumerable, so he can just query it with LINQ to Objects (not LINQ to SharePoint).

    The same point applies to Dimitry's suggestion of using LINQ to SharePoint. The only way you could do that would be to ignore the original collection and write a new LINQ-to-SharePoint query that goes back to the DB.

     


    Rick - MSFT


    I actually say that I normally would perfer a lambda expressions. However I wouldn't want to iterate 100.000 items in memory. Just to fecth a few items. A database query would be faster here.

    The List is allready in the memory. It's referenced by the SPListItemCollection and hasn't been garbage collected.


    Also I would recommend you to start your SQL Server Profiler. If you believe that iterating over the SPListItemCollection dosn't make any roundtrips to the database (at least the first time you iterate over it).
    Wednesday, June 15, 2011 8:46 AM
  • My original dataset was around 20k items, which the original query narrowed down to ~200-500.  What I ended up doing was just using a foreach on the query results to find what I needed.  I realize that this isn't the best way to do this for a large itemcollection, but it was sufficient in this case.  I'll explore LINQ to objects some more to see if it could help me speed up my application.
    Wednesday, June 15, 2011 3:09 PM
  • A foreach loop is fine. There is no performance benefits of using LINQ to objects over a foreach loop.

     

     

    Wednesday, June 15, 2011 6:37 PM
  • Ok, thanks for the clarification.
    Wednesday, June 15, 2011 7:51 PM
  • I have the same problem .,

    But I want to use Caml ..

    to filter the SPItemCollection ..,

    the solution of getting it's parent list is not usfull at all because I already filtered it from another method and I want to filter it more after I made it SPListItemCollection.

    is there a way to use caml ?


    thank u for help
    Saturday, December 03, 2011 11:43 AM