Unwanted table getting updated uneccessarily

FIGDevelopers · **Joined:** Tue Sep 11, 2007 5:57 am **Posts:** 36

I have an entity named Person. Person has an column named AddressUid
, that refers to AddressUid of Address table.

now I retrieve one object of Person as

Person Per = Context.FindUnique("PersonUid=''");

Then I change the LastName of the Person as
Per.LastName = "Abcd";

Then I save the object to the database

as Contect.Save(Pers).

The changes are reflected to the database , but the profiler shows an update query fired on the Address table as well , even when I didn't do any changes to the Entity Address.

Is there any work around? I strictly need session object to update only Person table.

dyahav · **Joined:** Mon Oct 22, 2007 5:44 am **Posts:** 28

Hi,

I think that NHibernate executes a sql containing all the fields
of the object when updating.
If you want to update only the fields that have been changed, use the
dynamic-update="true" in the class tag.

Regards,

FIGDevelopers · **Joined:** Tue Sep 11, 2007 5:57 am **Posts:** 36

I had tried that earlier , but it didn't work unless I used select-before-update="true".

Does both dynamic-update and select-before-update need to be used together for expected result?

canton · **Posted:** Thu Dec 13, 2007 5:44 am

Hi, do you mind posting the relevant class mapping?

FIGDevelopers · **Joined:** Tue Sep 11, 2007 5:57 am **Posts:** 36

Here is the mapping file for table Individual(Person)

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="FIG.FIGMD.BusinessEntities.Individual,BusinessEntities" table="Individual" lazy="false" dynamic-update="true">

<id name="IndividualUid" column="IndividualUid" type="Guid">
<generator class="assigned"/>
</id>
<property column="First" type="String" name="First" length="50" />
<property column="Middle" type="String" name="Middle" length="50" />
<property column="Last" type="String" name="Last" length="50" />
<property column="Prefix" type="String" name="Prefix" length="50" />
<property column="Suffix" type="String" name="Suffix" length="50" />
<many-to-one name="Race" column="RaceUid" class="FIG.FIGMD.BusinessEntities.MasterRace,BusinessEntities" cascade= "all"/>
<many-to-one name="Gender" column="GenderUid" class="FIG.FIGMD.BusinessEntities.MasterGender,BusinessEntities" cascade= "all"/>
<property column="BirthDate" type="DateTime" name="BirthDate" />
<property column="DeathDate" type="DateTime" name="DeathDate" />
<property column="SSN" type="String" name="Ssn" length="9" />
<many-to-one name="MaritalStatus" column="MaritalStatusUid" class="FIG.FIGMD.BusinessEntities.MasterMaritalStatus,BusinessEntities" cascade= "all"/>
<many-to-one name="Address1" column="Address1Uid" class="FIG.FIGMD.BusinessEntities.Address,BusinessEntities" cascade= "all"/>
<many-to-one name="Address2" column="Address2Uid" class="FIG.FIGMD.BusinessEntities.Address,BusinessEntities" cascade= "all"/>
<many-to-one name="Phone1" column="Phone1Uid" class="FIG.FIGMD.BusinessEntities.Phone,BusinessEntities" cascade= "all"/>
<many-to-one name="Phone2" column="Phone2Uid" class="FIG.FIGMD.BusinessEntities.Phone,BusinessEntities" cascade= "all"/>
<property column="EmailAddress" type="String" name="EmailAddress" length="255" />
<property column="Inactive" type="Boolean" name="Inactive" not-null="true" />
<property column="CreatedDate" type="DateTime" name="CreatedDate" />
<many-to-one name="CreatedBy" column="CreatedByUid" class="FIG.FIGMD.BusinessEntities.FigUser,BusinessEntities" cascade= "all"/>
<property column="ModifiedDate" type="DateTime" name="ModifiedDate" />
<many-to-one name="ModifiedBy" column="ModifiedByUid" class="FIG.FIGMD.BusinessEntities.FigUser,BusinessEntities" cascade= "all"/>

</class>
</hibernate-mapping>

Mapping File for class Address

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="FIG.FIGMD.BusinessEntities.Address,BusinessEntities" table="Address" lazy="false" dynamic-update="true">

<id name="AddressUid" column="AddressUid" type="Guid">
<generator class="assigned"/>
</id>
<property column="Line1" type="String" name="Line1" length="50" />
<property column="Line2" type="String" name="Line2" length="50" />
<property column="City" type="String" name="City" length="50" />
<property column="State" type="String" name="State" length="50" />
<property column="Zip" type="String" name="Zip" length="20" />
<property column="Country" type="String" name="Country" length="50" />
<property column="CreatedDate" type="DateTime" name="CreatedDate" />
<many-to-one name="CreatedBy" column="CreatedByUid" class="FIG.FIGMD.BusinessEntities.FigUser,BusinessEntities" cascade= "all"/>
<property column="ModifiedDate" type="DateTime" name="ModifiedDate" />
<many-to-one name="ModifiedBy" column="ModifiedByUid" class="FIG.FIGMD.BusinessEntities.FigUser,BusinessEntities" cascade= "all"/>

