Adding lookup column to list using CSOM

Question

I have a list called "Books" with columns 'Name','AuthorName','ISBN' with type as text. Now I have another list called "BillTokenStore" and i want to add lookup column 'AuthorName' in "BillTokenStore". Below is what i have done.

using (ClientContext context = new ClientContext(webFullUrl: siteUrl))
            {
                context.Credentials = new SharePointOnlineCredentials(userName, GetPassWord());

                Web web = context.Web;

                ListCollection listCollection = web.Lists;
                List list = listCollection.GetByTitle("BillTokenStore");


                string schemaLookupField = @"<Field Type='Lookup' Name='InStock' StaticName='InStock' DisplayName='InStock' List = 'Books' ShowField = 'Title' /> ";
                Field lookupField = list.Fields.AddFieldAsXml(schemaLookupField, true, AddFieldOptions.DefaultValue);

                context.ExecuteQuery();               
            }

When i run this code, i am getting the error "value does not fall within the expected range sharepoint 2013". What is wrong here? Thanks in Advance.

Note: I am able to achieve the same thing thorough UI. I am also able to add other type of fields like choice,boolean and all through code.

Answer 1

You need to explicitly load the list and the fields of that list.

Also, we need to pass the GUID of the lookup column list.

Please try the below modified code:

using (ClientContext context = new ClientContext(webFullUrl: siteUrl))
{
    context.Credentials = new SharePointOnlineCredentials(userName, GetPassWord());

    Web web = context.Web;

    List booksList = context.Web.Lists.GetByTitle("Books");

    List list = context.Web.Lists.GetByTitle("BillTokenStore"); 

    context.Load(list, l => l.Fields);
    context.Load(booksList, b => b.Id);
    context.ExecuteQuery();

    string schemaLookupField = @"<Field Type='Lookup' Name='InStock' StaticName='InStock' DisplayName='InStock' List='"+ booksList.Id +"' ShowField = 'Title' />";
    Field lookupField = list.Fields.AddFieldAsXml(schemaLookupField, true, AddFieldOptions.DefaultValue);
    lookupField.Update();

    context.Load(lookupField);
    context.ExecuteQuery();     
}