Nhibernate QueryOver don't get latest database changes

Question

I am trying get a record updated from database with QueryOver. My code initially creates an entity and saves in database, then the same record is updated on database externally( from other program, manually or the same program running in other machine), and when I call queryOver filtering by the field changed, the query gets the record but without latest changes.

This is my code:

//create the entity and save in database
MyEntity myEntity = CreateDummyEntity();
myEntity.Name = "new_name";

MyService.SaveEntity(myEntity);

// now the entity is updated externally changing the name property with the 
// "modified_name" value (for example manually in TOAD, SQL Server,etc..)

//get the entity with QueryOver
var result = NhibernateHelper.Session
                 .QueryOver<MyEntity>()
                 .Where(param => param.Name == "modified_name")
                 .List<T>();

The previous statement gets a collection with only one record(good), BUT with the name property established with the old value instead of "modified_name".

How I can fix this behaviour? First Level cache is disturbing me? The same problem occurs with

CreateCriteria<T>();

The session in my NhibernateHelper is not being closed in any moment due application framework requirements, only are created transactions for each commit associated to a session.Save(). If I open a new session to execute the query evidently I get the latest changes from database, but this approach is not allowed by design requirement.

Also I have checked in the NHibernate SQL output that a select with a WHERE clause is being executed (therefore Nhibernate hits the database) but don´t updates the returned object!!!!

UPDATE

Here's the code in SaveEntity after to call session.Save: A call to Commit method is done

public virtual void Commit() 
{ 
  try 
  { 
    this.session.Flush(); 
    this.transaction.Commit();
  } 
  catch 
  { 
    this.transaction.Rollback(); 
    throw; 
  } 
  finally 
  { 
    this.transaction = this.session.BeginTransaction();
  } 
}

The SQL generated by NHibernate for SaveEntity:

NHibernate: INSERT INTO MYCOMPANY.MYENTITY (NAME) VALUES (:p0);:p0 = 'new_name'. 

The SQL generated by NHibernate for QueryOver:

NHibernate: SELECT this_.NAME as NAME26_0_ 
            FROM MYCOMPANY.MYENTITY this_ 
            WHERE this_.NAME = :p0;:p0 = 'modified_name' [Type: String (0)]. 

Queries has been modified due to company confidential policies.

Help very appreciated.

Spike

this may sound silly, but once in a project we shifted the database from SQLServer to Oracle and we were connecting to it through an oracle PL/SQL developer. I would make changes to it manually through the oracle client and then run my application and the old values were being returned, not the updated ones. Turns out I had to manually COMMIT the database inserts/updates. – Generate

@Carlos. Just to confirm if you check the DB MyEntity.Name is populated with 'modified_name' before you perform the QueryOver? Also the line this.session.Flush(); is unnecessary as the call to Commit() will perform the flushing for you. – Chthonian

@penfold: of course, the name is succesfully populated in the database before to execute the queryOver(), because I set a breakPoint after to execute the query and I check that the database is updated. Thank you for additional information about session.FLush(). – Spike

@mridula, I dont understand you when you says Turns out I had to manually COMMIT the database inserts/updates.. What did you do to get the latest changes? – Spike

well, after making changes to the records using the oracle client, I had to run the commit command for the changes to take effect in the database. I was used to working with SQLServer, which does not need this manual commit. So then when I ran my application, the changes were not visible because I hadn't commited them. – Generate

ok, you had other different problem (autocommit disabled). – Spike

Answer 1

After search and search and think and think.... I´ve found the solution.

The fix: It consist in open a new session, call QueryOver<T>() in this session and the data is succesfully refreshed. If you get child collections not initialized you can call HibernateUtil.Initialize(entity) or sets lazy="false" in your mappings. Take special care about lazy="false" in large collections, because you can get a poor performance. To fix this problem(performance problem loading large collections), set lazy="true" in your collection mappings and call the mentioned method HibernateUtil.Initialize(entity) of the affected collection to get child records from database; for example, you can get all records from a table, and if you need access to all child records of a specific entity, call HibernateUtil.Initialize(collection) only for the interested objects.

Note: as @martin ernst says, the update problem can be a bug in hibernate and my solution is only a temporal fix, and must be solved in hibernate.

Spike

Answer 2

As far as I know, you have several options :

• have your Session as a IStatelessSession, by calling sessionFactory.OpenStatelesSession() instead of sessionFactory.OpenSession()
• perform Session.Evict(myEntity) after persisting an entity in DB
• perform Session.Clear() before your QueryOver
• set the CacheMode of your Session to Ignore, Put or Refresh before your QueryOver (never tested that)

I guess the choice will depend on the usage you have of your long running sessions ( which, IMHO, seem to bring more problems than solutions )

Rate

Thank you very much @jbl, but I already have tried all options indicated by you, and nothing is valid in my context due to design requirements (only one session for the entire application lifecycle) – Spike

@Spike : these should do the trick for a first level cache problem. Does your application use some custom behaviors for lazy-loading, interceptors, second level cache ? The issue might be there. – Rate

No, second level cache is not beind referenced, and the unique specific case with relevant changes is in the collection mappings as there(a 1:N relationship): <bag name="MyChild" inverse="true" cascade="all"> <key> <column name="MYCOLUMN" not-null="true" /> </key> <one-to-many class="MyChild" /> </bag> – Spike

@Spike : from the code you provide, it seems like you are trying to implement string collections. It also seems you are altering the key column. You should add your mappings to your question. – Rate

No, there are not string collections, are custom objects collections. Its a single object with some properties, no more relevant information required on this context. Thank you very much for your interest. – Spike

