How to get a distinct list of first letters with NHibernate

Question

I have been landed into the middle of a project that is using MVC2 & NHibernate. I haven't worked with NHibernate before, so as simple as this question might seem, I'm a bit out of my depth.

I have to create a directory for a number of Company Groups. The first task is to get a distinct list of the first letter for each company.

So if we have

ACompany1
ACompnay2
BCompany1
DCompany1
DCompany2
ECompany1

I need to get a list like

A B D E (note, there's no 'C')

Can somebody please provide me with an outline of what I need to do? Thanks

Answer 1

First you need to decide how will you do it with sql. Probably you will use some kind of SUBSTRING function. After you solve it, you can write similar HQL query.

Answer 2

I wonder if you are approaching this from the wrong direction. NHibernate is used to map your database objects to your domain objects. You could create some custom map file that use a formula to get you the distinct characters but I think this is one of those cases where a simple SQL query would suffice.

The alternative would be load all the companies into Memory and use linq to get the names.

companies.Select(company => company.Name.Substring(0, 1));

I sometimes get blinkered when using a mocking framework and struggle to mock a particular entity, it isn't till I step back I realise that it would be easier and cleaner to mock my own entity.

Answer 3

Assuming the class name is Company and the property is Name...

var groups = session.CreateQuery(
                    "select distinct substring(Name, 1, 1) from Company")
                    .List<string>()