Lambda expression with .Where clause using Contains

Question

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.

possible duplicate of [Equivalent to SQL IN clause](http://stackoverflow.com/questions/8881302/equivalent-to-sql-in-clause) — andriikulyk, Jan 13 '14 at 10:17
The linked answer does not have a solution for this specific problem. The Dynamic LINQ does not seem to work for Guid's. — JensB, Jan 13 '14 at 10:48

Daryl · Accepted Answer · 2014-01-13T17:24:22.320

5

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.

edited Jan 13 '14 at 17:24

answered Jan 13 '14 at 14:01

Daryl

18,592
9
78
145

The code compiled but threw this error: The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter http://schemas.microsoft.com/xrm/2011/Contracts/Services:query. The InnerException message was 'Error in line 1 position 10724. Element 'http://schemas.microsoft.com/2003/10/Serialization/Arrays:anyType' contains data from a type that maps to the name 'System.Collections.Generic:List`1'. The deserializer has no knowledge of any type that maps to this name. . – JensB Jan 13 '14 at 14:14
1

@JensBerfenfeldt sorry, we have some extension method that make this code easier and I attempted to do it off of my head. I've updated my answer with the correct that handles the conversion from List to Object[]. I tested it, so it should work :) The `AddCondition` accepts an params object[], and the list gets treated as a single object, rather than a list, so the call to cast it as an object, and then convert to an array fixes the issue. – Daryl Jan 13 '14 at 14:26
1

As a side note, you need to add the columns you want to work with to the QueryExpression, ie: qe.ColumnSet = new ColumnSet("fullname", "contactid"); or you will just get null values back. – JensB Jan 13 '14 at 14:55
Thanks @Daryl for sharing this information, I knew the `In` clause but I didn't think it works also with the record Guid – Guido Preite Jan 13 '14 at 14:57
@GuidoPreite No Prob! – Daryl Jan 13 '14 at 17:27

score 2 · Answer 2 · edited May 23 '17 at 11:58

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:

Retrieve all the records and after check the Guids
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:

edited May 23 '17 at 11:58

Community

1
1

answered Jan 13 '14 at 11:20

Guido Preite

14,905
4
36
65

When would you say the performance breakpoint for doing single retrieve for every post compared to get all and do the sorting in code is? – JensB Jan 13 '14 at 12:04
3

It's not easy to determinate the breakpoint, but I suggested using late-bound because it is faster than early-bound for few records as in your case, you can find an analysis here: http://woodsworkblog.wordpress.com/2013/02/25/crm-2011-early-binding-vs-late-binding-performance/ – Guido Preite Jan 13 '14 at 12:29

Lambda expression with .Where clause using Contains

2 Answers2

Linked