Lambda expression with .Where clause using Contain

问题:

When connecting to CRM 2013 is there a smart way to create a lambda expression that gets the entities who's GUID are in a List.

This code breaks on the Where clause and gives the error:

Invalid 'where' condition. An entity member is invoking an invalid property or method.

Code:

    private List<UserInformationProxy> GetContactsFromGuidList(List<Guid> contactList)
    {
        var result = _serviceContext.ContactSet
            .Where(x=> contactList.Contains((Guid) x.ContactId)) // this line breaks
            .Select(x => new UserInformationProxy()
            {
                FullName = x.FullName,
                Id = x.ContactId
            })
            .Distinct()
            .ToList<UserInformationProxy>();

        return result;
    }

    // return class
    public class UserInformationProxy
    {
        public Guid? Id { get; set; }
        public string FullName { get; set; }
        public string DomainName { get; set; }
    }

Currently I'm solving this by getting all the contacts from the ContactSet and sorting out the ones I want with a loop in my code. This works, but is quite slow as I need to get all 10000 contacts instead of sending the Guids of the 40 Im actually interested in to the SQL server.

回答1:

QueryExpressions support an In operator, so this should work just fine:

private List<UserInformationProxy> GetContactsFromGuidList(List<Guid> contactList)
{
    var qe = new QueryExpression(Contact.EntityLogicalName);
    qe.ColumnSet = new ColumnSet("fullname", "contactid")
    qe.Criteria.AddCondition("contactid", ConditionOperator.In, list.Cast<Object>().ToArray());
    qe.Distinct = true;

    var results = service.RetrieveMultiple(qe).Entities.Select (e => e.ToEntity<Contact>()).
        Select(x => new UserInformationProxy()
        {
            FullName = x.FullName,
            Id = x.ContactId
        });

    return results;
}

On a side note, every Contact has to have an Id that is not empty, so there is no need to check for it.

回答2:

EDIT: It is possible to accomplish using a single query, Daryl posted an answer with the right code.

Other (not so clever) alternatives are:

1. Retrieve all the records and after check the Guids
2. Do a single retrieve for each Guid

Because are only 40 records, I suggest to use late-bound to retrieve the records, in order to choose the minimal ColumnSet.

Useful links related to this issue:

• Another question regarding Dynamics CRM LINQ limitations
• Performance test Early Bound vs Late Bound