Get filter criteria from function in c# LINQ to SQL

user_10406502 0
Viewed 114

I have the following LINQ query. Is there a way to outsource the central part into a method?

IQueryable<vwResCache> clientDataset = db.vwResCaches.Where(r =>
    clientResources.Contains(r.ID_Resource) &&
    r.StartDate < prospectDate &&
    (
        r.StartDate >= retrospectDate ||
        r.StartDate < retrospectDate && (
            (clientConfig.FinishReq_FinishedPA && clientConfig.FinishReq_DeliveryNotesPrinted && (!r.FinishedPA.Value || !r.DeliveryNotesPrinted.Value)) ||
            (clientConfig.FinishReq_FinishedPA && !clientConfig.FinishReq_DeliveryNotesPrinted && !r.FinishedPA.Value) ||
            (!clientConfig.FinishReq_FinishedPA && clientConfig.FinishReq_DeliveryNotesPrinted && !r.DeliveryNotesPrinted.Value)
        )
    ));

As you can see, the must indented part depends on the local object clientConfig. I want to exclude that into s separate method like so:

IQueryable<vwResCache> clientDataset = db.vwResCaches.Where(r =>
    clientResources.Contains(r.ID_Resource) &&
    r.StartDate < prospectDate &&
    (
        r.StartDate >= retrospectDate ||
        r.StartDate < retrospectDate && GetFilterCriteria(clientConfig, r)
    ));

This method should look something like:

FilterExpression GetFilterExpression(ClientConfig clientConfig, c)
{
    if (clientConfig.FinishReq_FinishedPA && clientConfig.FinishReq_DeliveryNotesPrinted)
        return new FilterExpression(c, r => !r.FinishedPA.Value || !r.DeliveryNotesPrinted.Value);

    if (clientConfig.FinishReq_FinishedPA)
        return new FilterExpression(c, r => !r.FinishedPA.Value);

    if (clientConfig.FinishReq_DeliveryNotesPrinted)
        return new FilterExpression(c, r => !r.DeliveryNotesPrinted.Value);
}

Is such a thing possible? Or would the effort not justify the benefit?

linq-to-sql c#
1 Answers

Calling .Where(...) multiple times has the same effect as combining the individual filters with &&.

Unfortunately, there's no simple equivalent for combining filters with ||, so your filter function would need to include the StartDate logic for each branch.

Something like this should work:

static Expression<Func<vwResCache, bool>> BuildClientConfigFilter(
    DateTime retrospectDate,
    ClientConfig clientConfig)
{
    if (clientConfig.FinishReq_FinishedPA 
        && clientConfig.FinishReq_DeliveryNotesPrinted)
    {
        return r => r.StartDate >= retrospectiveDate 
            || (r.StartDate < retrospectiveDate 
            && (!r.FinishedPA.Value || !r.DeliveryNotesPrinted.Value));
    }
    
    if (clientConfig.FinishReq_FinishedPA 
        && !clientConfig.FinishReq_DeliveryNotesPrinted)
    {
        return r => r.StartDate >= retrospectiveDate 
            || (r.StartDate < retrospectiveDate 
            && !r.FinishedPA.Value);
    }
    
    if (!clientConfig.FinishReq_FinishedPA 
        && clientConfig.FinishReq_DeliveryNotesPrinted)
    {
        return r => r.StartDate >= retrospectiveDate 
            || (r.StartDate < retrospectiveDate 
            && !r.DeliveryNotesPrinted.Value;
    }
    
    return r => r.StartDate >= retrospectiveDate;
}

Usage:

IQueryable<vwResCache> clientDataset = db.vwResCaches
    .Where(r => clientResources.Contains(r.ID_Resource))
    .Where(r => r.StartDate < prospectDate)
    .Where(BuildClientConfigFilter(retrospectDate, clientConfig));
user_1243860
Related