Nullable filters in XRM where clause

Question

I'm using the XRM (early bound) types in a WCF project so I have access to the CRM model and can use LINQ queries. But I've been running into a problem described here, it's the limitations on the where clause specific to XRM LINQ:

where [clause limitations]

The left side of the clause must be an attribute name and the right side of the clause must be a value. You cannot set the left side to a constant. Both the sides of the clause cannot be constants.

Supports the String functions Contains, StartsWith, EndsWith, and Equals.

One requirement that keeps popping up is when a parameter is null, all entities should be returned otherwise filter by the parameter. But I can't think of a way to do this without breaking the requirements above, or writing multiple queries to handle the scenario when it's null.

this is an example of one of my queries, the typeFilter == null is the problem here is I've used a constant on the LHS. In my real code there's a guard clause that points typeFilter == null to another query but I now have to add a start/end date filter (both nullable) and I cannot express how much I don't want to write a query for every combination of nullables.

private IQueryable<EventInfo> getAllEvents( DataContext context, EventType? typeFilter )
{
    return (
        from evt in context.new_eventSet
        where
        ( evt.statecode == new_eventState.Active ) &&
        ( typeFilter == null || evt.new_EventType.Value == (int) typeFilter.Value )
        select new EventInfo()
        {
            ID = evt.Id,
            EventType = (EventType) evt.new_EventType.Value
            ...
        } );            
}

Bouilli

Answer 1

How about:

if (typeFilter == null)
{
        return (
        from evt in context.new_eventSet
        where
        ( evt.statecode == new_eventState.Active )
               select new EventInfo()
        {
            ID = evt.Id,
            EventType = (EventType) evt.new_EventType.Value
            ...
        } );   
}
else
{
        return (
        from evt in context.new_eventSet
        where
        ( evt.statecode == new_eventState.Active ) &&
        evt.new_EventType.Value == (int) typeFilter.Value )
        select new EventInfo()
        {
            ID = evt.Id,
            EventType = (EventType) evt.new_EventType.Value
            ...
        } );   
}

Germander

the problem is I now have to add a start / end date filter in addition to the type filter. writing the query out 4 times with if/else statements to handle the extra filters is a bit much – Bouilli

actually more than 3 times, as I think you'd need every combination – Bouilli

I don't think you need the parentheses at return statement. – Forgot

Answer 2

I've answered my own question! sometimes you just need a place to vent your problems before you get it.

the trick was to not use LINQ syntax:

private IQueryable<EventInfo> getAllEvents( DataContext context, EventType? typeFilter, DateTime? startDateFilter, DateTime? endDateFilter  )
{
    var result = context.new_eventSet
        // active records
        .Where( evt => evt.statecode == new_eventState.Active )             
        // is publish-able
        .Where( ..etc.. );

    if ( typeFilter != null )
    {
        // filter by type
        result = result.Where( evt => evt.new_EventType.Value == (int) typeFilter.Value );
    }

    if( startDateFilter != null)
    {
        // filter by startDate
        result = result.Where(evt => evt.new_StartDate > startDateFilter.Value);
    }

    if( endDateFilter != null)
    {
        // filter by endDate
        result = result.Where(evt => evt.new_StartDate < endDateFilter.Value);
    }

    return result.Select( evt => new EventInfo()
        {
            ID = evt.Id,
            EventType = (EventType) evt.new_EventType.Value,
            ...
        }  );       
}

Bouilli

Answer 3

If you want to use the Linq syntax, it is possible to construct a query dynamically using LinqKit.

I have used it for this purpose on the Dynamics CRM project which I'm currently working on, and it does the job very well.

Please refer to the following answer, which is where I got the idea: https://mcmap.net/q/541577/-linq-to-dynamics-crm-query-filtering-records-locally

Revisal