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

Question

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.

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]

Answer 1

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.