</class>
</hibernate-mapping>

I have used cascade="all", that is the reason why it updates the Address table while updating Individual. But if I set cascade="none", then address is not updated even when I do

Individual Ind = Context.FindUnique();
Ind.Address1.City = "xyz";
Context.Save(Ind);

What I expect is that if I do change the Address object than I should update it while updating Individual object, but not if I have done no changes to Address.

canton · **Posted:** Fri Dec 14, 2007 2:56 am

I wrote up a very simple test and everything worked fine. Only the Individual table was updated.

Here is the test code

Code:

using System;
using System.Collections.Generic;
using System.Text;
using NHibernate;
using NHibernate.Cfg;

namespace BugFix
{
    public class Program : IDisposable
    {
        readonly ISessionFactory sessionFactory;

        static void Main(string[] args)
        {
            try
            {
                using (Program p = new Program())
                {
                    p.Run();
                }
            }
            catch (Exception e)
            {
                Console.Error.WriteLine(e.ToString());
            }
            finally
            {
                Console.Write("Press ENTER to exit...");
                Console.ReadLine();
            }
        }

        public Program()
        {
            Configuration cfg = new Configuration();
            cfg.AddAssembly(System.Reflection.Assembly.GetExecutingAssembly());
            this.sessionFactory = cfg.BuildSessionFactory();
        }

        private void Run()
        {
            Guid individualUid = Guid.NewGuid();
            Guid addressUid = Guid.NewGuid();
            const string hongkong = "Hong Kong";

            // create test data
            using (ISession session = sessionFactory.OpenSession())
            {
                Address a = new Address();
                a.AddressUid = addressUid;
                a.Line1 = hongkong;

                Individual i = new Individual();
                i.IndividualUid = individualUid;
                i.First = "Ken";
                i.Address1 = a;

                session.Save(a);
                session.Save(i);
                session.Flush();
            }

            // modify Individual and check profiler Address table should not be updated
            using (ISession session = sessionFactory.OpenSession())
            {
                Individual i = session.Get<Individual>(individualUid);
                i.Last = "Tong";
                System.Diagnostics.Debug.Assert(i.Address1.Line1 == hongkong, "Address1.Line1 is incorrect");

                session.Update(i);
                session.Flush();
            }
        }

        #region IDisposable Members

        public void Dispose()
        {
            try
            {
                if (sessionFactory != null)
                {
                    sessionFactory.Dispose(); // drop tables
                }
            }
            finally
            {
            }
        }

        #endregion
    }
}

The profiler result is

Code:

exec sp_executesql N'INSERT INTO dbo.Address (Line1, Line2, AddressUid) VALUES (@p0, @p1, @p2)',N'@p0 nvarchar(9),@p1 nvarchar(4000),@p2 uniqueidentifier',@p0=N'Hong Kong',@p1=NULL,@p2='BBEDA802-8FBD-4E84-B821-C3CE55EEECFE'
go
exec sp_executesql N'INSERT INTO dbo.Individual (First, Last, Address1Uid, Address2Uid, IndividualUid) VALUES (@p0, @p1, @p2, @p3, @p4)',N'@p0 nvarchar(3),@p1 nvarchar(4000),@p2 uniqueidentifier,@p3 uniqueidentifier,@p4 uniqueidentifier',@p0=N'Ken',@p1=NULL,@p2='BBEDA802-8FBD-4E84-B821-C3CE55EEECFE',@p3=NULL,@p4='7C740414-CC77-488A-ABFF-B3D738B874EF'
go
exec sp_executesql N'SELECT individual0_.IndividualUid as Individu1_0_2_, individual0_.First as First0_2_, individual0_.Last as Last0_2_, individual0_.Address1Uid as Address4_0_2_, individual0_.Address2Uid as Address5_0_2_, address1_.AddressUid as AddressUid1_0_, address1_.Line1 as Line2_1_0_, address1_.Line2 as Line3_1_0_, address2_.AddressUid as AddressUid1_1_, address2_.Line1 as Line2_1_1_, address2_.Line2 as Line3_1_1_ FROM dbo.Individual individual0_ left outer join dbo.Address address1_ on individual0_.Address1Uid=address1_.AddressUid left outer join dbo.Address address2_ on individual0_.Address2Uid=address2_.AddressUid WHERE individual0_.IndividualUid=@p0',N'@p0 uniqueidentifier',@p0='7C740414-CC77-488A-ABFF-B3D738B874EF'
go
exec sp_executesql N'UPDATE dbo.Individual SET Last = @p0 WHERE IndividualUid = @p1',N'@p0 nvarchar(4),@p1 uniqueidentifier',@p0=N'Tong',@p1='7C740414-CC77-488A-ABFF-B3D738B874EF'
go

