1

I have been tasked with downloading an excel file from my companies internal sharepoint site. Note that I do not work on the team that owns the site and dont have direct access to the server. I have been working a bit with one of the site admins and was able to develop some code that works for all intents and purposes. The problem is, its ugly. Really ugly. Here it is with important info redacted.

using (ClientContext context = new ClientContext(BASE_URL))
            {
                var queryExpression = from list
                                    in context.Web.Lists.Include(list => list.RootFolder,
                                        list => list.RootFolder.Folders,
                                        list => list.RootFolder.Folders.Include(
                                            rf => rf.Folders,
                                            rf => rf.Folders.Include(
                                                f => f.Folders,
                                                f => f.Files)
                                            )
                                        )
                                      where list.Title == "MAIN LIST NAME"
                                      select list;

                IEnumerable<List> resultCollection = context.LoadQuery(queryExpression);
                context.ExecuteQuery();

                foreach (var folderFromRoot in resultCollection.First().RootFolder.Folders)
                {
                    if (folderFromRoot.Name == "FIRST FOLDER NAME")
                    {
                        foreach (var folder in folderFromRoot.Folders)
                        {
                            if (folder.Name == "SECOND FOLDER NAME")
                            {
                                var newestWriteDate = folder.GetLatestWriteDate();

                                //find the file using the latest write time, then do the download
                                foreach (var folderFile in folder.Files)
                                {
                                    if (folderFile.TimeLastModified.Equals(newestWriteDate))
                                    {
                                        FileInformation fi = Microsoft.SharePoint.Client.File.OpenBinaryDirect(context, folderFile.ServerRelativeUrl);

                                        using (FileStream fs = new FileStream(GetDownloadedFileName(), FileMode.Create))
                                        {
                                            fi.Stream.CopyTo(fs);
                                        }

                                        break;
                                    }
                                }

                                break;
                            }
                        }

                        break;
                    }
                }
            }

As you can see, the nested foreach loops and if statements make this very ugly to read. I tried condensing things by using LINQ statements to filter the resultCollection but I always get this exception:

Message "Invalid usage of query execution. The query should be executed by using ExecuteQuery method on the client context object."

I understand what it means, but Im unsure how I could alter the query expression I currently have to drill down to exact folder with the files in it. Is there a better approach for what Im doing?

2

Disclaimer: I have never really worked with the CSOM, so you should consider what I'm going to write as "pseudocode". But since you wanted a code review (and you're right, that's not great), I have some possible suggestions.

You're right, that's probably not the best way to go about doing what you are trying to do. The giveaways to me that it could be done much more efficiently are that you apparently know what the document library name is ("MAIN LIST NAME"), and you apparently also know exactly what folder the file is in, because you loop through the folders until you find some with specific names ('FIRST FOLDER NAME", "SECOND FOLDER NAME").

If you know the folder that the file is going to be in already, why not just load that folder specifically using web.GetFolderByServerRelativeUrl, then load the files and loop through them. I would have suggested targeting the file directly using web.GetFileByServerRelativeUrl except you seem to be looking for the newest file, not a specific one by name.

So in "pseudocode" (although attempting to get the CSOM syntax as right as I can), you would do

var folderUrl = "/sites/subsite/Lists/MAIN LIST NAME/FIRST FOLDER NAME/SECOND FOLDER NAME";
var folder = context.Web.GetFolderByServerRelativeUrl(folderUrl);
context.Load(folder);
context.ExecuteQuery();
var files = folder.Files;
context.Load(files);
context.ExecuteQuery();

foreach (var file in files)
{
    // do your thing
}

The main thing here is to realize that if you know exactly where something is, there's probably a more direct way to get to it that loading everything and looping through it all.

| improve this answer | |
  • This definitely seems like the better option. I was following some microsoft documentation at first and kind of built from there until it was what I posted. I would much rather just be able to hit the folder directly and get the files that way. I will keep you updated on if the implementation works. Thanks for the input! – Kyle Rone Dec 3 '19 at 22:47
  • This worked! It took me a minute to get the correct relative url for where I was going but once I did this worked just as well. Thank you for your help! – Kyle Rone Dec 4 '19 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.