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?

score 8 · Accepted Answer · answered May 27 '13 at 04:33

8

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

answered May 27 '13 at 04:33

GotDibbs

2,490
1
17
27

2

Thanks for your answer. Could you kindly explain what you mean by "ground-level attribute"? Thank you. – Ryan May 27 '13 at 23:45
2

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! – GotDibbs May 28 '13 at 00:07

score 1 · Answer 2 · answered May 23 '13 at 09:43

1

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

answered May 23 '13 at 09:43

Guido Preite

13,789
3
32
63

1

Would that cause the OrgContext.CreateQuery() to evaluate, therefore pulling all Audit records from the server, before applying filtering? – Ryan May 23 '13 at 09:59
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. – Guido Preite May 23 '13 at 10:23
1

@Ryan yes, it will evaluate. – ccellar May 23 '13 at 11:40
6

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. – GotDibbs May 27 '13 at 04:02

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

2 Answers2

Linked