Another Thing, I was having problem with a property that is mapped as a one-to-one rellation, After I save an entity, this property wasn´t being refreshed . Example: Property One to One: User.Colaborator.Manager if I add a Manager, and try to get all User, the Manager of User.Colaborator will not have a Manager, but doing a Session.Clear worked for me! – Moses

I got Another Problem with Session.Clear, i got a LazyInitializeException. Because the session has finished when I did the session.Clear – Moses

Answer 3

Calling session.Save(myEntity) does not cause the changes to be persisted to the DB immediately*. These changes are persisted when session.Flush() is called either by the framework itself or by yourself. More information about flushing and when it is invoked can be found on this question and the nhibernate documentation about flushing.

Also performing a query will not cause the first level cache to be hit. This is because the first level cache only works with Get and Load, i.e. session.Get<MyEntity>(1) would hit the first level cache if MyEntity with an id of 1 had already been previously loaded, whereas session.QueryOver<MyEntity>().Where(x => x.id == 1) would not.

Further information about NHibernate's caching functionality can be found in this post by Ayende Rahien.

In summary you have two options:

1. Use a transaction within the SaveEntity method, i.e.

   using (var transaction = Helper.Session.BeginTransaction())
   {
     Helper.Session.Save(myEntity);
     transaction.Commit();
   }
   

2. Call session.Flush() within the SaveEntity method, i.e.

     Helper.Session.Save(myEntity);
     Helper.Session.Flush();
   

The first option is the best in pretty much all scenarios.

*The only exception I know to this rule is when using Identity as the id generator type.

Chthonian

Thank you @penfold, I forget indicate you that my service called SaveEntity also perform all steps that you indicates ( I perform flush and commit) therefore the save process always has gone OK. I continue with the problem, because your approach is being performed in my code anyway. – Spike

@Carlos. Would it be possible for you to update the question to include your implementation of SaveEntity, and the SQL generated by NHibernate for both the 'SaveEntity' and subsequent 'QueryOver'. – Chthonian

Of course @penfold, here the code in SaveEntity after to call session.Save: A call to Commit method is done --> ` public virtual void Commit() { try { this.session.Flush(); this.transaction.Commit(); } catch { this.transaction.Rollback(); throw; } finally { this.transaction = this.session.BeginTransaction(); } }` – Spike

-The SQL generated by NHibernate for SaveEntity: NHibernate: INSERT INTO MYCOMPANY.MYENTITY (NAME) VALUES (:p0);:p0 = 'new_name'. -The SQL generated by NHibernate for QueryOver: NHibernate: SELECT this_.NAME as NAME26_0_ FROM MYCOMPANY.MYENTITY this_ WHERE this_.NAME = :p0;:p0 = 'modified_name' [Type: String (0)]. Queries has been modified due to company confidential policies. – Spike

I insist in say that the save process is ok, the wrong result on hibernate is the problem. I don´t understand why hibernare perform a select query but then no fill the object returned and gets from cache! – Spike

Answer 4

try changing your last query to:

 var result = NhibernateHelper.Session
             .QueryOver<MyEntity>()
             .CacheMode(CacheMode.Refresh)
             .Where(param => param.Name == "modified_name")

if that still doesn't work, try add this after the query:

NhibernateHelper.Session.Refresh(result);

Deafen

thank you for your help. I had tried with your second approach NhibernateHelper.Session.Refresh(result); previously successfully, but I want avoid the refresh method. Your first approach still not working. Thank you very much other time. – Spike

if the second approach works, then it looks like a bug in NHibernate - you could try call Session.Clear() before the query – Deafen

Yeah, Session.Clear() works but all objects are Evict, and I dont want this. – Spike

You can log a bug report for NHibernate, and/or fix it yourself since it's open source... – Deafen

Answer 5

People here do not want to call Session.Clear() since it is too strong.
On the other hand, Session.Evict() may seem un-applicable when the objects are not known beforehand.
Actually it is still usable.
You need to first retrieve the cached objects using the query, then call Evict() on them. And then again retrieve fresh objects calling the same query again.
This approach is slightly inefficient in case the object was not cached to begin with - since then there would be actually two "fresh" queries - but there seems to be not much to do about that shortcoming...
By the way, Evict() accepts null argument too without exceptions - this is useful in case the queried object is actually not present in the DB.

var cachedObjects = NhibernateHelper.Session
             .QueryOver<MyEntity>()
             .Where(param => param.Name == "modified_name")
             .List<T>();

foreach (var obj in cachedObjects) 
    NhibernateHelper.Session.Evict(obj);

var freshObjects = NhibernateHelper.Session
             .QueryOver<MyEntity>()
             .Where(param => param.Name == "modified_name")
             .List<T>()

Blameful

Answer 6

I'm getting something very similar, and have tried debugging NHibernate. In my scenario, the session creates an object with a couple children in a related collection (cascade:all), and then calls ISession.Flush(). The records are written into the DB, and the session needs to continue without closing. Meanwhile, another two child records are written into the DB and committed. Once the original session then attempts to re-load the graph using QueryOver with JoinAlias, the SQL statement generated looks perfectly fine, and the rows are being returned correctly, however the collection that should receive these new children is found to have already been initialized within the session (as it should be), and based on that NH decides for some reason to completely ignore the respective rows. I think NH makes an invalid assumption here that if the collection is already marked "Initialized" it does not need to be re-loaded from the query. It would be great if someone more familiar with NHibernate internals could chime in on this.

Pearle

Ok, after a bit more digging, this appears to be the default behavior in NH. groups.google.com/forum/#!topic/nhusers/UAvGM0Tql1w – Pearle