Showing posts with label Ineffiecient Queries. Show all posts
Showing posts with label Ineffiecient Queries. Show all posts

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