none
Error reading Project Custom fields via CSOM: Too many projects. RRS feed

  • Question

  • Hi!

    Starting at June 1, 2016 Project Online updated its behavior and does not allow reading project fields with single query via CSOM. Now it throws the following error: 

    Microsoft.SharePoint.Client.ServerException: Too many projects: (current count of projects). You cannot load dependent objects for more than 20 projects. Use a filter to restrict your query

    Now it is possible to load data per single project, but not 20 as the exception says. Otherwise it throws another server exception "Unknown error".

    Any Ideas how I could read data for those 20 projects as the message says?

    Thanks,

    Andrew

    Thursday, June 9, 2016 8:46 AM

Answers

  • Hello,

    This is due to a change in the CSOM API when call child objects for the project. An updated code sample can be seen on the Yammer IT Pro Network in the Project IT Pro group:

    https://www.yammer.com/itpronetwork/#/Threads/show?threadId=673325901

    I have copied the post here from Nadin in the Project Product Group at Microsoft:

    CSOM Enhancements and Limits

    Hello everyone,

    We are doing some performance and optimization changes CSOM which will greatly speed up processing time of requests that ask for child objects, for example

            var projectTasks = projContext.LoadQuery(projContext.Projects.IncludeWithDefaultProperties(p => p.Tasks, p => p.Assignments));
    projContext.ExecuteQuery();

    will load significantly faster.  In the past these queries would have timed out before all the data could be loaded and passed down the wire.    We have made these optimizations for querying child EnterpriseResource objects (StatusAssignments, StatusTasks etc.) and for child Project objects (Tasks, Assignments  etc.)

    As a consequence of this optimization, we placed some limits around the number of EnterpriseResources/Projects whose children are being requested in the same execution block in order to protect the service from very large datasets being loaded.

    What are the limits and how do they affect me?

    Limits:
    •         20 Projects where child objects are queried
    •         1000 resources  where child objects are queried

    Requesting parent objects will work the same as before:
    •         All projects will succeed
    •         All resources will succeed

    Requesting project + child entities:
    •         > 20 Projects with at least 1 child object will error out
    •         > 1000 Resources with at least 1 child object will error out

    Note: That we will be tweaking these values and will notify you of future changes.

    I get it, so do I modify my apps to take advantage?

    The following CSOM flow will still work as before:

                 //Success: Retrieve all projects
                 var projects = projContext.LoadQuery(projContext.Projects);
                 projContext.ExecuteQuery();


    The following will fail (assuming more than 20 projects  exist):

          //Fail: Retrieving tasks and assignments for too many projects (projects >20)
                 var projectTasks = projContext.LoadQuery(projContext.Projects.IncludeWithDefaultProperties(p => p.Tasks, p => p.Assignments));
                 projContext.ExecuteQuery();

    To work around the limit, we will request the list of projects we care about and then request the full project and children objects in blocks:

                 const int PROJECT_BLOCK_SIZE = 20;

                 //Query for all the projects first
                 projContext.Load(projContext.Projects, proj => proj.Include(p => p.Id));
                 projContext.ExecuteQuery();
                 var allIds = projContext.Projects.Select(p => p.Id).ToArray();

                 //get the number of blocks we will have
                 int numBlocks = allIds.Length / PROJECT_BLOCK_SIZE + 1;
                 //Query all the child objects in blocks of 20
                 for (int i = 0; i < numBlocks; i++)
                 {
                     var idBlock = allIds.Skip(i * PROJECT_BLOCK_SIZE).Take(PROJECT_BLOCK_SIZE);
                     Guid[] block = new Guid[PROJECT_BLOCK_SIZE];  //Zero'd Guid Array
                     Array.Copy(idBlock.ToArray(), block, idBlock.Count());

                     //some elements will be Zero'd guids at the end
                     var projectQuery = projContext.Projects.Where(p =>  
                         p.Id == block[0] || p.Id == block[1] ||
                         p.Id == block[2] || p.Id == block[3] ||
                         p.Id == block[4] || p.Id == block[5] ||
                         p.Id == block[6] || p.Id == block[7] ||
                         p.Id == block[8] || p.Id == block[9] ||
                         p.Id == block[10] || p.Id == block[11] ||
                         p.Id == block[12] || p.Id == block[13] ||
                         p.Id == block[14] || p.Id == block[15] ||
                         p.Id == block[16] || p.Id == block[17] ||
                         p.Id == block[18] || p.Id == block[19]
                         ).IncludeWithDefaultProperties(p => p.Tasks, p => p.Assignments);

                    //Success: The block will return the Project, Task and Assignments
                    projContext.ExecuteQuery();


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Thursday, June 9, 2016 2:31 PM
    Moderator

All replies

  • Hello,

    This is due to a change in the CSOM API when call child objects for the project. An updated code sample can be seen on the Yammer IT Pro Network in the Project IT Pro group:

    https://www.yammer.com/itpronetwork/#/Threads/show?threadId=673325901

    I have copied the post here from Nadin in the Project Product Group at Microsoft:

    CSOM Enhancements and Limits

    Hello everyone,

    We are doing some performance and optimization changes CSOM which will greatly speed up processing time of requests that ask for child objects, for example

            var projectTasks = projContext.LoadQuery(projContext.Projects.IncludeWithDefaultProperties(p => p.Tasks, p => p.Assignments));
    projContext.ExecuteQuery();

    will load significantly faster.  In the past these queries would have timed out before all the data could be loaded and passed down the wire.    We have made these optimizations for querying child EnterpriseResource objects (StatusAssignments, StatusTasks etc.) and for child Project objects (Tasks, Assignments  etc.)

    As a consequence of this optimization, we placed some limits around the number of EnterpriseResources/Projects whose children are being requested in the same execution block in order to protect the service from very large datasets being loaded.

    What are the limits and how do they affect me?

    Limits:
    •         20 Projects where child objects are queried
    •         1000 resources  where child objects are queried

    Requesting parent objects will work the same as before:
    •         All projects will succeed
    •         All resources will succeed

    Requesting project + child entities:
    •         > 20 Projects with at least 1 child object will error out
    •         > 1000 Resources with at least 1 child object will error out

    Note: That we will be tweaking these values and will notify you of future changes.

    I get it, so do I modify my apps to take advantage?

    The following CSOM flow will still work as before:

                 //Success: Retrieve all projects
                 var projects = projContext.LoadQuery(projContext.Projects);
                 projContext.ExecuteQuery();


    The following will fail (assuming more than 20 projects  exist):

          //Fail: Retrieving tasks and assignments for too many projects (projects >20)
                 var projectTasks = projContext.LoadQuery(projContext.Projects.IncludeWithDefaultProperties(p => p.Tasks, p => p.Assignments));
                 projContext.ExecuteQuery();

    To work around the limit, we will request the list of projects we care about and then request the full project and children objects in blocks:

                 const int PROJECT_BLOCK_SIZE = 20;

                 //Query for all the projects first
                 projContext.Load(projContext.Projects, proj => proj.Include(p => p.Id));
                 projContext.ExecuteQuery();
                 var allIds = projContext.Projects.Select(p => p.Id).ToArray();

                 //get the number of blocks we will have
                 int numBlocks = allIds.Length / PROJECT_BLOCK_SIZE + 1;
                 //Query all the child objects in blocks of 20
                 for (int i = 0; i < numBlocks; i++)
                 {
                     var idBlock = allIds.Skip(i * PROJECT_BLOCK_SIZE).Take(PROJECT_BLOCK_SIZE);
                     Guid[] block = new Guid[PROJECT_BLOCK_SIZE];  //Zero'd Guid Array
                     Array.Copy(idBlock.ToArray(), block, idBlock.Count());

                     //some elements will be Zero'd guids at the end
                     var projectQuery = projContext.Projects.Where(p =>  
                         p.Id == block[0] || p.Id == block[1] ||
                         p.Id == block[2] || p.Id == block[3] ||
                         p.Id == block[4] || p.Id == block[5] ||
                         p.Id == block[6] || p.Id == block[7] ||
                         p.Id == block[8] || p.Id == block[9] ||
                         p.Id == block[10] || p.Id == block[11] ||
                         p.Id == block[12] || p.Id == block[13] ||
                         p.Id == block[14] || p.Id == block[15] ||
                         p.Id == block[16] || p.Id == block[17] ||
                         p.Id == block[18] || p.Id == block[19]
                         ).IncludeWithDefaultProperties(p => p.Tasks, p => p.Assignments);

                    //Success: The block will return the Project, Task and Assignments
                    projContext.ExecuteQuery();


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Thursday, June 9, 2016 2:31 PM
    Moderator
  • Thank you so much, Paul, for your  reply!

    I'll give the code sample a try asap

    PS: the code does not look that elegant, the filter part 1-20 I mean. They say "That we will be tweaking these values and will notify you of future changes.". I wonder how is will look like when 100 items is supported :)

    Regards,

    Andrew

    Friday, June 10, 2016 9:54 PM
  • Does anyone have a powershell example for filtering status assignments.  I'm getting the same error.  Can anyone give me an example of 

    $statusAssignment = $timephase.Assignments (I need a filter on Name here) I am only looking for specific named assignments.  I have also tried to use the specific assignment guid which I have for all assignments I need to update but it still wants to load all status assignments for the resource.  Is there a way to only load the specific status assignment record that I need to update?
    $projContext.Load($statusAssignment)
    $projContext.ExecuteQuery()

    or Paul might you have a powershell example of the code you outlined above for projects or assignments?

    This is the error

     Exception calling "ExecuteQuery" with "0" argument(s): "Too many resources: 5996. You cannot load dependent objects for more than 1000 resources. Use a filter to

    restrict your query."

    At line:3 char:7

    +       $projContext.ExecuteQuery()

    Thanks for your help


    Don Landry





    • Edited by Don Landry Tuesday, October 17, 2017 12:59 PM
    Monday, October 16, 2017 10:40 PM
  • Does anyone monitor these threads?  This is very frustrating that I should be able to get a status assignment record using the getbyguid method but it returns no data even though I know (for a resource with less than 1000 assignments) the value and data is in the database.  I've also tried the rest service GET <a href="http:///<site>/_api/ProjectServer/EnterpriseResources('resourceid')/Assignments/getByGuid(uid">http://<sitecollection>/<site>/_api/ProjectServer/EnterpriseResources('resourceid')/Assignments/getByGuid(uid) which throws an error as well.  I've also seen this assignment question on other posts since 2015 and not answered.  Using this filtering method is unnecessary if you have the actual guid.  Can anyone provide some help? Thanks

    Don Landry

    Wednesday, October 18, 2017 11:07 PM
  • Update

    Hi to anyone following this thread.  I found that this works for any resource that has less than 1000 assignments.

          foreach($assignment in $assignInputFile)
          { 
           $assResource = $projContext.EnterpriseResources.GetByGuid($assignment.ResourceId)
           $projContext.Load($assResource)
           $projContext.ExecuteQuery()
          ##################################################################################################
    # THIS WORKS TO GET THE STATUS ASSIGNMENT TASK FOR ANYONE WHO HAS LESS THAN 1000 Assignments       ##################################################################################################
           $getMyAssignments = $projContext.Load($assResource.Assignments.GetById($assignment.AssignmentId))
           $projContext.ExecuteQuery()
           $val = $assResource.Assignments.GetById($assignment.AssignmentId)
           Write-Host "Here is the name $($val.Name)"
           
          }

    It returns the status assignment name of every assignment guid you are searching for.  If I put in a resource that has more than a 1000 assignments it bombs with the stupid filter error even though I've got the guid.  Can anyone give me some idea on resolving the filter issue?

    Thanks

    Don


    Don Landry


    • Edited by Don Landry Friday, October 20, 2017 7:40 PM
    Friday, October 20, 2017 11:06 AM
  • Hi

    Is this thread steal being followed. I have same issue while accessing the Project Enterprise Custom Fields. My code is as below. In the below code, I am trying to get the projects of current user only.

    LoadAssignments=function()

    {

                    try

                    {

                                    currentUserId=_spPageContextInfo.userId;

                                    projContext = PS.ProjectContext.get_current(); 

                                    enterpriseResource = PS.EnterpriseResource.getSelf(projContext);

                                    assignments = enterpriseResource.get_assignments();

                                    allProjects = projContext.get_projects();

                                    projContext.load(allProjects, 'Include(Id, Name, CustomFields, IncludeCustomFields, FieldValues)');

                                    projContext.load(enterpriseResource);

                                    projContext.load(assignments,'Include(Resource,Name,Start,Finish,Id,PercentComplete,Project)');

                                    projContext.executeQueryAsync(IterateThroughAssignments, errorCallback);

                    }

                    catch (error)

                    {

                       console.log(error);

                    }

    }

                                   

    IterateThroughAssignments=function()

    {

                    try

                    {             

                                    var Projenumerator = allProjects.getEnumerator();

                                    while (Projenumerator.moveNext())

                                    {

                                                    var projectInfo = Projenumerator.get_current();

                                                    var fieldValues = projectInfo.get_includeCustomFields().get_fieldValues();

                                                    var CFenumerator = projectInfo.get_customFields().getEnumerator();

                                                    while (CFenumerator.moveNext())

                                                    {

                                                                    var customField = CFenumerator.get_current();

                                                                    if(customField.get_name()=="Project Status" && fieldValues[customField.get_internalName()][0]!="Entry_be9a2b7d8a89e71180cf00155d3caf0d")

                                                                    {

                                                                                    allPWAProjects.push(projectInfo.get_name());

                                                                    }

                                                    }

                                    }

                    }

                    catch (error)

                    {

                       console.log(error);

                    }

    }

    When I have less than 20 projects in all, this code works well but when the count of project exceeds 20, I get error in ExecuteQuery  statement when I have Included custom fields. If I do not include custom fields and query out of the box fields like start date, Name, Finish Date, etc. my code works correctly.

    Would appreciate a help on this.

    Tuesday, January 9, 2018 7:42 AM