Re: Nhibernate Update associate table unexpected on session.CreateSQLQuery

[kor]

i had a similar problem with firebirdsql for the conversion from timestamp type, try to make a test with a database that contains only entities with datetime roundend to seconds.

another "not very clean" solution is to add .SetReadonly(true) to your query so it will not check for dirty properties.

[Darren Kopp]

I believe that nhibernate will flush any pending changes before doing a query because those changes may affect the result of the query. I think there is a way to turn it off, but I don't exactly remember where it is. I think it's the FlushMode property on session or something like that, but may be controlled through configuration / session factory

On Thursday, July 26, 2012 7:25:13 PM UTC-6, Veasna MUCH wrote:

I have face a trouble while trying to load data to my GridView in ASP.NET page. I am quiet new to NHibernate and now try to use it as mapping tools.

I have following XML mapping schemas:

Status

<?xml version="1.0" encoding="utf-8" ?> 
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> 
 
  <class name="CMMS.BLL.Status, CMMS" table="tblStatus"> 
   
    <id name="StatusID" column="StatusID" unsaved-value="0"> 
     
      <generator class="identity" /> 
   
    </id> 
   
    <property name="StatusCode" column="StatusCode" /> 
   
    <property name="StatusNote" column="StatusNote" /> 
 
   
 
    <!-- 
    <set name="WorkOrderStatus" table="tblWorkOrderStatus" inverse="true" cascade="all-delete-orphan" fetch="join"> 
      <key column="WorkOrderID" /> 
      <one-to-many class="CMMS.BLL.WorkOrderStatus, CMMS" /> 
    </set> 
    -->
    <set name="WorkOrderStatus" table="tblWorkOrderStatus" inverse="true" cascade="all-delete-orphan" fetch="join"> 
      <key column="WorkOrderID" /> 
      <one-to-many class="CMMS.BLL.WorkOrderStatus, CMMS" /> 
    </set> 
    --> 
</class> 


 

Workorder

<?xml version="1.0" encoding="utf-8" ?>  
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> 
   
    <class name="CMMS.BLL.WorkOrder, CMMS" table="WorkOrder"> 
       
        <id name="ID" column="ID" type="Int32">  
           
            <generator class="identity" />  
       
        </id> 
       
        <property name="WOID" column="WOID" type="String" length="50" /> 
       
        <property name="WOReference" column="WOReference" type="String" length="50" />       
 
           
 
            <set name="WorkOrderStatus" table="tblWorkOrderStatus" inverse="true" cascade="all-delete-orphan"> 
               
                <key column="WorkOrderID" /> 
               
                <one-to-many class="CMMS.BLL.WorkOrderStatus, CMMS" /> 
         
         </set>  
   
    </class> 
</hibernate-mapping> 


 

WorkOrderStatus

<?xml version="1.0" encoding="utf-8" ?> 
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> 
 
  <class name="CMMS.BLL.WorkOrderStatus, CMMS" table="tblWorkOrderStatus"> 
   
    <id name="WSID" column="WSID" type="Int32" unsaved-value="0"> 
     
      <generator class="identity" /> 
   
    </id> 
 
   
 
    <property name="Comments" column="Comments" /> 
   
    <property name="LastModifiedOn" column="LastModifiedOn"  type="Timestamp" /> 
   
    <property name="CreatedBy" column="CreatedBy" /> 
 
   
 
    <many-to-one name="WorkOrder" class="CMMS.BLL.WorkOrder, CMMS" column="WorkOrderID" /> 
   
    <many-to-one name="Status" class="CMMS.BLL.Status, CMMS" column="StatusID" /> 
 
  </class> 
</hibernate-mapping>   


 

and its POCO class are defined as below:

Status

public class Status { 
   
    /* Tables fields definition */ 
   
    public const string STATUS_ID = "StatusID"; 
   
    public const string STATUS_CODE = "StatusCode"; 
   
    public const string STATUS_NOTE = "StatusNote"; 
 
 
   
 
 
    /* Private parameter of object*/ 
   
    private int _statusid; 
   
    private string _statuscode ; 
   
    private string _statusnote ; 
   
    //private ISet<WorkOrderStatus> _workorder_status = new HashedSet<WorkOrderStatus>(); 
 
   
 
    /* Public methode to access object*/ 
   
    public virtual int StatusID{ 
       
        get { return this._statusid; } 
       
        set { this._statusid  = value; } 
   
    }         
 
   
 
    public virtual string StatusCode{ 
       
        get { return _statuscode ; } 
       
        set { _statuscode  = value; } 
   
    } 
 
   
 
    public virtual string StatusNote{ 
       
        get { return _statusnote; } 
       
        set { _statusnote = value; } 
   
    } 
 
   
 
    /* 
    public virtual ISet<WorkOrderStatus> WorkOrderStatus 
    { 
        get { return (_workorder_status); } 
        protected set { _workorder_status = value; } 
    } 
     */
    public virtual ISet<WorkOrderStatus> WorkOrderStatus 
    { 
        get { return (_workorder_status); } 
        protected set { _workorder_status = value; } 
    } 
     */  
 
   
 
    /* Class Constructor */ 
   
    public Status() { } 
} 


 

WorkOrder

public class WorkOrder 
{ 
   
    private int _ID; 
   
    private string _WOID; 
   
    private string _WOReference;         
             
              private ISet<WorkOrderStatus> _workorder_status; 
 
 
   
 
 
    public virtual int ID 
   
    { 
       
        get { return this._ID; } 
       
        set { this._ID = value; } 
   
    } 
 
   
 
    public virtual string WOID 
   
    { 
       
        get { return this._WOID; } 
       
        set { this._WOID = value; } 
   
    } 
 
         
 
