Nhibernate Update associate table unexpected on session.CreateSQLQuery

Question

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

I have following XML mapping schemas:

Status

Workorder

WorkOrderStatus

and its POCO class are defined as below:

Status

public class Status {
    /* Private parameter of object*/
    private int _statusid;
    private string _statuscode ;
    private string _statusnote ;
    //private ISet _workorder_status = new HashedSet();

    /* 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
    {
        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 _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{
                get { return (_workorder_status); }
                protected set { _workorder_status = value; }
     }

    public WorkOrder(){ 
                this._workorder_status = new HashedSet();
        } 
}

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 += "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: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: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: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?

Nhibernate Update associate table unexpected on session.CreateSQLQuery

Answers (1)

Related Questions