Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I'm trying to use values from 2 SPListItemCollections, as one of them have a lookup field with values from the other list.

The reason why I need to do this, is because I need the value from the other list in order to OrderBy correctly when populating the values.

This is what I have so far.

SPWeb web = SPContext.Current.Site.RootWeb;
SPList list1 = web.Lists["List1"];
SPList list2 = web.Lists["List2"];

foreach (var l1 in list1.Items.Cast<SPListItem>().OrderBy(l1 => l1["Category"]).ThenBy(l1 => l1["Position"]))
{
    /* Logic */
}

I want l1["Category"] to be replaced by a column name from list2.
How can I achieve this?

I'm not that experienced with "advanced" coding.

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

What you want to do is look into CAML field joins and projections

As the name suggests, it allows you to join two lists linked by a lookup field, and bring up the values through a single CAML query. The page linked above is not a beacon of clarity but you can find good examples on Google.

Using CAML will also enable you to put your Order by in it. A CAML query will also be more efficient than your current list1.Items.Cast<..>( )

Note also that using

web.Lists["List1"];

can be slow if you have many lists on that web. Consider using something like

web.GetList(SPUtility.ConcatUrls(web.ServerRelativeUrl, "MyListUrl"));
share|improve this answer
add comment

You can achieve this by Join and Projected fields. Please check the section 3. List Joins here : http://extreme-sharepoint.com/2011/10/19/rdbms-features-sharepoint-2010/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.