SPQuery Based Joins In SharePoint 2010

While working with a client today on some SharePoint 2010 code, the requirement can up to do some query based joins. I hadn’t done this before in SP 2010, but it’s pretty sweet. The only way I have gotten around it in previous versions was converting the list data into data tables and then working with it in that format. Natively working with it is a whole lot less code, whole lot less complicated too. Even for permanent join type stuff, you can use projected fields which is really nice.

So, what’s it look like? Consider the following snippet:

[xml]


















[/xml]

Pretty easy!

Share

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:

[csharp]
SPWeb curWeb = SPContext.Current.Web;
SPList list1 = curWeb.Lists[“My First List”];
SPList list2 = curWeb.Lists[“My Second List”];
[/csharp]
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:

[csharp]
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)));
[/csharp]
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. :)

Share

MultipleLookupField

Description The Microsoft.SharePoint.WebControls.MultipleLookupField class inherits from the Microsoft.SharePoint.WebControls.LookupField class which is used to represent a Lookup field control. MultipleLookupField extends the concept of LookupField by allowing a field to lookup multiple values leveraging a robust selection interface.

This Post Details Content That I Have Written Regarding The SharePoint API On MSDN. The Full Article Will Have These Contents As Annotations Of The Full Article.

I Encourage You To Read The Full MSDN Class Documentation, Since Things Like Type Hierarchy Are Illustrated.

Adam Buenz – WSS MVP – Community Content From Annotations edited by Adam Buenz – MVP:

Description The Microsoft.SharePoint.WebControls.MultipleLookupField class inherits from the Microsoft.SharePoint.WebControls.LookupField class which is used to represent a Lookup field control. MultipleLookupField extends the concept of LookupField by allowing a field to lookup multiple values leveraging a robust selection interface.

Read My Complete MSDN Article: MultipleLookupField

Share