Linq to Dynamics - compare two properties in where

Question

I am using Dynamics sdk dll's ver 5.0.9689.2165 and trying to use Linq to get all Accounts where the Account.XDate is less than the Account.YDate (both are custom DateTime properties - I use the genenerated proxy classes in project to access these) from a Dynamics Online account.

I have this basic expression:

var accounts = myOrganizationServiceContext.CreateQuery<Account>().Where(a => a.XDate < a.YDate)

But I get the exception below when it gets processed - can't you compare 2 Entity properties up on the server?

System.InvalidOperationException occurred
  Message=variable 'a' of type 'Account' referenced from scope '', but it is not defined
  Source=System.Core
  StackTrace:
       at System.Linq.Expressions.Compiler.VariableBinder.Reference(ParameterExpression node, VariableStorageKind storage)
       at System.Linq.Expressions.Compiler.VariableBinder.VisitParameter(ParameterExpression node)
       at System.Linq.Expressions.ExpressionVisitor.VisitMember(MemberExpression 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.Compiler.LambdaCompiler.Compile(LambdaExpression lambda, DebugInfoGenerator debugInfoGenerator)
       at Microsoft.Xrm.Sdk.Linq.QueryProvider.CompileExpression(LambdaExpression expression)
       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.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.TranslateWhere(QueryExpression qe, String parameterName, Expression exp, List`1 linkLookups)
       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 my call was here

InnerException:

Answer 1

This represents a limitation of the CRM Linq provider (yet again). Per Microsoft:

where
The left side of the clause must be an attribute name and the right side of the clause must be a value. You cannot set the left side to a constant. Both the sides of the clause cannot be constants.

To solve your particular problem using the Linq provider, you might have to gather the entire AccountSet in memory, then use regular Linq methods against the result to get the final result you're after.

var accounts = myOrganizationServiceContext
    .CreateQuery<Account>()
    .Select(a => new { a.AccountId, a.XDate, a.YDate })
    .ToList();

var filteredAccounts = accounts
    .Where(a => a.XDate < a.YDate);