Tuesday 22 March 2011

The query uses unsupported elements

I am using SPMetal for creating partial classes for accessing my SharePoint lists by code.
Today I was trying to run a Linq query in LinqPad on a SharePoint list (see here how to use LinqPad on SharePoint data). See query:
var q = from w in WorkItems
  where w.ShortUserID == "rob"
  where (((DateTime)(w.WorkDate)) >= firstDay 
     && ((DateTime)(w.WorkDate)) <= firstDay.AddDays(6))
  select new {w.Title, w.WorkDate, w.Hours, sprint = w.SprintItem.Title};
q.Dump();
Well, it's just an ordinary Linq query, but as always with SharePoint related programming, nothing goes well the first time. I got the following error message:
The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet
It has something to do with the 'sprint' information I needed. The 'SprintItem' is a property (Type of SprintItem object) of the WorkItem class. Somehow this will fail.
An explanation given by Paul Beck (see his blog):
ToList() method forces immediate query evaluation and returns the generic that contains the query result. As described in the MSDN article LINQ can't convert the LINQ to SharePoint into a CAML query so by using the ToList() method, the query is broken into 2 stages. This will apply to queries that use JOINS, UNIONS, and various other LINQ operators as described in the MSDN Unsupported LINQ queries article.
In his article he relates this problem with 'join'. However, I didn't use this keyword. Nevertheless his solution worked, I needed to put 'ToList()' right behind 'WorkItems' and the problem is solved. So the code will now looks like:
var q = from w in WorkItems.ToList()
  where w.ShortUserID == "rob"
  where (((DateTime)(w.WorkDate)) >= firstDay 
      && ((DateTime)(w.WorkDate)) <= firstDay.AddDays(6))
  select new {w.Title, w.WorkDate, w.Hours, sprint = w.SprintItem.Title};
q.Dump();

5 comments:

sem said...

Great , thanks , that's work also for me

Rob said...

You're welcome Sem. Happy programming!

Anonymous said...

Thanks for sharing this!

Unknown said...

Thanks, great workaround

klaus said...

The amount of money you are prepared to put on your Smart City SIM Cards and the types of activities you want to use it for will significantly influence which choice is the best for use when travelling in a foreign country.