CODEWITHSUNDEEP
linqdynamics-crm-2011
Bvrce
Bvrce

Reputation: 2170

Can LINQ be used in Dynamics CRM to get all Accounts not in a Collection?

How can a LINQ query be written to return all Accounts where the account number is not in a List?
The list is going to be pulled from an excel document.

private bool GetAccounts()
{
        List<String> accountIds = new List<String>();
        accountIds.Add( "[unknown]");
        var query = from accounts in context.AccountSet where !accountIds.Contains(accounts.AccountNumber) select accounts;
}

It does not have to be a List.

EDIT

This is what happens when the above query runs - Is this CRM's fault? enter image description here

Upvotes: 6

Views: 4803

Answers (1)

Andy Meyers
Andy Meyers

Reputation: 1581

I don't believe you can via linq. Here is the where clause limitations from the SDK.

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.

Supports the String functions Contains, StartsWith, EndsWith, and Equals.

You can get around these limitations by using QueryExpression or FetchExpressions. The query you want would look like this using QueryExpression. The only thing I would mention is if you are expecting a lot of record (5000+ I believe) you will most likely need to implement paging for your function as well. 

private static IEnumerable<Account> GetAccounts(IOrganizationService proxy)
{
    List<String> accountIds = new List<String>(new string[]{"654321", "12345"});

    var results = proxy.RetrieveMultiple(new QueryExpression(Account.EntityLogicalName)
    {
        ColumnSet = new ColumnSet("accountid", "name", "accountnumber"),
        Criteria = new FilterExpression()
        {
            Conditions = { new ConditionExpression("accountnumber", ConditionOperator.NotIn, accountIds) }
        }
    });

    return results.Entities.Select(x=> x.ToEntity<Account>());
}

Upvotes: 16

Related Questions