Nhibernate: Why session cache and query results mismatch?

Question

We are using Nhibernate v4.0.30319 and we are using Flushmode.Commit. Let's consider an example Employee table with IsActive column in DB.

In one of the transaction we are fetching the Employee with empId = 100 as below:

var employee = session.Get<Employee>(100);

employee.IsActive = false;

It is not yet committed and not even flushed.

Immediately we are running the query to fetch all employees who are active.

var activeEmployees = session.CreateCriteria<Employee>()
                             .Add(Restrictions.Eq("IsActive", true))
                             .List<Employee>();

Now in the activeEmployees result I am still getting the Employee details of empId=1 but with IsActive value as false.

I am not able to understand why this inconsistent behaviour. If the query is running on the DB then it should return empId -100 with IsActive value 'True' else if it is running on first level cache it should not include empId-100 in the query result. Here it is returning EmpId-1 with IsActive false.

Why it is behaving like this? I have gone through some articles and configurations details but I am not able to figure out about this behaviour.

I have created a console app:

Employee Table:

Nhibernate configuration:

 if (_sessionFactory == null)
 {
     var configuration = new Configuration();

     // Set connection properties for SQL Server
     configuration.SetProperty(NHibernate.Cfg.Environment.ConnectionDriver, typeof(SqlClientDriver).AssemblyQualifiedName);
     configuration.SetProperty(NHibernate.Cfg.Environment.ConnectionString, "Data Source=INLT-D72FCSG3\\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True;");
     configuration.SetProperty(NHibernate.Cfg.Environment.Dialect, typeof(MsSql2012Dialect).AssemblyQualifiedName);
     configuration.SetProperty(NHibernate.Cfg.Environment.ShowSql, "true");
     configuration.SetProperty(NHibernate.Cfg.Environment.FormatSql, "true");
     configuration.SetProperty(NHibernate.Cfg.Environment.Hbm2ddlAuto, "update");


     configuration.AddFile("Employee.hbm.xml"); // Add your mapping files
     _sessionFactory = configuration.BuildSessionFactory();
 }
 return _sessionFactory;

Execute:

 using (var session = NHibernateHelper.OpenSession())
 {
     session.FlushMode = FlushMode.Commit;
     // Start a new transaction
     using (var transaction = session.BeginTransaction())
     {
         // Load employee with Id = 100
         var employee = session.Get<Employee>(1);
         Console.WriteLine($"Before Update: {employee.Name} - IsActive: {employee.IsActive}");
     // Modify the IsActive field to false (this marks the entity as dirty)
     employee.IsActive = false;

     // The entity is now in the session cache with IsActive = false
     Console.WriteLine($"After Update: {employee.Name} - IsActive: {employee.IsActive}");

     // Now, we fetch active employees (IsActive = true)
     var activeEmployees = session.CreateCriteria<Employee>()
                                  .Add(Restrictions.Eq("IsActive", true))
                                  .List<Employee>();

     // Print the active employees (it should still return the modified entity in the session cache)
     Console.WriteLine("\nActive Employees (before commit):");
     foreach (var emp in activeEmployees)
     {
         Console.WriteLine($"{emp.Name} - IsActive: {emp.IsActive}");
     }

     // Commit the transaction to persist changes to the database
     transaction.Commit();
 }

}

  // Simulate a new session for the next query (to see changes after commit)
 using (var session = NHibernateHelper.OpenSession())
 {
     var activeEmployees = session.CreateCriteria<Employee>()
                                  .Add(Restrictions.Eq("IsActive", true))
                                  .List<Employee>();

     // Print the active employees after the session is committed
     Console.WriteLine("\nActive Employees (after commit):");
     foreach (var emp in activeEmployees)
     {
         Console.WriteLine($"{emp.Name} - IsActive: {emp.IsActive}");
     }
 }

OutPut:

NHibernate:
    SELECT
        employee0_.Id as id1_0_0_,
        employee0_.Name as name2_0_0_,
        employee0_.IsActive as isactive3_0_0_
    FROM
        Employee employee0_
    WHERE
        employee0_.Id=@p0;
    @p0 = 1 [Type: Int32 (0:0:0)]
Before Update: John Doe - IsActive: True
After Update: John Doe - IsActive: False
NHibernate:
    SELECT
        this_.Id as id1_0_0_,
        this_.Name as name2_0_0_,
        this_.IsActive as isactive3_0_0_
    FROM
        Employee this_
    WHERE
        this_.IsActive = @p0;
    @p0 = True [Type: Boolean (0:0:0)]

Active Employees (before commit):
John Doe - IsActive: False
Jane Smith - IsActive: True
Bob Brown - IsActive: True
Alice Green - IsActive: True
NHibernate:
    SELECT
        this_.Id as id1_0_0_,
        this_.Name as name2_0_0_,
        this_.IsActive as isactive3_0_0_
    FROM
        Employee this_
    WHERE
        this_.IsActive = @p0;
    @p0 = True [Type: Boolean (0:0:0)]

Active Employees (after commit):
Jane Smith - IsActive: True
Bob Brown - IsActive: True
Alice Green - IsActive: True

Thanks,

Nagasree.

Answer 1

When first you get the employee with ID 100, the entity get cached in first level cache. Then you set it's IsActive to false. Hence, the entity in first level cache is updated; not reflected to the database yet. Value in the database is still true.

Then you query for all the employees where IsActive is true. The query must be building something like below:

SELECT * FROM Employee WHERE IsActive = true

As said above, the IsActive value for employee id 100 is still true in the database, so employee 100 is also returned as a part of result set.

THEN NHibernate builds Entity objects for all the rows returned. Other rows are not in the first level cache yet, so it builds them as new one. But, NHibernate finds that Entity object for employee 100 already exists in the first level cache, so it does not build it, it simply use it from the first level cache. As the IsActive is false in first level cache, you see the mismatch.

This is correct and expected behaviour; this is how NHibernate works.