Invalid CRM 2011 LINQ Query: "Invalid 'where' condition. An entity member is invoking an invalid property or method."

Question

I am trying to execute this query to retrieve Audit items for specific entity types

public List<Audit> GetAuditChangesSince(DateTime since, string entityType)
{
    return (from a in OrgContext.CreateQuery<Audit>()
        where
            a.ObjectId != null && a.ObjectId.LogicalName == entityType &&
            a.CreatedOn > since
        select a).ToList();
}

The a.ObjectId != null && a.ObjectId.LogicalName == entityType && clause is causing problems. I know .Equals() may cause problems (hence ==) and there are these limitations to the LINQ Provider:

The left side of the clause must be an attribute name and the right side of the clause must be a value

The left side is a property and the right side is a constant. Is the .ObjectId.LogicalName causing the problem?

Solution 1

Because the Audit entity doesn't provide an ground-level attribute for the logical name/type code of the entity a particular record is related to, the best you can do here is to link to the entity (or entities) you wish to locate audit records for -- that is without retrieving all the records.

A general technique for scenarios like this is that you can link to an related entity with a semi-nonsense condition like checking that the primary key is not null. For your case, just the link should be enough.

An example for pulling audit records tied to a contact:

from a in OrgContext.CreateQuery<Audit>()
join c in ContactSet on a.ObjectId.Id equals c.ContactId
where a.ObjectId != null && a.CreatedOn > since
select a

Solution 2

Add a ToList() after the CreateQuery method, in this way your conditions will work with LINQ to Objects provider and not with the CRM LINQ provider (and its limitations)

public List<Audit> GetAuditChangesSince(DateTime since, string entityType)
{
    return (from a in OrgContext.CreateQuery<Audit>().ToList()
        where
            a.ObjectId != null && a.ObjectId.LogicalName == entityType &&
            a.CreatedOn > since
        select a).ToList();
}

Comments

[Ryan]

Would that cause the OrgContext.CreateQuery<Audit>() to evaluate, therefore pulling all Audit records from the server, before applying filtering?

[Guido Preite]

With high probability is yes, you can split in two steps, retrieving first the Audit with only the createdon condition and after evaluate the entity type.

[ccellar]

@Ryan yes, it will evaluate.

[GotDibbs]

Depending on the level of auditing in the org, splitting the query this way is a really bad idea. You'll be pulling back an excessive number of records.

[Ryan]

Thanks for your answer. Could you kindly explain what you mean by "ground-level attribute"? Thank you.

[GotDibbs]

No problem! I just meant that, as you suggested in your question, since there is no actual attribute on the audit entity for the logical name/type code of the entity the audit record is related to, there is no way to filter directly on the audit entity. In terms of querying CRM we have to think of fields of type EntityReference as only being queryable by their Id properties. The metadata of what type of record is associated to that Id is abstracted away from us and not directly queryable in this instance. Let me know if I can clarify further!