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. :)