Loading a entity IDs CSV column as hydrated entities in NHibernate

Question

I have a number of database table that looks like this:

EntityId int : 1
Countries1: "1,2,3,4,5"
Countries2: "7,9,10,22"

I would like to have NHibernate load the Country entities identifed as 1,2,3,4,5,7,9 etc. whenever my EntityId is loaded.

The reason for this is that we want to avoid a proliferation of joins, as there are scores of these collections.

Answer 1

Does fetching the countries have to happen as the entity is loaded - it is acceptable that you run a query to fetch these? (You could amend your DAO to run the query after fetching the entity.) The reason I ask is that simply running a query compared to having custom code invoked when entities are loaded requires less "plumbing" and framework innards.

After fecthing your entity, and you have the Country1,Country2 lists, You can run a query like:

select c from Country c where c.id in (:Country1)

passing :Country1 as a named parameter. You culd also retrieve all rows for both sets of countries

select Entity e where e.id in (:Country1, :Country2)

I'm hoping the country1 & country2 strings can be used as they are, but I have a feeling this won't work. If so, you should convert the Strings to a collection of Integers and pass the collection as the query parameter.

EDIT: The "plumbing" to make this more transparent comes in the form of the IInterceptor interface. This allows you to plug in to how entities are loaded, saved, updated, flushed etc. Your entity will look something like this

   class MyEntity
   { 
       IList<Country> Country1;
       IList<Country> Country2;
       // with public getter/setters

       String Country1IDs;
       String Country2IDs;
       // protected getter and setter for NHibernate
   }

Although the domain object has two representations of the list - the actual entities, and the list of IDs, this is the same intrusion that you have when declaring a regular ID field in an entity. The collections (country1 and Country2) are not persisted in the mapping file.

With this in place, you provide an IInterceptor implementation that hooks the loading and saving. On loading, you fetch the value of the countryXID property an use to load the list of countries (as I described above.) On saving, you turn the IList of countries into an ID list, and save this value.

I couldn't find the documentation for IInterceptor, but there are many projects on the net using it. The interface is described in this article.

Answer 2

No you cannot, at least not with default functionality.

Considering that there is no SPLIT string function in SQL it would be hard for any ORM to detect the discreet integer values delimited by commas in a varchar column. If you somehow (custom sql func) overcame that obstacle, your best shot would be to use some kind of component/custom user type that would still make a smorgasbond of joins on the 'Country' table to fetch, in the end, a collection of country entities...

...but I'm not sure it can be done, and it would also mean writing from scratch the persistence mechanism as well.

As a side note, I must say that i don't understand the design decision; you denormalized the db and, well, since when joins are bad?

Also, the other given answer will solve your problem without re-designing your database, and without writing a lot of experimental plumbing code. However, it will not answer your question for hydration of the country entities

UPDATE: on a second thought, you can cheat, at least for the select part. You could make a VIEW the would split the values and display them as separate rows:

Entity-Country1 View:
EntityId Country
1        1
1        2
1        3

etc

Entity-Country2 View:
EntityId Country
1        7
1        9
1        10

etc

Then you can map the view