NHibernate.ISQLQuery query = (NHibernate.ISQLQuery)session.CreateSQLQuery("SELECT {stock.Article.*}, {stock.Site.*}, SUM(QUANTITY) AS {stock.Quantity} " +
                    "FROM ( " +
                        "SELECT ARTICLE_ID, TO_PLACE_ID SITE,

NHibernate.ISQLQuery query = (NHibernate.ISQLQuery)session.CreateSQLQuery("SELECT {stock.*} " +
                    "FROM ( " +
                        "SELECT ARTICLE_ID, TO_PLACE_ID SITE,

            IList<Model.Consulting.Stock> result = new List<Model.Consulting.Stock>();
            NHibernate.ISession session = this.session_factory.OpenSession();
            NHibernate.ISQLQuery query = (NHibernate.ISQLQuery)session.CreateSQLQuery("SELECT {stock.Article.*}, {stock.Site.*}, SUM(QUANTITY) AS {stock.Quantity} " +
                    "FROM ( " +
                        "SELECT ARTICLE_ID, TO_PLACE_ID SITE, CAST(AMOUNT AS SIGNED INT) QUANTITY " +
                            "FROM STOCK_MOVEMENTS WHERE TO_PLACE_ID IS NOT NULL AND MOVEMENT_DATE < ? " +
                        "UNION ALL " +
                        "SELECT ARTICLE_ID, FROM_PLACE_ID SITE, CAST(-AMOUNT AS SIGNED INT) QUANTITY " +
                            "FROM STOCK_MOVEMENTS " +
                            "WHERE FROM_PLACE_ID IS NOT NULL AND MOVEMENT_DATE < ? " +
                    ") stock " +
                    "GROUP BY ARTICLE_ID, SITE").AddEntity("stock", typeof(Model.Consulting.Stock)).SetDateTime(0, date).SetDateTime(1, date);
            result = query.List<Model.Consulting.Stock>();
            session.Close();

            return result;

                NHibernate.ISession session = this.session_factory.OpenSession();
                NHibernate.ISQLQuery query = (NHibernate.ISQLQuery)session.CreateSQLQuery("SELECT {article.*}, {site.*}, SUM(QUANTITY) AS {stock.Quantity} " +
                        "FROM ( " +
                            "SELECT ARTICLE_ID, TO_PLACE_ID SITE, CAST(AMOUNT AS SIGNED INT) QUANTITY " +
                                "FROM STOCK_MOVEMENTS WHERE TO_PLACE_ID IS NOT NULL " +
                            "UNION ALL " +
                            "SELECT ARTICLE_ID, FROM_PLACE_ID SITE, CAST(-AMOUNT AS SIGNED INT) QUANTITY " +
                                "FROM STOCK_MOVEMENTS " +
                                "WHERE FROM_PLACE_ID IS NOT NULL " +
                        ") stock JOIN ARTICLE A ON stock.ARTICLE_ID = A.ID JOIN PLACE S ON stock.SITE = S.ID " +
                        "GROUP BY ARTICLE_ID, SITE")
                        .AddEntity("stock", typeof(Model.Consulting.Stock))
                        .AddJoin("article", "stock.Id.Article")
                        .AddJoin("site", "stock.Id.Site");
                result = query.List<Model.Consulting.Stock>();
                session.Close();

Public Class Category
    Inherits Entity

    Private _Description As String
   

    Public Overridable Property Description() As String
        Get
            Return _Description
        End Get
        Set(ByVal value As String)
            _Description = value
        End Set
    End Property

End Class

Public Class Supplier
    Inherits Entity

    Private _Code As String


    Public Overridable Property Code() As String
        Get
            Return _Code
        End Get
        Set(ByVal value As String)
            _Code = value
        End Set
    End Property

End Class

Public Class Product
    Inherits Entity

    Private _Name As String
    Private _Category As Category
    Private _Supplier As Supplier


    Public Overridable Property Name() As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = value
        End Set
    End Property

    Public Overridable Property Supplier() As Supplier
        Get
            Return _Supplier
        End Get
        Set(ByVal value As Supplier)
            _Supplier = value
        End Set
    End Property

    Public Overridable Property Category() As Category
        Get
            Return _Category
        End Get
        Set(ByVal value As Category)
            _Category = value
        End Set
    End Property

End Class

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" >

  <class name="Prod.Domain.Category, Prod.Domain" table="Category">

    <id name="Id" column="IdCategory" unsaved-value="0">
      <generator class="increment" />
    </id>

    <property name="Description" />

  </class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" >

  <class name="Prod.Domain.Supplier, Prod.Domain" table="Supplier">

    <id name="Id" column="IdSupplier" unsaved-value="0">
      <generator class="increment" />
    </id>

    <property name="Code" column="SupCode" />

  </class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" >
 
  <class name="Prod.Domain.Product, Prod.Domain" table="Product">
   
    <id name="Id" column="IdProduct" unsaved-value="0">
      <generator class="increment" />
    </id>

    <property name="Name" column="ProdName" />

    <many-to-one name="Category"
                 column="IdCategory"
                 class="Prod.Domain.Category, Prod.Domain"
                 />

    <many-to-one name="Supplier"
                 column="IdSupplier"
                 class="Prod.Domain.Supplier, Prod.Domain"
                 />
   
  </class>
</hibernate-mapping>

Imports NHibernate
Public Class ProductDao

    Protected _SessionFactory As ISessionFactory = NHibernateHttpModule.CurrentFactory()

    Public Function GetProductByName(ByVal name As String) As IList

        Dim sql As String = " SELECT prod.ProdName AS {product.Name}, " & _
                            " prod.IdProduct AS {product.Id}," & _
                            " cat.IdCategory AS {product.Category}," & _
                            " 1 AS {product.Supplier}," & _
                            " cat.IdCategory AS {category.Id}," & _
                            " cat.Description AS {category.Description}" & _
                            " FROM Product prod, " & _
                            " Category cat " & _
                            " WHERE prod.IdCategory = cat.IdCategory" & _
                            " AND prod.ProdName like :name "


        Return _SessionFactory.GetCurrentSession() _
                .CreateSQLQuery(sql) _
                .AddEntity("product", GetType(Product)) _
                .AddJoin("category", "product.Category") _
        .SetString("name", "%" + name + "%") _
        .List()
    End Function

End Class