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:

PLAIN TEXT

C#:

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:

PLAIN TEXT

C#:

var resultSet = list1.Items.Cast<SPListItem>()
.Where(i => Equals (String.Compare(i["Property To Match #1"].ToString(), "Example String Literal"), 0))
.SelectMany(x => list2.Items.Cast<SPListItem>()
.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.

Article Or Research Filed Under SharePoint| SharePoint Development.

5 Comments »

[...] Multiple SPListItemCollection Query With SPFieldLookupValue [...]

Pingback by Links (4/30/2009 « Steve Pietrek - Everything SharePoint and Office) — April 30, 2009 @ 4:47 pm
[...] Join SharePoint Lists with LINQ April 30, 2009 — Keith Dahlby I just read yet another post by Adam Buenz that got me thinking, this time about querying multiple SharePoint lists. Here’s the code he came up with: [...]

Pingback by Join SharePoint Lists with LINQ « Solutionizing .NET — April 30, 2009 @ 10:03 pm
Excellent! Much more succinct code then foreach loops over SPListItems.

Comment by Kirk C. — May 5, 2009 @ 7:50 am
Pretty reasonable, but debugging with it is difficult without excessive use of the immediate window in VS.NET.

Comment by Matt T. — May 5, 2009 @ 7:57 am
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…

Comment by James Fortner — May 8, 2009 @ 7:34 am