Disclaimer: I am neither an expert, nor fan of SharePoint. Simple fact is I abhore SharePoint.

Unfortunately, I was brought into a project that required a SharePoint list to be updated via an external web service. There are two ways to accomplish this:

• Use the SharePoint Client Object Model (client.svc) interface and get the rich client interface similar to writing applications that are hosted within SharePoint. This requires that the Microsoft.SharePoint.Client libraries are included or available to your application.
• Use the WCF DataServices or REST interfaces (listdata.svc) to interact with the Lists on the SharePoint site. Unlike the Client Object Model, you’ll be limited to only working with the list data doing CRUD (Create, Read, Update, Delete) operations. Note: If the list columns change, or new lists added to the site, the service reference will need to be updated since the generated code is type-safe.

For this project, we opted to go with the WCF DataServices approach since we only needed to populate and update existing list data. In Visual Studio it’s easy to add the WCF web service reference which will create the proxy for you. Basically reference your site url and prefix ‘_vti_bin/listdata.svc’ to the end of the url.

When creating a new column in SharePoint, you have the following options for the ‘type’:

• Single line of text
• Multiple lines of text
• Choice (menu to choose from)
• Number (1, 1.0, 100)
• Currency ($, ¥, €)
• Date and Time
• Lookup (information already on this site)
• Yes/No (check box)
• Person or Group
• Hyperlink or Picture
• Calculated (calculation based on other columns)
• External Data
• Managed Metadata

When using the WCF proxy, the bold items identified above require special treatment for both reading and writing the values. Let’s look at reading these values via the WCF proxy.

For this example setup, we’re going to have a ‘Products’ list with the following columns:

• Shipping: Choice (Multi-value)
• Color: Choice (single value)
• Category: Lookup (Multi-value)
• Manufacturer: Lookup (single value)

Two additional lookup lists were created called ‘Categories’ and ‘Manufacturers’. Nothing special about these lookup lists, just used the default ‘Id’ and ‘Title’ columns to store our data.

For the purpose of this demo, the SharePoint site name was ‘WCF Test’. In our Visual Studio project, the web service reference was named ‘StoreSite’.

Reading Lists

First we’ll create a little utility function to get our DataContext.

/// <summary>
/// Get the WCF data context proxy.
/// </summary>
/// <param name="url">The optional Url for the sharepoint site listdata.svc endpoint.</param>
/// <returns>The DataContext to operate on.</returns>
static WCFTestDataContext GetDataContext(string url = null)
{
  if (url == null) url = ConfigurationManager.AppSettings["SharePointSiteURL"];
      
  var context = new StoreSite.WCFTestDataContext(new Uri(ConfigurationManager.AppSettings["SharePointSiteURL"]));
  context.Credentials = CredentialCache.DefaultNetworkCredentials;
  return (context);
}

The first step is reading the results from the ‘Products’ list. Go ahead and create some sample data manually via the SharePoint site.

/// <summary>
/// Displays the products to the console.
/// </summary>
static void DisplayProducts()
{
  var context = GetDataContext();
  var products = context.Products;
  foreach (var product in products) {
    Console.WriteLine("[{0}] {1} @ {2}", product.Id, product.Title, product.Created);
    Console.WriteLine("  Manufacturer: [{0}] {1}", product.Manufacturer.Id, product.Manufacturer.Title);
    Console.WriteLine("  Color:        {0}", product.Color.Value);
    Console.WriteLine("  Category:     {0}", String.Join(",", product.Category.Select(c => String.Format("[{0}] {1}", c.Id, c.Title))));
    Console.WriteLine("  Shipping:     {0}", String.Join(",", product.Shipping.Select(s => s.Value)));
  }
}

If you run this, you’ll get a System.NullReferenceException. If you trace through the debugger, you’ll find that all of the choice and lookup columns are null and/or don’t contain any items. Essentially, SharePoint is not doing the joins on that data to avoid unnecessary data transfer and optimal query performance. We have to explicitly tell SharePoint that we want that data included in our results. At the same time, we’ll make our output code a little more robust with some null checking.

Replace the previous function with the following:

/// <summary>
/// Displays the products to the console.
/// </summary>
static void DisplayProducts()
{
  var context = GetDataContext();
  var products = context.Products.Expand(p => p.Manufacturer)
                                 .Expand(p => p.Category)
                                 .Expand(p => p.Shipping)
                                 .Expand(p => p.Color);
  foreach (var product in products) {
    if (product.Manufacturer != null) {
      Console.WriteLine("  Manufacturer: [{0}] {1}", product.Manufacturer.Id, product.Manufacturer.Title);
    }
    if (product.Color != null) {
      Console.WriteLine("  Color:        {0}", product.Color.Value);
    }
    if (product.Category != null) {
      Console.WriteLine("  Category:     {0}", String.Join(",", product.Category.Select(c => String.Format("[{0}] {1}", c.Id, c.Title))));
    }
    if (product.Shipping != null) {
      Console.WriteLine("  Shipping:     {0}", String.Join(",", product.Shipping.Select(s => s.Value)));
    }
  }
}

