Xrm.Sdk Linq with Joins and dynamic where

Question

After a bunch of time getting my query syntactically correct it looks like the the sdk has some limitations.. i also tried a sub query with facility but it returned a IQuery (i or something). Is there a way to achieve a dynamic where clause in a Linq Xrm Query

the exception thrown

System.NotSupportedException: The method 'Where' cannot follow the method 'Select' or is not supported. Try writing the query in terms of supported methods or call the 'AsEnumerable' or 'ToList' m ethod before calling unsupported methods.

var predicate = PredicateBuilder.New<GetAllResult>(false);

if (query?.ObservationOnStart != null && query?.ObservationOnEnd != null)
{
    predicate.And(result =>
        result.ObservationEntity.esor_ObservationOn >= query.ObservationOnStart &&
        result.ObservationEntity.esor_ObservationOn <= query.ObservationOnEnd);
}


var queryable = from observationEntity in _ctx.esor_ObservationSet
        join facilityEntity in _ctx.core_FacilitySet
            on observationEntity.esor_Facility.Id equals facilityEntity.Id
        orderby observationEntity.esor_ObservationOn
        select new GetAllResult {ObservationEntity = observationEntity, FacilityEntity = facilityEntity}
    ;


// THIS throws exception
 queryable.Where(predicate).ToList(); 

I have also tried checking the a varable and used and OR but it also throws an exception

where completedById.Equals(null) || observationEntity.esor_CompletedBy.Id.Equals(completedById)

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

Answer 1

In your first code snippet, you first call:

select new GetAllResult 

and then

queryable.Where(predicate) 

which doesn't work. because - as you've find out - you can't call where after calling select.

Then, in your second code snippet, you call:

where completedById.Equals(null)

Which doesn't work because the left hand side of the where clause has to be an entity attribute name, while completedById is apparently a variable you've delcared somewhere.

CRM Dynamics's Linq queries must be kept pretty simple, and if you insist on using Linq (and not, say, QueryExpression), do as follows:

// ... build you queryable, and then:

// 1. first, call ToList()
var records = queryable.ToList();

// 2. then, filter it with Where()
if (query?.ObservationOnStart != null && query?.ObservationOnEnd != null)
{
    records = records.Where(r =>
        r.ObservationEntity.esor_ObservationOn >= query.ObservationOnStart &&
        r.ObservationEntity.esor_ObservationOn <= query.ObservationOnEnd
    ).ToList();
}