fluent nhibernate collation conflict

Question

hi, really struggling to resolve this issue. using nhibernate Im trying to join two different tables from two different databases but im getting a collation conflict error.

To resolve this issue i know i need to append "collate Latin1_General_CI_AS" to the end of my sql string but have no idea how to do it using nhibernate.

Error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

have looked for solutions for some time without any real joy. I get the feeling that it will be a simple configuration change. I thought that maybe i could override some string function within IQuery to append "collate Latin1_General_CI_AS" to the end of the sql but couldnt find anything obvious.

the sql that nhibernate fails on fails in ms sql 2005 management studio but runs and returns a result if i append the collate.

any help would be greatly recieved.

much love c

Answer 1

ok the answer to this is simply(it would appear), you cant using 2 different domain models from 2 different databases that have 2 different collations.

I created a named query and appended the collation parse string. Using the snipped below shows how to return this query as a domain model.

IList<UserCustomer> collection = session.GetNamedQuery("GetCustomerDetails")
                    .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(UserCustomer)))
                    .SetString("username", username)
                    .List<UserCustomer>();