SPQueryThrottledException when doing file.Item

Question

So I have a document library with sub folders in SP2010

"FI\Supporting Docs\F1234" & "FI\Supporting Docs\F1235"

the F123 is created for each incident we deal with. There are currently 5000 + items in total

We have a page that uses the sharepoint multi file upload (linked to clients version of office). The user uploads the files and when we get back we set a bunch of metadata.

So I navigate to the folder and get a list of all the files. This works.

When I try to get the .Item it comes back with a throttle exception. When I looked into it I did see an exception indicating that SPFile.Item does a .items[X]

Has anyone got an ideas of what I can do??? I want to get the ID of the listitem and then I can do a GetItemByID, but cant see how to get it from an SPFile object

public static List<SPFile> GetSupportingDocFiles(SPList list, string cadNumber)
        {
            var supportDocsfolder = list.ParentWeb.GetFolder(string.Format("{0}\\{1}", list.Title, CONSTANTS.FOLDERS.SUPPORTING_DOCS));
            //var supportDocsfolder = list.Folders.Cast<SPListItem>().FirstOrDefault(f => f.Name == CONSTANTS.FOLDERS.SUPPORTING_DOCS);
            if (supportDocsfolder == null || supportDocsfolder.Exists == false)
                throw new Exception(string.Format("{0} folder is missing", CONSTANTS.FOLDERS.SUPPORTING_DOCS));

            //var folderItem = list.Folders.Cast<SPListItem>().FirstOrDefault(f => f.Name == cadNumber);
            var cadFolder = list.ParentWeb.GetFolder(string.Format("{0}\\{1}\\{2}", list.Title, CONSTANTS.FOLDERS.SUPPORTING_DOCS, cadNumber));
            if (cadFolder == null || cadFolder.Exists == false)
                throw new Exception(string.Format("{0} folder is missing", cadNumber));

            var files = cadFolder.Files.Cast<SPFile>().ToList();
            return files;
        }

        public static void SetSupportingDocMetaData(int taskId, string cadNumber, int? buildingAddressId, SPList list)
        {


            var files = GetSupportingDocFiles(list, cadNumber);

            foreach (SPFile file in files)
            {



                //file.GetListItem()
                //SPListItem item = list.ParentWeb.GetListItem(file.Url);

                SPListItem item = file.Item; // ***** SPQueryThrottledException  here

                if (item[CONSTANTS.SC.FIRE_INVESIGATION.CAD_NUMBER].ToValueOrEmpty() != cadNumber
                    || (buildingAddressId.HasValue == true && item[CONSTANTS.SC.COMMON.BUILDING_ADDRESS_BUILDING_ADDRESS_ID].ToValueOrEmpty() != buildingAddressId.Value.ToString())
                    || item[CONSTANTS.SC.FIRE_INVESIGATION.SMSFITASKID].ToValueOrEmpty() != taskId.ToString())
                {
                    if (file.Exists == true && file.CheckOutType == SPFile.SPCheckOutType.None)
                        file.CheckOut();
                    if (buildingAddressId.HasValue)
                        BcsItem.SetBcsColumn(CONSTANTS.BCS_TYPES.BUILDING_ADDRESS, list, item, CONSTANTS.SC.COMMON.BUILDING_ADDRESS, buildingAddressId.Value);
                    item[CONSTANTS.SC.FIRE_INVESIGATION.CAD_NUMBER] = cadNumber;
                    item[CONSTANTS.SC.FIRE_INVESIGATION.SMSFITASKID] = taskId;
                    item.Update();

                    file.CheckIn("Update metadata");
                }

            }
        }

Answer 1

To get the file simply use item.File this will return a SPFile object.

If you are trying to get specific files you should always use SPQuery and write a CAML Query to get the items. When you execute such query the Throttle mode of the query can also be set using set with the QueryThrottleMode property. This allows you the return of more objects than SharePoint normal item return count is. To get more the 2000 items at once have always impact on the performance.

A general guideline: Forget that list.Items exists to return all items. This will only work with small lists. When they get bigger and bigger you might fail to get a result or as in your case get exceptions by throttles, like the maximum count of items that will be returned by SharePoint.

You will also find some background information in the following blog post: Working with large lists in SharePoint 2010

Answer 2

I'd turn your logic around. Instead of getting all the files in a folder and loop through to see if their corresponding listitem match some criteria (wrong Cadnumber, addressId or taskid) you should:

• Create a CAML query looking in the specified folder for items matching those criteria (turning off throttle mode)
• Loop through these hopefully few