How to handle multi-criteria queries in a 3-tier architecture

Question

Assuming a basic 3-tier application (UI-Service-Data Access) with a general abstraction of the data access level (SQL, Xml ...)

UI applications consist of Datagrids with filters with multiple criteria, find, etc.

So, how to work with mutli-query criteria in this architecture without creating several service methods with all possible criteria as parameters ...

Note that the UI layer does not know how DAL works.

Answer 1

This is what DTO means.

Answer 2

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

Answer 3

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

Ask the DAL to create the object, edit the filter criteria as necessary, return it and let the DAL do this with the object in any way for this access method.

This, of course, assumes that you do not have a completely open data structure ... that you have a known and reasonable size of possible filtering criteria. If it needs to be flexible to the point where you can pass under strict filtering criteria for unknown data structures, this is probably not your solution.

Answer 4

You can create an object that stores something like KeyValuePair for each criteria that you want to filter on. Then your DAL can build where that condition is.

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 easily spread this, for example. add an “operator” field to the FilterCriteria class so that there are more filtering options than just an exact match.

Answer 5

I like to use Query-By-Example for this. Here you can pass the actual DTO example, and any non-default fields represent the query criteria.

eg.

CustomerDTO example = new CustomerDTO();
example.lastName = "jones";
AddressDTO exAddr = new AddressDTO();
exAddr.city = "Boston";
example.addresses.add(exAddr);

var customers = svc.GetCustomersLike (example);

This can be used from a service level or from a higher level.

Answer 6

Check out Rob Tutorial on shop window . It uses a model that is passed from the DAL through the service level and is even used in the user interface layer. This is normal and does not violate requirements when the user interface cannot know how DAL is implemented. You can easily transfer your domain model to another VS project if you want third-party applications to access your service level and not know how DAL works.

This answer has some information on how to abstract LinqToSql functions in higher layers. You might want to do this if, like me, you like the LinqToSql deferred functions, but you don't want your application to depend on LinqToSql as a data provider.

Answer 7

There are several ways to do this; I used a combination of API criteria and query objects. For example, if you have a collection of persons that you want to request:

1) More flexible API criteria method: GetPerson (IList request)

 public class Criteria { Object Property; // (Domain property, not DB)// (String Or Lambda) Age, Father.Age, Friends, etc Object Operator; //(Enum or String)(Eq, Gr, Between,Contains, StartWith, Whatever...) Object Value; // (most likely Object, or use generics Criteria<T>), (Guid, int[], Person, any type). } 

2) The strictly described request object:

 public class PersonQuery { Guid? Id; GenderEnum? Gender; Int32? Age; Int32? AgeMin; Int32? AgeMax; String Name; String NameContains; Person FatherIs; Person MotherIs; //... } 

Use Nullable <> for Value types and assign Null to indicate that the parameter is not required.

Each method has positive and negative sides.