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.


Specify index on query
RelationShip N,M with property
Nhiberante map by code problem with UniqueKey("UQ_Name")
Nhiberante map by code problem with UniqueKey("UQ_Name")