Recently, I wanted to use the List web service in SharePoint 2010 to retrieve data from a SharePoint list that has lookup columns in it. There doesn’t appear to be any documentation indicating that this would be a problem. The List web service is a new Windows Communication Foundation (WCF) service that provides strong-typing of list elements from client code. This helps make your code more readable and faster because nothing is being cast to and from Object for transport.
With strong typing comes Language Integrated Query (LINQ) access to the elements in the service. However, the List web service converts LINQ to CAML Queries internally and executes the request that way. CAML is a SharePoint specific language that used to be the preferred way to execute a query. The List web service is also lazy-loading, meaning that only what you request comes back from the service.
How Does This Work with Lookup Columns?
When it comes to Lookup columns, the List web service does not retrieve the values by default. So if you want that data, you have to explicitly tell the List web service to expand the requested rows to access any Lookup columns. Simply referencing the Lookup column will not work and instead generate a null reference exception. To request all lookup data, you use the "Expand" function. This extends the CAML query that runs on the back end to retrieve all data for the given column, and not just the unlinked data.
One thing to point out is that this call is not recursive. So if you have another Lookup column in your Lookup column, you’ll have to explicitly Expand that data as well.
Why Does SharePoint Do This?
In my opinion, it’s not just enough to know that something works a specific way, but why it works that way. The List web service doesn’t provide lookup column data by default for several reasons, but they all boil down to performance. It’s expensive to do what’s essentially a join operation on a SharePoint list to retrieve a little more data. The CAML query gets more complicated and the amount of data returned is often much larger than what the developer needs because you get all columns from the looked-up column (except for other lookup columns). Thus you get a significant performance hit for what could be a little payoff and you’re not really supposed to use SharePoint like a database to begin with.
Still, it’s good to know that the List web service isn’t hobbled by lookup columns, especially since they show up in IntelliSense.
