Extract only the values from SharePoint list with C#

Question

I'm trying to extract the values of a SharePoint list to use with Revit to update the status parameters of some elements and after many tries I can connect and get the values if I know the keys for the dictionary inside every ListItem, but there are many problems with this approach. The first one is the need to know the keys, sometimes the key is changed because of encoding, It would be more productive for me to get all the list values at one time. I tried to use a GetDataTable like some tutorials, but it appears that this don't work with the client. The second is sometimes I can't get the value of the List but a description of the value, like "Microsoft.SharePoint.Client.FieldLookupValue".

Can someone help me with this issue? Bellow is the code I'm using.

            using Microsoft.SharePoint.Client;
            using System;
            using System.Security;

            namespace ConsoleTESTES
            {
                class Program
                {
                    static void Main(string[] args)
                    {

                        string username = "USERNAME";
                        string siteURL = "SITEURL";
                        SecureString password = GetPassword();
                        GetAllWebProperties(siteURL, username, password);
                    }

                    public static void GetAllWebProperties(string siteURL, string username, SecureString password)
                    {
                        using (var context = new ClientContext(siteURL))
                        {
                            context.Credentials = new SharePointOnlineCredentials(username, password);
                            Web web = context.Web;
                            context.Load(web);
                            context.ExecuteQuery();
                            Console.WriteLine("Title: " + web.Title + "; URL: " + web.Url);

                            // Assume the web has a list named "Announcements".
                            //List lista = context.Web.Lists.GetByTitle("Lista teste");
                            List lista = context.Web.Lists.GetByTitle("LIST");

                            // This creates a CamlQuery that has a RowLimit of 100, and also specifies Scope="RecursiveAll"
                            // so that it grabs all list items, regardless of the folder they are in.
                            CamlQuery query = CamlQuery.CreateAllItemsQuery();
                            ListItemCollection items = lista.GetItems(query);

                            // Retrieve all items in the ListItemCollection from List.GetItems(Query).
                            context.Load(items);
                            context.ExecuteQuery();

                            //GET VALUES FROM LISTITEM
                            foreach (ListItem listItem in items)
                            {
                                Console.WriteLine(listItem["Setor"] + "   " + "|" + "   "
                                    + listItem["LocalServico"] + "   " + "|" + "   "
                                    + listItem["Equipe"] + "   " + "|" + "   "
                                    + listItem["Confeccao"]);
                            }

                            Console.ReadLine();
                        }
                    }
                    public static SecureString GetPassword()
                    {
                        ConsoleKeyInfo info;
                        SecureString securePassword = new SecureString();
                        do
                        {
                            info = Console.ReadKey();
                            if (info.Key != ConsoleKey.Enter)
                            {
                                securePassword.AppendChar(info.KeyChar);
                            }
                        }

                        while (info.Key != ConsoleKey.Enter);
                        return securePassword;

                    }
                }
            }

Answer 1

You could get the values from the fields collection, but be warned that some special types of fields might require special treatment for the values and that you might not need to get all the values from the server (you can probably reduce your payload):

var items = lista.GetItems(query);
var fields = list.Fields;

var fieldsToIgnore = new[] { "ContentType", "Attachments" };

context.Load(items);
context.Load(fields);
context.ExecuteQuery();

foreach (ListItem listItem in items)
{
    foreach (Field field in fields)
    {
         if (!fieldsToIgnore.Contains(fld.InternalName))
             Console.WriteLine(item[field.InternalName]);
    }
}

There are some fields that might not be loaded by default of that you might not need, so I have included the fieldsToIgnore to make your test easier.

Answer 2

After some search I found this solution to my FieldLookUpTable, to avoid errors if the item is null I added a if statement, but I could access the value with (listItem["Setor"] as FieldLookupValue).LookupValue. Here my messy code to check if is a LookupValue and get the value. Now I need to implement Pedro's solution to get all the values without the need to write everyone.

                String setor = "";
                String localServico = "";
                String confeccao = "";

                if (listItem["Setor"] != null && listItem["Setor"].ToString() == "Microsoft.SharePoint.Client.FieldLookupValue")
                {
                    setor = (listItem["Setor"] as FieldLookupValue).LookupValue;
                }
                else if (listItem["Setor"] != null && listItem["Setor"].ToString() != "Microsoft.SharePoint.Client.FieldLookupValue")
                {
                    setor = listItem["Setor"].ToString();
                }

                if (listItem["LocalServico"] != null && listItem["LocalServico"].ToString() == "Microsoft.SharePoint.Client.FieldLookupValue")
                {
                    localServico = (listItem["LocalServico"] as FieldLookupValue).LookupValue;
                }
                else if (listItem["LocalServico"] != null && listItem["LocalServico"].ToString() != "Microsoft.SharePoint.Client.FieldLookupValue")
                {
                    localServico = listItem["LocalServico"].ToString();
                }

                if (listItem["Confeccao"] != null && listItem["Confeccao"].ToString() == "Microsoft.SharePoint.Client.FieldLookupValue")
                {
                    confeccao = (listItem["Confeccao"] as FieldLookupValue).LookupValue;
                }
                else if (listItem["Confeccao"] != null && listItem["Confeccao"].ToString() != "Microsoft.SharePoint.Client.FieldLookupValue")
                {
                    confeccao = listItem["Confeccao"].ToString();
                }


                Console.WriteLine(setor + "   " + "|" + "   "
                    + localServico + "   " + "|" + "   "
                    + confeccao);