The key in the above code is the .Expand() function.

  var products = context.Products.Expand(p => p.Manufacturer)
                                 .Expand(p => p.Category)
                                 .Expand(p => p.Shipping)
                                 .Expand(p => p.Color);

It basically instructs SharePoint to also return that auxiliary data in the results. With that change, we should now get our expected results. For each choice and lookup field that you want included or populated, you need to include a corresponding .Expand() statement with a lambda selector.

[1] Bosch 10-in Table Saw @ 7/1/2014 11:51:35 AM
  Manufacturer: [6] Bosch
  Color:        Green
  Category:     [8] Tools,[9] Saws
  Shipping:     FedEx,UPS

Creation

For creating a new item, we’ll need to create a new ProductsItem. Once you’re ProductsItem is created, you’ll need to add it to your context which is keeping track of all changes.

var context = GetDataContext();
var product = new ProductsItem() {
  Title = "Bosch 10-in Table Saw"
};
context.AddToProducts(product);

// TODO: Assign addition properties / fields

context.saveChanges();

The above code snippet will be used for each specialized example. Note: The new product MUST be added to the context (tracked) before you can attach or link the choice and lookup columns.

Modifying Single Choice Columns

Modifying a single choice column is pretty simple. In our case, our ‘Color’ field in a single choice. Essentially we just need to set the value using the *Value attribute. Using the Visual Studio intellisense, you’ll see that ProductsItem has a ‘Color’ as well as a ‘ColorValue’ property. We can simply set the ‘ColorValue’ property.

// Color: Choice (Single)
product.ColorValue = "Green";

You can get the list of all the available choices with the following:

foreach (var color in context.ProductsColor) {
  Console.WriteLine("{0}", color.Value);
}

Note: You can set the ‘ColorValue’ string to anything. It doesn’t have to exist in the list although the native SharePoint tools and editor will likely not be happy and lose the custom value on a subsequent edit.

Modifying Multiple Choice Columns

For a multiple choice column, the ‘ProductItem’ class contains a ‘Shipping’ field of type DataServiceCollection<>. Included with this method are convenient .Add() methods. You might think that you only need to do the following:

var product = new ProductItem();
var ups = ProductsShippingValue.CreateProductsShippingValue("UPS");
product.Shipping.Add(ups);

Unfortunately, the above won’t generate an error, but neither will your data be saved. Go ahead and try it.

To save this item, we must get an existing ProductsShippingValue which is already being tracked by the context or create a new one and manually attach it to the context.

Use / Retrieve Existing Tracked Context

The following code shows how to query the list of available choices and add it to the multi choice column.

// Shipping: Choice (Multiple)
var ups = context.ProductsShipping.Where(s => s.Value == "UPS").FirstOrDefault();
var fedex = context.ProductsShipping.Where(s => s.Value == "FedEx").FirstOrDefault();
product.Shipping.Add(ups);
product.Shipping.Add(fedex);
context.AddLink(product, "Shipping", ups);
context.AddLink(product, "Shipping", fedex);

Essentially we lookup one of the available choice values which is being tracked, add it to multi-choice column and then notify the DataContext that the values are “linked”. Please note, that the above code should be made more robust by checking for null values, etc.

Create New Entity and Track It

The above example has the overhead of running a remote query. Since we’re just matching on a predetermined or known string we can instead manually create our ProductsShippingValue and accomplish the same things. The only difference is that we need to make our context start tracking our new item. We accomplish this by “attaching” it. Otherwise the code is nearly identical.

// Manufacturer: Lookup (Single)
var ups = ProductsShippingValue.CreateProductsShippingValue("UPS");
var fedex = ProductsShippingValue.CreateProductsShippingValue("FedEx");
context.AttachTo("ProductsShipping", ups);
context.AttachTo("ProductsShipping", fedex);
product.Shipping.Add(ups);
product.Shipping.Add(fedex);
context.AddLink(product, "Shipping", ups);
context.AddLink(product, "Shipping", fedex);

Either option works although I would argue that the later option, although more code, makes more sense since you’re matching and selecting based predetermined strings. An enumeration would probably be ideal for this and could be streamlined with some extension method overloads.

Modifying Single Lookup Columns

For modifying a lookup field with a single value, we simply set the appropriate ‘*Id’ value that corresponds to our lookup value. You can also dynamically lookup this value which the following example demonstrates:

// Manufacturer: Lookup (Single)
var manufacturer = context.Manufacturers.Where(m => m.Title == "Bosch").FirstOrDefault();
product.ManufacturerId = manufacturer.Id;

For brevity, null checks and other exceptions were omitted and should be included in your production code.

Modifying Multiple Lookup Columns

Setting a multiple lookup column is very similar to setting a multi-choice column value. We can either query the existing lookup value which will already be “tracked” by the DataContext or we can manually create our items.

Use / Retrieve Existing Tracked Context

We query the existing lookup value although we’re only interested and need to set the Id value.

