CSOM CAML displays all fields

Question

I would like to reduce the no of fields that I get from CAML query. The List has more than 100 fields, which is causing a lot of performance issues.

In SSOM the SPQuery object has a query.ViewFieldsOnly = true;. Currently I am querying SharePoint online using CSOM i.e. using c#. I do not get the ViewFieldsonly property.

Following is the code I use to get the ListItem

ClientContext context = new ClientContext("http://SiteUrl");
ListCollection lcoll = context.Web.Lists;
context.Load(lcoll);
context.ExecuteQuery();
lst = lcoll.Where(x => x.Title == ListName).FirstOrDefault();
CamlQuery cQuery = new CamlQuery();
cQuery.ViewXml ="<View>"+
                  "<Query>"+
                    "<Where><Eq>"+
                     "<FieldRef Name='MyName'/>"
                     "<Value Type='Text'>John"+
                     "Ranger</Value>"+
                    "</Eq></Where>"+
                   "</Query>"+
                 "<ViewFields>"+
                 "<FieldRef Name='MyName'/>"+
                 "<FieldRef Name='Title' />"+
                 "<FieldRef Name='ID' />"+
               "</ViewFields></View>";
ListItemCollection licoll = lst .GetItems(cQuery);
context.Load(licoll);
context.ExecuteQuery();
ListItem item = licoll.Where(i => i[MyName].ToString()== email).FirstOrDefault();
context.Load(item);
context.ExecuteQuery();

Using the CAML query gives me one list item but with 179 fields. I only want the three fields that I have specified in the CAML query.

Answer 1

It is weird but ViewFields in CamlQuery is kind of ignored. You have to use eager loading using Include().

using (ClientContext cc = new ClientContext("https://sharepoint.domain.com"))
{
    // Load server-relative url
    cc.Load(cc.Web, x => x.ServerRelativeUrl);
    cc.ExecuteQuery();

    // Get list by url
    List list = cc.Web.GetList($"{cc.Web.ServerRelativeUrl}/Lists/SomeList");

    CamlQuery query = new CamlQuery();
    query.ViewXml = @"<View>
                        <Query>
                          <Where>
                            <Eq>
                              <FieldRef Name='MyName'/>
                              <Value Type='Text'>John Ranger</Value>
                            </Eq>
                          </Where>
                        </Query>
                      </View>";

    ListItemCollection items = list.GetItems(query);
    cc.Load(items, 
        i => i.Include(
            p => p.Id,
            p => p["Title"],
            p => p["MyName"] // You don't really need to load MyName because it is in the filter
        ));
    cc.ExecuteQuery();

    // Object 'items' now contains all items with 'John Rander' in column 'MyName'
    // Only Id, Title and MyName properties are loaded for each item
}

Some notes to your code

Following code can be omitted because licoll already contains list of all items so no need to load item again with Load() and ExecuteQuery().

ListItem item = licoll.Where(i => i[MyName].ToString()== email).FirstOrDefault();
context.Load(item);
context.ExecuteQuery();

Don't forget to dispose context with context.Dispose() or use using () {} as is used in this answer. Using statement is safer because the object is disposed even when some exception is thrown.

Answer 2

You see 179 fields in the list item, because the code below, it means you do a query to load item again, as Lukas's reply, we don't need use it.

context.Load(item);
context.ExecuteQuery();

Using ViewFields element in CAML query, we can limit a lot of fields return, unfortunately, it still some system columns return. If you only want to return "MyName","Id" and "Title" fields, we need using LINQ query.

The code below for your reference.

var ListName="Test0107";
string siteUrl = "https://tenant.sharepoint.com/sites/lz";
var login = "lz@tenant.onmicrosoft.com";
var password = "xx";
using (ClientContext context = new ClientContext(siteUrl))
{
    var securePassword = new SecureString();
    foreach (char c in password.ToCharArray()) securePassword.AppendChar(c);
    context.Credentials = new SharePointOnlineCredentials(login, securePassword);
    List lst = context.Web.Lists.GetByTitle(ListName);

    CamlQuery cQuery = new CamlQuery();
    cQuery.ViewXml = "<View>" +
                        "<Query>" +
                        "<Where><Eq>" +
                            "<FieldRef Name='MyName'/>" +
                            "<Value Type='Text'>John Ranger</Value>" +
                        "</Eq></Where>" +
                        "</Query>" +
                        "<ViewFields>" +
                        "<FieldRef Name='MyName'/>" +
                        "<FieldRef Name='Title' />" +
                        "<FieldRef Name='ID' />" +
                    "</ViewFields></View>";
    ListItemCollection licoll = lst.GetItems(cQuery);
    context.Load(licoll, items => items.Include(i => i.Id, i => i["MyName"], i => i["Title"]));               
    context.ExecuteQuery();
    ListItem item = licoll.FirstOrDefault();

}