O/RM are not Magic (but they are close)
time to read 3 min | 516 words

I just got a question in Email about NHibernate. The question was using composite keys (key with several columns) and an IN. NHibernate doesn't allow this, and the question was why, and how can it be solved.

Well, it took me a couple of minutes to figure out what is happening. NHibernate is not allowing this because there isn't a good story to support this type of functionality. To my knowledge, there is no way to do an IN over multiply queries. The nice thing about NHibernate is that it allows you to do stuff that you just can't do cleanly with SQL, I suggested this approach:

ICriterion lhs = null;
foreach(Entity e in Entities)
{
            ICriterion rhs = Expression.Eq("Id", e.Id);
            if(lhs!=null)
                        lhs = Expression.Or(lhs, rhs);
            else
                       lhs = rhs;
} 

This way, you get the same functionality, without having to do anything hard. The SQL statement this will generate is going to be horrible, but I challange you to find a better solution for the problem using just SQL that doesn't involve the same approach. Here is the generated SQL for one sample I had:

SELECT

testprojec0_._id AS id, testprojec0_.Data
FROM TestFile AS testprojec0_
WHERE (testprojec0_._id = 1) AND (testprojec0_.Data = 'a') OR
           (testprojec0_._id = 3) AND (testprojec0_.Data = 'b')

For small sets of entities, this will work very well, I believe. For large sets, you need a different approach.

Tweet Share Share 0 comments
Tags: