Linq Queryable from OrganizationServiceProxy throws exception when comparing two fields

Question

I've tried to reduce this example to remove the OrganizationServiceProxy/XrmServiceContext, but now I believe the issue is originating there. I'm pulling data from a Dynamics 365 instance using the code generated by CrmSvcUtil.exe from Microsoft. Here is the relevant snippet:

ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
CrmServiceClient client = new CrmServiceClient(userName, CrmServiceClient.MakeSecureString(password), string.Empty, orgName, isOffice365: true);

using (OrganizationServiceProxy service = client.OrganizationServiceProxy)
{
    XrmServiceContext crm = new XrmServiceContext(service);

    var q = crm.CreateQuery<Account>();

    List<Account> accounts = q.Where(x => x.AccountNumber != x.dynamics_integrationkey).ToList();
}

I get the following exception:

variable 'x' of type '{company-specific}.Account' referenced from scope '', but it is not defined

This is happening on the last line when the Linq is actually executed. It's only when it's created through the Microsoft.Xrm.Sdk.Client.OrganizationServiceContext that I get the error.

Things I've checked:

I get the error when comparing any two fields on the same object (not just Account), even when both are the same exact data type.
If I replace crm.CreateQuery... with a hand-populated List...AsQueryable() the Linq works fine
Most frustratingly, if I work through an intermediary list with List<Account> a = q.ToList(); to rasterize the result set first then this all works fine. It takes much longer to run because I've forfeited any lazy loading, but it works without error.
I've looked at other answers related to the referenced from scope '' but not defined Linq error, but they are all about malformed Linq. I know this Linq is well formed because it works fine if I change the underlying Queryable.

Clearly something is different between a List...ToQueryable() and the Microsoft.Xrm.Sdk.Linq.Query<> created by the XrmServiceContext (which inherits CreateQuery from Microsoft.Xrm.Sdk.Client.OrganizationServiceContext), but I don't know what it is.

Guido Preite · Accepted Answer · 2019-12-14 13:29:30Z

0

The query you are trying to build (compare a field value with another field value on the same entity) is not possible in Dynamics.

The LINQ Provider still uses QueryExpression under the hood, meaning that your LINQ condition will be converted in a ConditionExpression, and that comparison is not possible.

1 Comment

ArcherT Over a year ago

Thanks, that seems to be it. I'll have to code around it and take the performance hit or implement Aron's suggestion.

Aron · Accepted Answer · 2019-12-15 12:37:04Z

This morning I realized that my answer contained the flaw of comparing two fields against each other in a LINQ query. My bad. Thank you Guido for calling that out as impossible.

I generate my proxy classes with a 3rd party tool, so I'm not familiar with the XrmServiceContext class. But, I have confirmed that XrmServiceContext inherits from Microsoft.Xrm.Client.CrmOrganizationServiceContext, which inherits from Microsoft.Xrm.Sdk.Client.OrganizationServiceContext

I use OrganizationServiceContext for LINQ queries.

If you retrieve all the accounts first by calling ToList() on the query, you can then do the comparison of the two fields. If you have too many Accounts to load all at once, you can page through them and store the mismatched ones as you go.
And, I would probably retrieve a subset of the fields rather than the whole record (as shown).

var client = new CrmServiceClient(userName, CrmServiceClient.MakeSecureString(password), string.Empty, orgName, isOffice365: true);

using (var ctx = new OrganizationServiceContext(client))
{
    var q = from a in ctx.CreateQuery<Account>()
            where a.AccountNumber != null
            && a.dynamics_integrationkey != null
            select new Account 
            {
                Id = a.AccountId,
                AccountId = a.AccountId,
                Name = a.Name,
                AccountNumber = a.AccountNumber,
                dynamics_integrationkey = a.dynamics_integrationkey
            };

    var accounts = q.ToList();
    var mismatched = accounts.Where(a => a.AccountNumber != a.dynamics_integrationkey).ToList()
}

Collectives™ on Stack Overflow

Linq Queryable from OrganizationServiceProxy throws exception when comparing two fields

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related