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?

Answer 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

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