Get all items from Sharepoint list including lookup values using CSOM

Question

I want to retrieve all items from a Sharepoint list including the IDs and values of a lookup field. The lookup list resides on the same site/url as the queried list. Currently I'm getting a "property or field has not been initialized" error.

How to load the lookup field into the client context correctly, so that the property can be accessed?

After searching and trying different things without success, e.g. getting all related fields with list.GetRelatedFields() and loading all fields in all lists by iterating the list IDs in the collection of related fields, I need help.

I can't see the forest for the trees anymore. Any hint appreciated. Thanks in advance.

UPDATE

Two things happened. First, I've forgot to load the list object into the context too. Second, I've tried to request a property/column with faulty configuration.

The list on Sharepoint Online was duplicated from another Sharepoint site which includes the automatic duplication of lists used for lookups.

It seems that this duplication has corrupted the lookup column. I had to delete and readd the lookup column manually to the requested list.

Now, it works! :)

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using SP = Microsoft.SharePoint.Client;

    namespace Theroka {

        class Sharepoint {

            private private SP.ClientContext context;

            // credential handling, authorization, context creation, etc. ....
            
            public void getItems() {
                SP.List list = this.context.Web.Lists.GetByTitle("NameOfList");
                this.context.Load(list); // UPDATE: I've forgot to load the list too
                this.context.Load(list.Fields, c => c.Where(e => e.Hidden != true));
                this.context.ExecuteQuery();

                ListItemCollectionPosition position = null;
                do {
                    CamlQuery query = CamlQuery.CreateAllItemsQuery(5000);
                    query.ListItemCollectionPosition = position;
                    SP.ListItemCollection items = list.GetItems(query);
                    this.context.Load(items);
                    this.context.ExecuteQuery();
                    position = items.ListItemCollectionPosition;
                    foreach(SP.ListItem item in items) {
                        // this works now
                        SP.FieldLookupValue lv = (item["LookupFieldName"] as SP.FieldLookupValue);
                        Console.WriteLine("{0}\t{1}\t{2}", item.Id, item["Title"], lv.LookupValue.ToString());
                    }
                } while (position != null);
            }

        }
    }

Kind regards,

theroka

Adam Adam · Accepted Answer · 2020-07-18T23:06:42

You could try to load all fields of list items as text to the context and the just display them, I think this is the easiest way but maybe not the most effective one. The lookup columns should also be visible as text value (not id) in this case.

to include field values as text You need to load it to the context together with loading items. So for Your example it would be something like

this.context.Load(items, items => items.Include(item => item.FieldValuesAsText));

after that You should be able to get the value of the lookup column like:

item.FieldValuesAsText["LookupFieldName"]

I hope this will be of any help

Get all items from Sharepoint list including lookup values using CSOM

1 Answers