CRM 2011: An unhandled exception of type 'Syst

Question

I am getting the following error message when I execute this query. If I remove the second where clause, query works fine.

Code

(from cl in context.CreateQuery<ContractDetail>()
  join a in context.CreateQuery<Account>()
      on cl.CustomerId.Id equals a.AccountId
  where cl.StateCode.Value == 0
  where cl.new_SupportedBy == a.Name
  select cl).ToList();

Error

An unhandled exception of type 'System.InvalidOperationException' occurred in ConsoleApplication1.exe

Additional information: variable '<>h__TransparentIdentifier0' of type '<>f__AnonymousType0``2[ConsoleApplication1.ContractDetail,ConsoleApplication1.Account]' referenced from scope '', but it is not defined

UPDATE

Try to hardcode the value in where clause as following and both time it worked. Can't understand what is the issue.

Firstly tried as:

where cl.new_SupportedBy == "abc"

Secondly:

where a.Name == "abc"

Exception Thrown By Linq Pad

at System.Linq.Expressions.Compiler.VariableBinder.Reference(ParameterExpression node, VariableStorageKind storage)
at System.Linq.Expressions.Compiler.VariableBinder.VisitParameter(ParameterExpression node)
at System.Linq.Expressions.ParameterExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitMember(MemberExpression node)
at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitMember(MemberExpression node)
at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)
at System.Linq.Expressions.Compiler.VariableBinder.VisitLambda[T](Expression`1 node)
at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda, DebugInfoGenerator debugInfoGenerator)
at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateExpressionToValue(Expression exp, ParameterExpression[] parameters)
at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateExpressionToConditionValue(Expression exp, ParameterExpression[] parameters)
at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateWhereCondition(BinaryExpression be, FilterExpressionWrapper parentFilter, Func`2 getFilter, Boolean negate)
at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateWhere(String parameterName, BinaryExpression be, FilterExpressionWrapper parentFilter, Func`2 getFilter, List`1 linkLookups, Boolean negate)
at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateWhereBoolean(String parameterName, Expression exp, FilterExpressionWrapper parentFilter, Func`2 getFilter, List`1 linkLookups, BinaryExpression parent, Boolean negate)
at Microsoft.Xrm.Sdk.Linq.QueryProvider.GetQueryExpression(Expression expression, Boolean& throwIfSequenceIsEmpty, Boolean& throwIfSequenceNotSingle, Projection& projection, NavigationSource& source, List`1& linkLookups)
at Microsoft.Xrm.Sdk.Linq.QueryProvider.Execute[TElement](Expression expression)
at Microsoft.Xrm.Sdk.Linq.QueryProvider.GetEnumerator[TElement](Expression expression)
at Microsoft.Xrm.Sdk.Linq.Query`1.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at UserQuery.RunUserAuthoredQuery() in c:\Users\abc\AppData\Local\Temp\LINQPad\_nmuxfnrq\query_lnnofb.cs:line 34

Thanks in Advance

Answer 1

Maybe where cl.new_SupportedBy == a.Name is the problem

You can't write a CRM LINQ query wherein the where clause that contains a predicate that compares attributes; even if those attributes are defined on the same entity instance.

https://social.microsoft.com/Forums/en-US/2d510944-c46f-4698-a6aa-c3ed5f76dc77/freaky-linq-issues-with-joins?forum=crmdevelopment

Answer 2

I have sorted the problem as below:

var contractLines = (from cl in context.CreateQuery<ContractDetail>()
  join a in context.CreateQuery<Account>()
  on cl.CustomerId.Id equals a.AccountId                                 
  where cl.StateCode.Value == 0
  select new {cl, a}).ToList();

var collection = new EntityCollection();

foreach (var line in contractLines)
{
    if (line.a.Name == line.cl.dbc_SupportedBy)
    {
        collection.Entities.Add(line.cl);
    }
}

I understand, it is not a perfect solution. But as this application is only one time run, so I don't mind if it performs slow. But I am still curious to know why the error occurred, so I will keep this question open.

Update

Kept it open for more than six months without any reply. So I am closing it now, as this workaround worked for me and also closing for the sake of my answer acceptancy percentage :P