I am in the processing of learning how to use SPMetal to replace those dreaded SPQuery CAML queries. So far, very nice!

If you need more information on using SPMetal to add LINQ to SharePoint to your SharePoint development environment, check out my blog entitled Using SPMetal to Generate SharePoint 2013 LINQ Classes. (This is also relevant to SharePoint 2010)

One scenario I ran into was needing to filter my results on a multi-value lookup column.

In this example, I have two custom lists: Clients and Services. A client may be associated with one or more services, so I created a multi-value lookup column on the Clients list. Now I want to retrieve all Clients which are associated with a Service entitled “Applications”.

One thing I need to do in my LINQ query is be sure to for immediate query evaluation using a .ToList() statement. This will prevent me from getting an “Unsupported Elements, References to More than One List” error. For more information on this, please check out my blog entitled SPMetal List Joins “Unsupported Elements, References to More than One List” Error.

One thing to note is that I am using a data service and returning strongly-typed objects. An alternative to this would be to simply use anonymous types if you wanted to process the results immediately, rather than return it from a method.

publicclassClients

{

public string Title { get; set; }

public string Logo { get; set; }

}

If the List field was simply a lookup field, then filtering with LINQ is very easy and would look something like this:

public IEnumerable<Clients> RetrieveClients()

{

SPMetalDataContext dataContext = new SPMetalDataContext(SPContext.Current.Web.Url);

var query = from client in dataContext.CatapultClients.ToList()

where client.Services.Title = “Applications”

orderby client.Order, client.Title

selectnewClients

{

Title = client.Title,

Logo = client.ClientLogo

};

return query;

}

However, since it is a multi-value lookup field, the filter expects an actual Service object. First I retrieve the Service, and then I pass it in as a filter.

public IEnumerable<Clients> RetrieveClients()

{

SPMetalDataContext dataContext = new SPMetalDataContext(SPContext.Current.Web.Url);

var myService = dataContext.CatapultServices.Where(e => e.Title == “Applications”).ToList().Single();

var query = from client in dataContext.CatapultClients.ToList()

where client.Services.Contains(myService)

orderby client.Order, client.Title

selectnewClients

{

Title = client.Title,

Logo = client.ClientLogo

};

return query;

}

Advertisements