A while ago I jumped through the hoops of fire to find out how to select distinct records from a table using Castle ActiveRecord. Although it turned out to not be too difficult it is not one of those code snippets that I keep in brain’s cache. It is for this reason that I am adding it to my blog with the hope that it may help me and others in the future.
The below query will select a distinct list of companies who have employees whose name starts starts with a J.
DetachedCriteria distinctCriteria = DetachedCriteria.For(typeof (Company))
.SetProjection(Projections.Distinct(Property.ForName("Identifier")))
.CreateCriteria("Employees")
.Add(Restrictions.Like("Title", "j%"));
DetachedCriteria fullCriteria = DetachedCriteria.For(typeof(Company))
.Add(Subqueries.PropertyIn("Identifier", distinctCriteria));
Company[] result = ActiveRecordBase<Company>.FindAll(fullCriteria, new Order[] { new Order("Title", true) });
I have mocked up an example that show the select as it would normally be performed which returns duplicate date. The example then shows the above select distinct query which does not return the duplicates. You can download it from here.