Thursday 24 June 2010

Inefficient Queries - SPMetal join issue

Problem: LINQ to SharePoint 2010 (SPMetal) is not querying 2 SharePoint lists, when using SPMetal on a visual web part. I am trying to JOIN 2 lists inside a web part. The Application error message is "The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.".



Hypothesis: At the SharePoint conference in Vegas Oct 2009 I remember in 1 of the sessions that inefficient LINQ queries are blocked. This was a little vague but a good start noting the stack trace message "InvalidOperationException: The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.]
Microsoft.SharePoint.Linq.Rules.QueryEfficiencyProcessor.Process(Expression e)"
.
Exception Details: System.InvalidOperationException: The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.
Using LINQ I can query each of the lists.  Each list has a field of the same type that I am trying to join on. I was totally confused but realised it must be an issue with the efficiency/validity of the query. A simpler query worked so it looks like the CAML is not being correctly formed. I extracted the CAML using the DataContext Log method.
My choices are:
  1. To get and fix the CAML query and then run a CAML query manually in my code;
  2. Use LINQ to objects; or
  3. Perform 2 queries and link the data manually.
Resolution:
Reading a post on MSDN on 2 stage queries gave me a quick fix. By adding the ToList() method, the query works.
var empQuery = from bug in Bugs.ToList()
join emp in Employees on bug.AssignedTo equals emp.Title
select new
{ emp.Title,
Project = bug.Project
};

Explanation: ToList() method forces immediate query evaluation and returns the generic that contains the query result.  As described in the MSDN article above 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.

Tip: Turn off Object Change Tracking if you are only reading data.

LINQ to SharePoint Posts on this Blog
Links:
Linq to SharePoint 2010 examples
Tip: CAML query for optimised speed settings, only bring back columns you will use querySearch.ViewFieldsOnly = true;
querySearch.IncludeMandatoryColumns = false;
Display the CAML that LINQ to SharePoint is running

5 comments:

Anonymous said...

Saved me some time. Thanks

Kornelis said...

Do you also happen to know, how to efficiëntly query on choicefields?

As you most probably know, choicefields are converted to enums by SPMetal. The where-clause in this piece of code is however not getting translated to CAML at all. So first the entire list is loaded to memory, then the where-clause is applied in memory (LINQ to Object):
================================
from r in ppbData.P_Planverzoek
where r.StatusPlanverzoek == StatusPlanverzoek.Ingepland
select r.StatusPlanverzoek;
================================

'ppbData' is the datacontext generated by SPMetal. Also I have tried several variations on this where-clause, but I can't get any of them translated to CAML by LINQ to SharePoint..

In the near future I need to query large lists and then having to load the entire list all the time will not be acceptable. So, you'll surely understand that any ideas are -more- then welcome!

Paul Beck said...

Hi Kornelis,

You can edit the paramters.xml file so that choice fields are no longer enums and will allow you to use strings, this is useful in that if your choice options change you don't need to update the LINQ to SharePoint proxy code. Hence I use this approach on all choice fields.

I think this will add a where clause to your CAML query. Pls confirm?

paul

Anonymous said...

Thank you for the post
JK

Amit said...

Thanks buddy. its saved my life.

Post a Comment