CAML Query not returning results for SMLastModifiedDate field when working with Onedrive personal site.

Describe the bug

The CAML query fails to return results for the SMLastModifiedDate field when applied to an Onedrive Site. However, it functions correctly and retrieves results when applied to any SharePoint Site.

Interestingly, the query successfully retrieves results for both the OneDrive Site and SharePoint Site when using the Modified date field.

Example URLs:

Onedrive Site: https://testing-my.sharepoint.com/personal/mustafa_gaziani_admin_testing_onmicrosoft_com/Documents/test_folder SharePoint Site: https://testing.sharepoint.com/mg_general_testing/Shared Documents/test_folder

Steps to reproduce

I want to return a List Items where the SMLastModifiedDate field is greater or equals a specific datetime. When I execute my CAML in a CAML Query Builder (u2u) against my List, I receive the expected result...

However, I didn't get the records when I executed the query from code for the very same datetime stamp. This is not correct, it seems to be ignoring the CAML.

private string CreateCamlQuery(bool aRecursive, string aFolder, DateTime aSince, bool aScopeRecursive = true)
{
    string iFolderWithtSlashAtTheEnd = aFolder.EndsWith("/") ? aFolder : aFolder + "/";
    string iFolderWithOutSlashAtTheEnd = aFolder.EndsWith("/") ? aFolder.Remove(aFolder.Length - 1) : aFolder;
    string iLastModifiedDateFieldName = _SiteConfig._HasSyncFolders ? "SMLastModifiedDate" : "Modified";

    string iRetVal = $"<View Scope='{(aScopeRecursive ? "Recursive" : "FilesOnly")}'>" +
                        "<Query>" +
                            "<Where>" +
                                "<And>" +
                                    "<Geq>" +
                                        $"<FieldRef Name='{iLastModifiedDateFieldName}'/>" +
                                        $"<Value Type='DateTime' IncludeTimeValue='TRUE' StorageTZ='TRUE'>{aSince.ToString("yyyy-MM-ddTHH:mm:ssZ")}</Value>" + // IncludeTimeValue means both Date&Time are included in query. StorageTZ means query is done with UTC timestamps
                                    "</Geq>" +
                                    (aRecursive ?
                                    "<BeginsWith>" + // If recursive we search for all documents under certain directory. We use FileRef for that because we want to add '/' at the end to avoid interferences with other directories
                                         "<FieldRef Name='FileRef'/>" +
                                        $"<Value Type='Text'>{iFolderWithtSlashAtTheEnd}</Value>" +
                                    "</BeginsWith>"
                                    :
                                    "<Eq>" +        // If not recursive we search for all documents in certain directory. We use FileDirRef for that because we don't want '/' at the end
                                         "<FieldRef Name='FileDirRef'/>" +
                                        $"<Value Type='Text'>{iFolderWithOutSlashAtTheEnd}</Value>" +
                                    "</Eq>") +
                                "</And>" +
                            "</Where>" +
                        "</Query>" +
                        "<ViewFields>" +
                            "<FieldRef Name='FileRef'/>" +
                            "<FieldRef Name='Created'/>" +
                            "<FieldRef Name='File_x0020_Size'/>" +
                            $"<FieldRef Name='{iLastModifiedDateFieldName}'/>" +
                        "</ViewFields>" +
                        "<RowLimit Paged='TRUE'>5000</RowLimit>" +
                    "</View>";

    LogContext iLogContext = new LogContext(MethodBase.GetCurrentMethod().Name);
    LogHelper.Debug($"Next CAML query has been generated for inputs: Recursive: '{aRecursive}', Folder: '{aFolder}', Since: '{aSince}' => {iRetVal}", _logCtx: iLogContext);
    return iRetVal;
}