// Category: Lookup (Multiple)
var tools = context.Categories.Where(m => m.Title == "Tools").FirstOrDefault();
var saws = context.Categories.Where(m => m.Title == "Saws").FirstOrDefault();
product.Category.Add(tools);
product.Category.Add(saws);
context.AddLink(product, "Category", tools);
context.AddLink(product, "Category", saws);

Create New Entity and Track It

We can also create our objects manually, assuming we know their Id fields. When creating the CategoriesItem, the only thing we need to set is the Id field.

// Category: Lookup (Multiple)
var tools = new CategoriesItem() { Id = 8 };
var saws = new CategoriesItem() { Id = 9 };
context.AttachTo("Categories", tools);
context.AttachTo("Categories", saws);
product.Category.Add(tools);
product.Category.Add(saws);
context.AddLink(product, "Category", tools);
context.AddLink(product, "Category", saws);

Remarks

Hopefully this helps save you time. At the time I did this, it took my several days of digging, searching and experimenting before I found the right references and ordering. Special thanks to the following post on the MSDN forums which really helped to get things going in the right direction.

And yes, SharePoint sucks…

---

Below is the complete example of adding a product with basic error checking:

class Program
{
  /// <summary>
  /// Get the WCF data context proxy.
  /// </summary>
  /// <param name="url">The optional Url for the sharepoint site listdata.svc endpoint.</param>
  /// <returns>The WCFDataContext to operate on.</returns>
  static WCFTestDataContext GetDataContext(string url = null)
  {
    if (url == null) url = ConfigurationManager.AppSettings["SharePointSiteURL"];
    
    var context = new StoreSite.WCFTestDataContext(new Uri(ConfigurationManager.AppSettings["SharePointSiteURL"]));
    context.Credentials = CredentialCache.DefaultNetworkCredentials;
    return (context);
  }

  /// <summary>
  /// Displays the products to the console.
  /// </summary>
  static void DisplayProducts()
  {
    var context = GetDataContext();
    var products = context.Products.Expand(p => p.Manufacturer)
                                   .Expand(p => p.Category)
                                   .Expand(p => p.Shipping)
                                   .Expand(p => p.Color);
    foreach (var product in products) {
      Console.WriteLine("[{0}] {1} @ {2}", product.Id, product.Title, product.Created);
      if (product.Manufacturer != null) {
        Console.WriteLine("  Manufacturer: [{0}] {1}", product.Manufacturer.Id, product.Manufacturer.Title);
      }
      if (product.Color != null) {
        Console.WriteLine("  Color:        {0}", product.Color.Value);
      }
      if (product.Category != null) {
        Console.WriteLine("  Category:     {0}", String.Join(",", product.Category.Select(c => String.Format("[{0}] {1}", c.Id, c.Title))));
      }
      if (product.Shipping != null) {
        Console.WriteLine("  Shipping:     {0}", String.Join(",", product.Shipping.Select(s => s.Value)));
      }
    }
  }

  private static void Main(string[] args)
  {
    var context = GetDataContext();
    var product = new ProductsItem() {
      Title = "Bosch 10-in Table Saw"
    };
    context.AddToProducts(product);

    // Color: Choice (Single)
    product.ColorValue = "Teale";
    foreach (var color in context.ProductsColor) {
      Console.WriteLine("{0}", color.Value);
    }

    // Shipping: Choice (Multiple)
    var ups = context.ProductsShipping.Where(s => s.Value == "UPS").FirstOrDefault();
    var fedex = context.ProductsShipping.Where(s => s.Value == "FedEx").FirstOrDefault();
    //var ups = ProductsShippingValue.CreateProductsShippingValue("UPS");
    //var fedex = ProductsShippingValue.CreateProductsShippingValue("FedEx");
    //context.AttachTo("ProductsShipping", ups);
    //context.AttachTo("ProductsShipping", fedex);
    product.Shipping.Add(ups);
    product.Shipping.Add(fedex);
    context.AddLink(product, "Shipping", ups);
    context.AddLink(product, "Shipping", fedex);

    // Manufacturer: Lookup (Single)
    var manufacturer = context.Manufacturers.Where(m => m.Title == "Bosch").FirstOrDefault();
    if (manufacturer != null) {
      product.ManufacturerId = manufacturer.Id;
    }

    // Category: Lookup (Multiple)
    var tools = new CategoriesItem() { Id = 8 };
    var saws = new CategoriesItem() { Id = 9 };
    context.AttachTo("Categories", tools);
    context.AttachTo("Categories", saws);
    //var tools = context.Categories.Where(m => m.Title == "Tools").FirstOrDefault();
    //var saws = context.Categories.Where(m => m.Title == "Saws").FirstOrDefault();
    if (tools != null) {
      product.Category.Add(tools);
      context.AddLink(product, "Category", tools);
    }
    if (saws != null) {
      product.Category.Add(saws);
      context.AddLink(product, "Category", saws);
    }

    Console.WriteLine("Adding new product '{0}'...", product.Title);
    context.SaveChanges();

    DisplayProducts();
  }
}