         public virtual string WOReference 
   
    { 
       
        get { return _WOReference; } 
       
        set { _WOReference = value; } 
   
    } 
 
   
 
    public virtual ISet<WorkOrderStatus> WorkOrderStatus{ 
               
                get { return (_workorder_status); } 
               
                protected set { _workorder_status = value; } 
     
     } 
 
   
 
    public WorkOrder(){  
               
                this._workorder_status = new HashedSet<WorkOrderStatus>(); 
       
        }  
} 


 

WorkOrderStatus

public class WorkOrderStatus  
 
 { 
 
     
 
     private int _wsid; 
     
     private string _comments;         
     
     private DateTime _lastmodifiedon; 
     
     private WorkOrder _workorder; 
     
     private Status _status; 
     
     private int _createdby; 
 
 
     
 
 
     public virtual int WSID { 
         
         get { return this._wsid; } 
         
         set { this._wsid = value; } 
     
     } 
 
     
 
     public virtual string Comments { 
         
         get { return this._comments; } 
         
         set { this._comments = value; } 
     
     } 
 
     
 
     public virtual DateTime LastModifiedOn{  
         
         get { return _lastmodifiedon; } 
         
         set { _lastmodifiedon = value; } 
     
     } 
 
     
 
     public virtual  WorkOrder WorkOrder { 
         
         get { return _workorder; } 
         
         set { _workorder = value; } 
     
     } 
     
     public virtual Status  Status { 
         
         get { return _status; } 
         
         set { _status = value; } 
     
     } 
 
     
 
     public virtual int CreatedBy { 
         
         get { return _createdby; } 
         
         set { _createdby = value; } 
     
     } 
 
 
     
 
 
     public WorkOrderStatus() { } 
 
 } 


 

But when I bind my GridView to ObjectDataSource to a method which execute the following statement:

string strQuery = "SELECT wo.*, st.*, ws.* FROM WorkOrder wo " + 
                       
                       "INNER JOIN ( " + 
                       
                       "             SELECT * " + 
                       
                       "             FROM tblWorkOrderStatus o1 " + 
                       
                       "             WHERE LastModifiedOn=( " + 
                       
                       "                                    SELECT TOP 1 LastModifiedOn " + 
                       
                       "                                    FROM tblWorkOrderStatus o2 " + 
                       
                       "                                    WHERE (o1.WorkOrderID = o2.WorkOrderID) " + 
                       
                       "                                    ORDER BY LastModifiedOn DESC) " + 
                       
                       ")ws ON wo.ID= ws.WorkOrderID " + 
                       
                       "INNER JOIN tblStatus st ON ws.StatusID= st.StatusID "; 
 
 
   strQuery
 
 
   strQuery += "ORDER BY wo.WOID"; 
 
 
 
 
IList  wo = session.CreateSQLQuery(strQuery) 
     
      .AddEntity("wo", typeof(WorkOrder)) 
                 
                 .AddEntity("st", typeof(Status)) 
         
          .AddEntity("ws", typeof(WorkOrderStatus))   
               
                .List(); 
return wo;    


 

I had a series of UPDATE to table tblWorkOrderStatus without any call from my function seen on SQL Server Profiler.

RPC:Completed   exec sp_executesql N'UPDATE CMMS.dbo.tblWorkOrderStatus SET Comments = @p0, LastModifiedOn = @p1, CreatedBy = @p2, WorkOrderID = @p3, StatusID = @p4 WHERE WSID = @p5',N'@p0 nvarchar(4000),@p1 datetime,@p2 int,@p3 int,@p4 int,@p5 int',@p0=NULL,@p1='2012-07-19 09:44:45.4600000',@p2=0,@p3=7223,@p4=1,@p5=104147    .Net SqlClient Data Provider         
RPC
RPC:Completed   exec sp_executesql N'UPDATE CMMS.dbo.tblWorkOrderStatus SET Comments = @p0, LastModifiedOn = @p1, CreatedBy = @p2, WorkOrderID = @p3, StatusID = @p4 WHERE WSID = @p5',N'@p0 nvarchar(4000),@p1 datetime,@p2 int,@p3 int,@p4 int,@p5 int',@p0=NULL,@p1='2012-07-19 09:44:45.4600000',@p2=0,@p3=7226,@p4=1,@p5=104148    .Net SqlClient Data Provider         
RPC
RPC:Completed   exec sp_executesql N'UPDATE CMMS.dbo.tblWorkOrderStatus SET Comments = @p0, LastModifiedOn = @p1, CreatedBy = @p2, WorkOrderID = @p3, StatusID = @p4 WHERE WSID = @p5',N'@p0 nvarchar(4000),@p1 datetime,@p2 int,@p3 int,@p4 int,@p5 int',@p0=NULL,@p1='2012-07-19 09:44:45.4600000',@p2=0,@p3=7234,@p4=1,@p5=104150    .Net SqlClient Data Provider         
RPC
RPC:Completed   exec sp_executesql N'UPDATE CMMS.dbo.tblWorkOrderStatus SET Comments = @p0, LastModifiedOn = @p1, CreatedBy = @p2, WorkOrderID = @p3, StatusID = @p4 WHERE WSID = @p5',N'@p0 nvarchar(4000),@p1 datetime,@p2 int,@p3 int,@p4 int,@p5 int',@p0=NULL,@p1='2012-07-19 09:44:45.4600000',@p2=0,@p3=7235,@p4=1,@p5=104151    .Net SqlClient Data Provider   

  


 

I do not know what exactly the problem is. It might be of my mapping files or something wrong to my database? Could you please help me? What can I do to manage this solution?

Best regards,