Update using ISQLQuery clears all cache.

[esabah]

Hi,
We use Nhibernate 5.4.0-dev.3260 and second level cache enabled.
When I update an entity using ISQLQuery as below. All cached queries are deleted from cache.

ISQLQuery query = session.CreateSQLQuery("UPDATE CUSTOMER SET CITY=:City where GUID= :Guid");

In NHibernate/Cache/UpdateTimestampsCache class, Invalidate(IReadOnlyCollection spaces) method executes for all entities because spaces parameter includes all entities in project. Each time update executed all keys invalidated.

But when IQuery is used for update as below it works correctly. UpdateTimestampsCache.Invalidate method executes for only changed entity.

var query = session.CreateQuery($@"update {typeof(Customer)} set Name= 'test' where Guid = 100"); query.ExecuteUpdate();

I tired to reproduce case in the git repo below.

https://github.com/esabah/NHibernateBugTest/blob/master/NHibernateBugTest/CacheNativeSqlTest.cs

RetrieveFromCacheUpdate method retrieves third select from db.
But RetrieveFromCacheUpdate2 method retrieves third select from cache.

[bahusoid]

My guess is that when no entities/query spaces are specified - all caches are cleaned up on update. Try to specify it explicitly:

session.CreateSQLQuery("UPDATE CUSTOMER SET CITY=:City where GUID= :Guid").AddSynchronizedEntityClass(typeof(Customer));

[gokhanabatay]

Hi @bahusoid,
According to the definitions made via "yaml" file, we designed a system that will read the files and import them into the database. Since the system uses native SQLs, we want to bypass the second level cache Invalidate and PreInvalidate events. Could there be another solution without explicitly specifying entity, to do this by using stateless session or another way to bypass the second level cache Invalidate event?

[gliljas]

You could use AddSynchronizedQuerySpace instead, and give it a name of something that is not mapped.

https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate.Test/Async/BulkManipulation/NativeSQLBulkOperationsWithCache.cs