You can download the test application here
http://www.box.net/shared/z2gfzexx1j

FIGDevelopers · **Joined:** Tue Sep 11, 2007 5:57 am **Posts:** 36

That's strange, it's working even here.
I will try and find what exactly has gone wrong.

FIGDevelopers · **Joined:** Tue Sep 11, 2007 5:57 am **Posts:** 36

I found out that it works fine when the session object is same, meaning

using (ISession session = sessionFactory.OpenSession())
{
Individual i = session.Get<Individual>(individualUid);
i.Last = "Tong";
System.Diagnostics.Debug.Assert(i.Address1.Line1 == hongkong, "Address1.Line1 is incorrect");

session.Update(i);
session.Flush();
}

Here you get the Individual's object, modify it and update it under one session ,and rightly so. But if you do this

Individual i = null;
using (ISession session = sessionFactory.OpenSession())
{
i = session.Get<Individual>(individualUid);
}

using (ISession session = sessionFactory.OpenSession())
{
i.Last = "Tong";
System.Diagnostics.Debug.Assert(i.Address1.Line1 == hongkong, "Address1.Line1 is incorrect");

session.Update(i);
session.Flush();
}

Then you will see that all columns of Individual get updated, including all columns of address also.
But this behaviour is probably acceptable, as the session object was different. My way of implementing is also similar:

I have a wrapper class

pubilc class DataAccess
{
#region Private Members
private DataStore _objdatastore;
private Hashtable _htbusinessentities;
#endregion

/// <summary>
/// Default Constructor
/// </summary>
protected internal DataAccess()
{
_objdatastore = new DataStore();
_htbusinessentities = new Hashtable();
}

/// <summary>
/// saves and commits the object in the
/// database.
/// </summary>
/// <param name="Obj"></param>
public void SaveAndCommit(object Obj)
{
_objdatastore.SaveAndCommit(Obj);
}
}

[Serializable]
public class DataStore : MarshalByRefObject
{
#region Private Members
private SessionFactory _objsessionfactory;
private ISession _session;
private ITransaction _tx = null;
#endregion

/// <summary>
/// Default Constructor
/// </summary>
public DataStore()
{
_objsessionfactory = SessionFactory.CreateInstance();
_session = _objsessionfactory.Factory.OpenSession();
}

/// <summary>
/// This method will insert/update and commit the
/// supplied object to the database
/// </summary>
/// <param name="Obj"></param>
public void SaveAndCommit(object Obj)
{
try
{
if (!_session.IsConnected)
_session.Reconnect();

_tx = _session.Transaction;

_tx.Begin();
_session.SaveOrUpdate(Obj);
_tx.Commit();
}
catch (Exception ex)
{
if(_tx != null)
_tx.Rollback();
throw ex;
}
finally
{
_tx = null;
}
}
}

Now on the client side, what I do is create an object of DataAccess, which has an object of DataStore as a data member(but remember this is a proxy as DataStore is remotely exposed object)

DataAccess objDataAccess = new DataAccess();
Individual Ind = objDataAccess.FindUnique<Individual>(1);
Ind.Last = "Xyz";
Ind.First = "Abc";
objDataAccess.SaveAndCommit(Ind);

Now this query updates all columns in Individual.
If I execute the same code without remotely exposing the DataStore object, everything works fine, only Last and First are updated.

Therefore it seems like each time the call goes to the server, the ISession object is treated as different, even when it was instantiated only once.
I am not sure this is related to Remoting or ISession object, but any thought on this will certainly help me.

Thanks.

canton · **Posted:** Mon Dec 17, 2007 2:52 am

I suspect different DataStore instances are created (at server) when FindUnique() and SaveAndCommit() are called. BTW, I don't have any solid experience on .net remoting.

[Off Topic] I can't see the implementation of SessionFactory.CreateInstance(). I hope it caches the real NH ISessionFactory. ISessionFactory should normally be created once.

FIGDevelopers · **Joined:** Tue Sep 11, 2007 5:57 am **Posts:** 36

Exactly the SessioFactory is created only once by the server.
The DataStore object has been marked as Client Activated , where in the proxy is returned for the first time when the MBR object's proxy is requested by any client. The DataStore is not instantiated on each method call as against SingleCall implementation.
Therefore new session is for sure not created on FindUnique and SaveAndCommit. I checked this by actualy debugging the code and found that session is created only once.

Neways thanks, and I will keep you posted if I figure out the solution.

bvanevery · **Joined:** Wed Aug 13, 2008 12:44 pm **Posts:** 1

In my situation, I made an update to the "Person" instance, but I was also seeing an update to the "Address" instance upon persistence of the Person.

It turned out that the issue arose from the fact that one of the setters in my "Address" class was making a change to the value originally set by Hibernate (the full details of this being a problem of its own with my test data), such that Hibernate detected a change in object state on the Address instance and consequently persisted that change.

So, in fact, the additional UPDATE query was justified and had nothing to do with the Person instance at all.