How-To Deal with Multi-Criteria Queries in 3-Tier Architecture

Question

Assuming a basic 3-Tier application (UI-Service-Data Access) with a total abstraction of Data Access layer (SQL, Xml ...)

The UI applications are composed with Datagrids with multi criteria filters, find etc..

So how-to deal with mutli-criteria queries in this architecture without having to create multiple service methods with all possible criteria as parameters...

Note that UI tier doesn't know how the DAL works.

Answer 1

I use subsonic and pass a collection of where clauses to the service method

Answer 2

That is what a DTO is for.

Answer 3

I'm not sure if this is what you're looking for, but I use the DAL as a factory to create a DAL-aware object, with reasonable public properties and/or methods that encapsulate the various filter criteria.

Ask the DAL to create the object, edit filter criteria as needed, give it back, and let the DAL do it's thing with the object in whatever way it needs to for its given access method.

This, of course, assumes that you don't have a completely open-ended data structure...that you have a known and reasonable sized set of possible filter criteria. If it needs to be flexible to the point where you can pass in arbitary filter criteria for unknown data structures, this probably isn't your solution.

Answer 4

You could create an object that holds something like a KeyValuePair for each criteria you want to filter on. Your DAL can then build the where condition from that..

Like this:

class MultiCriteriaFiltering
{
    List<FilterCriteria> Criterias;

    // this method just sits here for simplicity - it should be in your DAL, not the DTO
    string BuildWhereCondition()
    {
        StringBuilder condition = new StringBuilder();
        condition.Append("WHERE (1=1) "
        foreach (FilterCriteria criteria in Criterias)
        {
            condition.Append(" AND ").Append(criteria.FieldName).Append(" = ");
            condition.Append("'").Append(criteria.FilterValue).Append("'");
        }
        return condition.ToString();
    }
}

class FilterCriteria
{
    string FieldName { get; set; }
    object FilterValue  { get; set; }
}

You could quite easily extend on that, e.g. add an "operator" field to the FilterCriteria class, to allow for more filtering options than just exact matches..

Answer 5

Check out Rob's Storefront tutorial. It uses a model that is passed up from the DAL, thru the Service layer and even used in the UI layer. This ok, and doesn't break you requirement where the UI can't know how the DAL is implemented. You can easily move your Domain Model onto another VS project in the event you wanted 3rd party apps to access your service layer and not be aware how the DAL works.

This answer has some details about ways to abstract functions of LinqToSql away in higher layers. You might want to do this if, like me, you love the deferred execution features of LinqToSql but you don't want your app to depend on LinqToSql as a data provider.