Multiple SPListItemCollection Query With SPFieldLookupValue

While SPList objects do not sustain true relational integrity due to the inherent nature of unstructured SharePoint data storage (in the current version at least), it is common within business applications to use Lookup field types are leveraged to build weak references between SharePoint lists. As such, typed collection queries become important, combining LINQ with method chaining allows us to write succinct SPListItem returns.

Firstly, let’s make some requisite test proxy objects:

SPWeb curWeb = SPContext.Current.Web;
SPList list1 = curWeb.Lists[“My First List”];
SPList list2 = curWeb.Lists[“My Second List”];
Now, let’s assume the following business requirement. I want to loop through list1, and match a returned SPField value to a string condition defined by a literal. Subsequently, the return will be used on the list2 SPList object within the Where clause. This is considered in the following code snippet:

var resultSet  = list1.Items.Cast()
.Where(i => Equals (String.Compare(i[“Property To Match #1”].ToString(), “Example String Literal”), 0))
.SelectMany(x => list2.Items.Cast()
.Where(i => Equals(String.Compare(new SPFieldLookupValue(x[“Client”].ToString()).LookupValue, (string) i[“Property To Match #2”]), 0)));
The above code is arguable that the second casting operation for the typed collection build should be broken out into a separate variable before the call and replacing the Cast statement within SelectMany with the result of the call using ToList to forces immediate execution. Meh. :)



  1. Kirk C. says:

    Excellent! Much more succinct code then foreach loops over SPListItems.

  2. Matt T. says:

    Pretty reasonable, but debugging with it is difficult without excessive use of the immediate window in VS.NET.

  3. James Fortner says:

    This is pretty good, but debugging it is difficult to look at the collection building at run-time. I might use more local variables…but the end result would be the same…


  1. Links (4/30/2009) « Steve Pietrek - Everything SharePoint and Office - [...] Multiple SPListItemCollection Query With SPFieldLookupValue [...]
  2. Join SharePoint Lists with LINQ « Solutionizing .NET - [...] Join SharePoint Lists with LINQ April 30, 2009 — Keith Dahlby I just read yet another post by…

Leave a Reply

Your email address will not be published. Required fields are marked *