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

Question

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?

Answer 1

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>());
}