do
{
// Load the query
ClientResult<Stream> iClientResult = aDocumentList.RenderListDataAsStream(iCamlQuery, iNextHref);

// Execute query
ctx.ExecuteQuery();

// Read the stream and deserialize data into custom format
RenderListOutput iPartialResult = iClientResult.ReadStreamResponse();

iNextHref = iPartialResult.FormattedNextHref;

} while (!string.IsNullOrWhiteSpace(iNextHref));

Expected behavior

The CAML query should return results when querying data with SMLastModifiedDate

Environment details (development & target environment)

SDK version: [Microsoft.SharePoint.Client - 16.1.0.0] Tooling: [Visual Studio 2017]

@Mustafa-Gaziani : can you try if LoadItemsByCamlQuery works? Also, did you verify you're using exactly the same CAML query in the CAML query builder and in code?

@jansenbe, upon rechecking with the u2u query builder tool, I realized that the query I provided earlier is not yielding the expected results with the tool as well. It seems I may have mixed up the SMLastModifiedDate and Modified fields earlier, and I apologize for any confusion this may have caused.

The query I crafted works perfectly with SharePoint Sites, but it seems to encounter issues when applied to the Onedrive Personal Site for the SMLastModifiedDate field. The query works and returns the result with the Modified field with both the sharePoint and the onedrive.

In summary, the same query operates successfully with the Modified field but encounters issues with the SMLastModifiedDate field when working with the Onedrive Personal site.

FieldRef Name='SMLastModifiedDate' NOT WORKING

<View Scope='Recursive'>
   <Query>
      <Where>
         <And>
            <Geq>
               <FieldRef Name='SMLastModifiedDate' />
               <Value Type='DateTime' IncludeTimeValue='TRUE' StorageTZ='TRUE'>2024-04-03T10:41:56Z</Value>
            </Geq>
            <BeginsWith>
               <FieldRef Name='FileRef' />
               <Value Type='Text'>/personal/mustafa_gaziani_admin_testing_onmicrosoft_com/Documents/test_folder/</Value>
            </BeginsWith>
         </And>
      </Where>
   </Query>
   <ViewFields>
      <FieldRef Name='FileRef' />
      <FieldRef Name='Created' />
      <FieldRef Name='File_x0020_Size' />
      <FieldRef Name='SMLastModifiedDate' />
   </ViewFields>
   <RowLimit Paged='TRUE'>5000</RowLimit>
</View>

FieldRef Name='Modified' WORKING

<View Scope='Recursive'>
   <Query>
      <Where>
         <And>
            <Geq>
               <FieldRef Name='Modified' />
               <Value Type='DateTime' IncludeTimeValue='TRUE' StorageTZ='TRUE'>2024-04-03T10:41:56Z</Value>
            </Geq>
            <BeginsWith>
               <FieldRef Name='FileRef' />
               <Value Type='Text'>/personal/mustafa_gaziani_admin_testing_onmicrosoft_com/Documents/test_folder/</Value>
            </BeginsWith>
         </And>
      </Where>
   </Query>
   <ViewFields>
      <FieldRef Name='FileRef' />
      <FieldRef Name='Created' />
      <FieldRef Name='File_x0020_Size' />
      <FieldRef Name='Modified' />
   </ViewFields>
   <RowLimit Paged='TRUE'>5000</RowLimit>
</View>

Both of the above queries are executing for the Onedrive personal site, but the query with the SMLastModifiedDate condition is not returning any results, even though it should return 2 records. However, the second query with the Modified condition is returning 2 results as expected.

@Mustafa-Gaziani : thanks for the detailed research. As PnP Core SDK is simply calling the SharePoint APIs and passing along the CAML query I don't think this is an issue in PnP Core SDK, but it's rather a limitation/feature of SharePoint. Does using Modified work for you then? If not, then I would recommend opening a support case with Microsoft, from the PnP Core SDK side we can't coordinate that.

@Mustafa-Gaziani : I'm closing this one as, like mentioned above, it's not an issue in PnP Core SDK.

pnp / pnpcore