NHibernate One-to-many between View and Table


#1

Hi,

I have a View , mapped with hbm.xml:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="RegistoLib.Model.Impl" assembly="RegistoLib">
  <class name="v_View" table="v_View" proxy="Registo.Model.Impl.v_View" >
    <id name="Row" column="Row">
      <generator class="identity" />
    </id>
    <property name="Id" not-null="true"/>
    <property name="col1" not-null="false"/>
    <property name="col2" not-null="false" />
    <property name="col3" not-null="false" />
    <property name="col4" not-null="false" />
   
    <set name="RelatedTable" table="RelatedTable" inverse="false" lazy="true" cascade="none">
      <key column="RelatedId"/>
      <one-to-many class="RegistoLib.Model.Impl.RelatedTable, RegistoLib"/>
    </set>

  </class>
</hibernate-mapping>

I created a Set (name=“RelatedTable”) to try a one-to-many relation wit table RelatedTable. (No indexes)

Does not work. The related data is not loaded.

I think the problem is that the id column in the view is ROW.

Resume:
View v_View must have a one-to-many relation with table RelatedTable
Relation to be made between In v_View and in table RelatedTable

What is the correct way to do this.

Tkx,
Henrique


#2

Please add all the code for:

  • classes
  • mappings
  • SQL tables

that are involved in this relationship. Only then we can know what you are doing here.


#3

Hi,

Tkx for your reply.

Note: I can’t use the real names of tables and colums. If you find any incoherence probably is because when i build this example did something wrong. In real application everything is working except the one-to-many relation

Corrected my first post (introducing format).

I want to establish a one-to-many relation between column Id of View v_View
with column LivroId of table Livros (1 v_View can reference multiple Livros).

View definition:
CREATE VIEW [dbo].[v_View]
AS
Select  ROW_NUMBER() OVER(ORDER BY Id) row,a.*
From (Select f.Id
      ,f.col1
      ,f.col2
      ,f.col3
      ,f.col4
From table1 f

 <?xml version="1.0" encoding="utf-8" ?>
 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="RegistoLib.Model.Impl" assembly="RegistoLib">
   <class name="v_View" table="v_View" proxy="Registo.Model.Impl.v_View" >
     <id name="Row" column="Row">
       <generator class="identity" />
     </id>
     <property name="Id" not-null="true"/>
     <property name="col1" not-null="false"/>
     <property name="col2" not-null="false" />
     <property name="col3" not-null="false" />
     <property name="col4" not-null="false" />

  </class>
</hibernate-mapping>
    public interface Iv_View
    {
        long Row
        {
            get;
            set;
        }

        long Id
        {
            get;
            set;
        }

        string col1
        {
            get;
            set;
        }

        int? col2
        {
            get;
            set;
        }

        int? col3
        {
            get;
            set;

        }

        int? col4
        {
            get;
            set;

        }



    public class v_View:Iv_View
    {
        private long row;
        public virtual long Row
        {
            get { return row; }
            set { row = value; }
        }

        private long id;
        public virtual long Id
        {
            get { return id; }
            set { id = value; }
        }

        private string _col1= string.Empty;
        public virtual string col1
        {
            get { return _col1; }
            set { _col1 = value; }
        }

        private int? _col2;
        public virtual int? col2
        {
            get { return _col2; }
            set { _col2 = value; }
        }
        private int? _col3;
        public virtual int? col3
        {
            get { return _col3; }
            set { _col3 = value; }
        }
        private int? _col4;
        public virtual int? col4
        {
            get { return _col4; }
            set { _col4 = value; }
        }
    }
Table Definition:
CREATE TABLE [dbo].[Livros](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Letra] [nvarchar](1) NULL,
	[Folha] [int] NULL,
	[Verso] [bit] NOT NULL,
	[CriadoEm] [datetime] NOT NULL,
	[LivroId] [bigint] NOT NULL,
	[CriadoId] [bigint] NULL,
 CONSTRAINT [PK_Livros] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Livros] ADD  CONSTRAINT [DF_Livros_CriadoEm]  DEFAULT (getdate()) FOR [CriadoEm]
GO
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Registo.Model.Impl" assembly="RegistoLib">
  <class name="Livros" table="Livros" proxy="Registo.Model.Impl.Livros" >
    <id name="Id" column="Id">
      <generator class="identity" />
    </id>

    <property name="Letra" not-null="false"/>
    <property name="Folha" not-null="false" />
    <property name="Verso" not-null="false" />
    <property name="CriadoEm" not-null="true" />
    <property name="LivroId" not-null="false" />

    <many-to-one name="Criado" class="Registo.Model.Impl.Utilizador, RegistoLib" column="CriadoId" lazy="false"/>
    
  </class>
</hibernate-mapping>
namespace Registo.Model
{
    public interface ILivros
    {
        long Id
        {
            get;
            set;
        }

        string Letra
        {
            get;
            set;
        }


        int? Folha
        {
            get;
            set;

        }

        bool Verso
        {
            get;
            set;
        }

        DateTime CriadoEm
        {
            get;
            set;

        }

        long? LivroId
        {
            get;
            set;

        }

        IUtilizador Criado
        {
            get;
            set;

        }

    }
}




namespace Registo.Model.Impl
{
    public class Livros : ILivros
    {
        private long id;
        public virtual long Id
        {
            get { return id; }
            set { id = value; }
        }

        private string letra = string.Empty;
        public virtual string Letra
        {
            get { return letra; }
            set { letra = value; }
        }


        private int? folha;
        public virtual int? Folha
        {
            get { return folha; }
            set { folha = value; }
        }

        private bool verso = false;
        public virtual bool Verso
        {
            get { return verso; }
            set { verso = value; }
        }

        private DateTime criadoEm = DateTime.Now;
        public virtual DateTime CriadoEm
        {
            get { return criadoEm; }
            set { criadoEm = value; }
        }

        private IUtilizador criado;
        public virtual IUtilizador Criado
        {
            get { return criado; }
            set { criado = value; }
        }

        private long? livroId;
        public virtual long? LivroId
        {
            get { return livroId; }
            set { livroId = value; }
        }
    }
}

#4

This forum is just for Hibernate projects you will find on hibernate.org.

NHibernate is a separate project, and we don’t maintain it.

Here’s a post from the old forum which tells you how to reach the NHibernate forums.


nHibernate rows insertion performance issue
Questions regarding migration from MySQL to Postgresql
"new" nhibernate with dot net 3.5
Event Listener to log sql statements
Specify index on query
RelationShip N,M with property
Nhiberante map by code problem with UniqueKey("UQ_Name")
Fetch grand chidrens have duplicate rows
Nhiberante map by code problem with UniqueKey("UQ_Name")