Sharepoint List retrieve large data C#

Question

Does TryGetList actually retrieves the entire list or does it work like IQueryable ? If my list have 1 million records is the following approach right?

  var list = web.Lists.TryGetList(<LIST NAME>);


  SPListItem item = list .Items.Cast<SPListItem>().FirstOrDefault(x => x["Id"] != null && x["Id"].ToString() == id && x["Status"] != null && x["Status"].ToString().ToLower() == "active");

Answer 1

TryGetList Will returns the SPList object. If the list exists in the corresponding web, will return the SPList else will return null.

You can directly get the ListItem by using ID. Here you don't want to use the Linq filter. Similarly you can use the Caml Query.

Get List Item By ID:

SPList list = web.Lists.TryGetList(<LIST NAME>);
SPListItem item = list.GetItemById(id);

Get List Item by Caml Query:

  SPList list = web.Lists.TryGetList(<LIST NAME>);
  SPListItemCollection itemsCol=list.GetItems(new SPQuery(){Query= "<Where><And><Eq><FieldRef Name='ID'/><Value Type='Counter'>"+id+"</Value></Eq><Eq><FieldRef Name='Status'/><Value Type='Text'>Active</Value></Eq></And></Where>"});
  if(itemsCol!=null && itemsCol.count>0)
  {
    SPListItem item =itemsCol.FirstOrDefault();
  }

Here you can download the Caml Builder.

Answer 2

The SPList object returned by TryGetList is an object that already has all of the metadata surrounding that list, but it doesn't have any of the data for the items in the list. You can, for example, determine the name of the list, or when it was created, from the SPList object, it won't need to do all of the work of querying the entire set of list items.

When you access the Items property on the SPList and iterate it (as you're doing with First) you are getting all of the items in the list though. The Items property is not an IQueryable<T>, any LINQ operations you're using on it are acting on the entire collection pulled into memory. You obviously don't want to do that just to get an item by it's ID. To get just that one item, use GetItemByID.