Situation:
I have a SharePoint Foundation 2010 environment with a document library. One of the metadata on the documents is a multivalue lookup to a category list.
I used SPMetal to create entity classes for easy usage of linq to the different lists and libraries inside the SharePoint site.
Problem:
Inside a visual web part, I was trying to execute a linq query on the document library, filtering on a given category.
Example code:
QualityDataContext dc = new QualityDataContext(SPContext.Current.Web.Url);
// some other steps made
var mySelectedCat = dc.MyCategories.OrderBy(p => p.Title).Where(p => p.id == "CategoryOfInterestID")
var docs = dc.MyDocLib.OrderBy(p => p.Title).Where(p => p.Categories.Contains(mySelectedCat));
This goes wrong because in this situation linq is messing up with the
"p.Categories" en the entity "mySelectedCat"
Solution:
I have searched a lot, without getting a right, easy solution. Some mentioned to use the 'old' "SPWeb, SPList, SPQuery and so on"-way. Others where trying to believe you that the sample above is working.
Sorry folks, it's not. The problem is that querying on the multivalue lookup just works after the data has been retrieved. So I uses the ToList() method to the 'docs' part, just before filtering. For performance reasons, I first get the whole list (I just needed the whole list) and then do the filtering on the moment I needed it.
So this results in the following piece of code that I am using to fill a tree view, grouped on main category and sub category with links to documents.
Note the 'ToList()' I am using, twice just straight in the beginning of the using section. This is doing the trick.
using (QualityDataContext dc = new QualityDataContext(SPContext.Current.Web.Url))
{
var cats = dc.MyCategories.OrderBy(p => p.Title).ToList();
var docs = dc.MyDocuments.OrderBy(p => p.Title).ToList();
SPTreeView tv = new SPTreeView();
var mainCat = cats.OrderBy(p => p.Category).GroupBy(p => p.Category);
// Step through the main categories
foreach (var item in mainCat)
{
TreeNode catNode = new TreeNode{ Text = item.Key, Value = item.Key };
var subCats = cats.OrderBy(p => p.Title).Where(p => p.Category == item.Key);
// Step through the sub categories
foreach (var subCat in subCats)
{
TreeNode subCatnode = new TreeNode { Text = subCat.Title, Value = subCat.Id.ToString() };
var subdocs = docs.Where(p => p.Categories.Contains(subCat));
if (subdocs.Count() > 0)
{
foreach (var doc in subdocs)
{
string docUrl = doc.Path + "/" + doc.Naam;
subCatnode.ChildNodes.Add(new TreeNode { Text = doc.Naam, ToolTip = doc.Title, NavigateUrl = docUrl, Target = "_blank" });
}
subCatnode.Collapse();
catNode.ChildNodes.Add(subCatnode);
}
}
tv.Nodes.Add(catNode);
}
this.Controls.Add(tv);
