Complex Searching / Querying With NHibernate
Let us take the following form, the like of which has been known to cause grown man to cry. The client want something "very simple": I just want to be able to search on my policies, and this is the stuff that I think that I need now, and I'll need more in the future.
Just a hint on the data model as relevant to this search form, it goes something like this:
- Polic:
- M:M - Clients:
- 1:M - Claims:
- Account:
Note that I make no promises that this is a viable model for insurance, I just needed a complex domain and I can't use my usual Employees > Salaries example, since it would hit too close to the real issue that I did.
Anyway, the problem with this kind of fomrs is that they are complex beasts. I have seen search forms that were two pages long, and were accompanied with a manual (just for the search form) that was bigger than the entire system specification. The real kicker here is that there isn't a single path that the user is going through, the system should be able to handle any combination of search terms, and ignore any that isn't relevant.
There is additional complexity added by the fact that this data is not sitting in the same table, actually, just from the rough data modle above, it looks like it is sitting in no less than 6 tables.
This is also the place where the Stored Procedure approach hurts the most, in my experiance.
Let us start by looking at the UI code for this, shall we?
protected void Search_Click(object sender, EventArgs e)
{
PoliciesFinder finder = new PoliciesFinder();
if (txtClientId.Text != "")
finder.ClientId = int.Parse(txtClientId.Text);
if (txtClientName.Text != "")
finder.ClientNameLike = txtClientName.Text;
if (ddlPolicyType.SelectedIndex != 0) //everything
finder.PolicyType = (PolicyType )Enum.Parse(typeof (PolicyType), ddlPolicyStatus.Text);
finder.PolicyStatus = (PolicyStatus )Enum.Parse(typeof (PolicyStatus), ddlPolicyStatus.Text);
finder.City = (WellKnownCities )Enum.Parse(typeof (WellKnownCities), ddlCity.Text);
if (txtClaimNumber.Text != "")
finder.ClaimNumber = int.Parse(txtClaimNumber.Text);
if (ClaimStatus_Paid.Checked)
finder.ClaimStatus = ClaimStatus.Paid;
if (ClaimStatus_Processing.Checked)
finder.ClaimStatus = ClaimStatus.Processing;
if (ClaimStatus_Rejected.Checked)
finder.ClaimStatus = ClaimStatus.Rejected;
if (txtPaymentNum.Text != "")
finder.PaymentNumber = int.Parse(txtPaymentNum.Text);
if (chkAccountSize_Small.Checked)
finder.AccountSize |= AccountSize.Small;
if(chkAccountSize_Medium.Checked)
finder.AccountSize |= AccountSize.Medium;
if (chkAccountSize_Big.Checked)
finder.AccountSize |= AccountSize.Big;
if (chkAccountSize_MoneyCow.Checked)
finder.AccountSize |= AccountSize.MoneyCow;
IList<Policy> policies = finder.Find();
gridPolicies.DataSource = policies;
gridPolicies.DataBind();
}
[Despite its size, this is still just first attempt, with no checks for user input, etc]
What we have here is a Finder object, which the UI uses. It is not the job of the UI to make searches, especially not searches this complex. If this is more than a one liner that can be expressed with NHQG, I tend to create a finder object for this, which will contain all the logic for the search. All the properties on finder are nullables, since they are all optionals...
public class PoliciesFinder
{
int? clientId;
string clientNameLike;
PolicyType? policyType;
PolicyStatus? policyStatus;
WellKnownCities? city;
int? claimNumber;
ClaimStatus? claimStatus;
int? paymentNumber;
AccountSize accountSize;
...
...
...
}
Now, let us turn to the implementation of the Find() method.
public IList<Policy> Find()
{
DetachedCriteria query = DetachedCriteria.For(typeof(Policy), "policy");
AddClientQuery(query);
AddPolicyQuery(query);
AddClaimsQuery(query);
AddAccountQuery(query);
AddDateRangeQuery(query);
return query.GetExecutableCriteria(Context.Session).List<Policy>();
}
We create a detached criteria, and start to pass it to each of the helper methods...
private void AddClientQuery(DetachedCriteria query)
{
DetachedCriteria clientCriteria = null;
if(clientId.HasValue)
{
clientCriteria = DetachedCriteria.For(typeof (Client));
clientCriteria.Add(Expression.Eq("Id", clientId.Value));
}
if(clientNameLike!=null)
{
clientCriteria = clientCriteria ?? DetachedCriteria.For(typeof (Client));
clientCriteria.Add(Expression.Like("Name", clientNameLike, MatchMode.Anywhere));
}
if(city.HasValue)
{
clientCriteria = clientCriteria ?? DetachedCriteria.For(typeof(Client));
clientCriteria.Add(Expression.Eq("Area",city.Value));
}
if (clientCriteria != null)
{
clientCriteria.SetProjection(Projections.Property("Id"));
clientCriteria.Add(Property.ForName("Id").EqProperty("client.Id"));
query.CreateCriteria("Clients","client")
.Add(Subqueries.Exists(clientCriteria));
}
}
The interesting part here is in the end, where we use a subquery to check for the existance of a client that match the descriptions.
private void AddPolicyQuery(DetachedCriteria query)
{
if(policyType.HasValue)
{
query.Add(Expression.Eq("Type", policyType.Value));
}
if(policyStatus.HasValue)
{
query.Add(Expression.Eq("Status", policyStatus.Value));
}
}
Nothing interesting above, but the next one is interesting:
private void AddClaimsQuery(DetachedCriteria query)
{
DetachedCriteria claimCriteria = null;
if(claimNumber.HasValue)
{
claimCriteria = query.CreateCriteria("Claims");
claimCriteria.Add(Expression.Eq("Id", claimNumber.Value));
}
if(claimStatus.HasValue)
{
claimCriteria = claimCriteria ?? query.CreateCriteria("Claims");
claimCriteria.Add(Expression.Eq("Status", claimStatus.Value));
}
if(paymentNumber.HasValue)
{
claimCriteria = claimCriteria ?? query.CreateCriteria("Claims");
claimCriteria
.CreateCriteria("Payments", "payment")
.Add(Expression.Eq("Id", paymentNumber.Value));
}
}
Notice the use of CreateCriteria to join to other tables with ease, so we can check them as well.
private void AddAccountQuery(DetachedCriteria query)
{
if(accountSize!=AccountSize.None)
{
ICriterion accountSizeExpr=null;
foreach (AccountSize value in Enum.GetValues(typeof(AccountSize)))
{
if((accountSize & value ) == value)
{
if(accountSizeExpr==null)
{
accountSizeExpr = Expression.Eq("Size", value);
}
else
{
accountSizeExpr = Expression.Or(
accountSizeExpr,
Expression.Eq("Size", value));
}
}
}
query.CreateCriteria("Account")
.Add(accountSizeExpr);
}
}
And this example shows the fun you can have with a search API :-) Now all we have to do is...
private void AddDateRangeQuery(DetachedCriteria query)
{
if(startDate.HasValue)
{
query.Add(Expression.Le("Range.Start", startDate.Value));
}
if(endDate.HasValue)
{
query.Add(Expression.Ge("Range.End", endDate.Value));
}
}
And we are basically done. We still need to test this finder, and to see that it give us the correct results, of course (which I hadn't done in this case, by the way). The advantages of this approach is that the finder is easily extensible in the future, if the customer would like me to give him additional fields, I can simply add it to the UI and then add additional querying to the finder. The nice thing about it is that I am not concating strings, so I can just play with it just about everywhere I want, and let NHibernate figure out the final query.
One thing to be aware of is that the final SQL query is probably going to be fairly involved if all the filterring are going to be used, but that is not an expected usage for this kind of screen.