Nhibernate createsqlquery from different db won't persist

Question

I first try and pull the customer from the db, but if my customer doesn't exist, then i make a sql call another db on the server to get the information. I'd like to then save the customer info in my db. The Session is currently hooked up to my db.

var customer = Session.Linq<Customer>().FirstOrDefault(x=>x.customernumber == cusotmerNumber);

if(customer == null) {
    var sql = @"select ... from anotherdb.dbo.customer where customernumber = :customer";

    customer = Session.CreateSqlQuery(sql)
        .AddEntity(typeof(Customer))
        .SetInt32("CustomerNumber",CustomerNumber).List<Customer>().FirstOrDefault();

    using(Session.BeginTransaction()) {
        Session.Save(customer);
        Session.Transaction.Commit();
    }

}

THe issue that I'm having is that when I call the Session.save, it doesn't persist the customer from the alternate db into my current db. If I hand assign all the values to a new customer object, then it will save if using the .Save.

When I look at the transaction properties after the commit, it shows the flag that it was committed, etc, and no errors are thrown. I've done the same with Session.Flush, Session.SaveOrUpdate, etc.

Is there some way, to force an insert/update?

Answer 1

NHibernate has no way of knowing that you are querying a different database, that's why it thinks the instance is already persistent and does not try to insert again.

You could use a StatelessSession, which doesn't track entities, and use the Insert method. Evict is not that bad, though.