Hi,

I have noticed several times a very strange behavior in NH. Many times, when I generate the sql create script and run it from the list of persistent classes I have seen it adding in some tables a column named 'elt', which shouldn't be there. Deleting it from the database, solved the problem.

Now I have a problem when I load by PK an entity (Product) from the database, again adding the misterious 'elt' column. The error is:

Invalid column name 'elt'.

The product mapping is:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" schema="dbo">
<class name="BTBLL.Product, BTBLL" table="Product">
<id name="Id" type="Int32" column="ID" unsaved-value="0">
<generator class="identity" />
</id>
<property name="Active" column="Active" type="Boolean" />
<property name="AutoUpdate" column="AutoUpdate" type="Boolean" />
<property name="BTIRef" column="BTIRef" type="String" />
<property name="Code" column="Code" type="String" />
<property name="CountryOfManufactureCode" column="CountryOfManufactureCode" type="String" />
<property name="CountryOfOriginCode" column="CountryOfOriginCode" type="String" />
<property name="Description" column="Description" type="String" />
<property name="EndUserUndertaking" column="EndUserUndertaking" type="String" />
<property name="Euccn" column="EU_CCN" type="String" />
<property name="Eueccn" column="EU_ECCN" type="String" />
<property name="GroupID" column="GroupID" type="String" />
<property name="Notes" column="Notes" type="String" />
<property name="SupplierName" column="SupplierName" type="String" />
<property name="Usccn" column="US_CCN" type="String" />
<property name="Useccn" column="US_ECCN" type="String" />
<property name="USLicensable" column="USLicensable" type="Boolean" />
<property name="VatRate" column="VatRate" type="Decimal" />
<property name="Weight" column="Weight" type="Decimal" />

<many-to-one name="Licence" column="LicenceID" class="BTBLL.Licence, BTBLL" />
<bag name="OIELs" table="OIEL_Products" inverse="true" lazy="true" >
<key column="ProductID" />
<many-to-many class="BTBLL.OIEL, BTBLL" />
</bag>

</class>
</hibernate-mapping>

and when I try to load a product, I get the error. The sql generated is:

NHibernate :SELECT products0_.OIELID as OIELID__, products0_.elt as elt__, product1_.ID as ID0_, product1_.BTIRef as BTIRef0_, product1_.AutoUpdate as AutoUpdate0_, product1_.USLicensable as USLicen17_0_, product1_.SupplierName as Supplie14_0_, product1_.VatRate as VatRate0_, product1_.Weight as Weight0_, product1_.US_CCN as US_CCN0_, product1_.Active as Active0_, product1_.EU_ECCN as EU_ECCN0_, product1_.Notes as Notes0_, product1_.Code as Code0_, product1_.GroupID as GroupID0_, product1_.CountryOfOriginCode as CountryO7_0_, product1_.EU_CCN as EU_CCN0_, product1_.US_ECCN as US_ECCN0_, product1_.CountryOfManufactureCode as CountryO6_0_, product1_.LicenceID as LicenceID0_, product1_.EndUserUndertaking as EndUserU9_0_, product1_.Description as Descript8_0_, licence2_.ID as ID1_, licence2_.OIELID as OIELID1_, licence2_.LicenceTypeID as LicenceT2_1_, licence2_.[FreeText] as [FreeText1_], oiel3_.ID as ID2_, oiel3_.LicenceNumber as LicenceN2_2_, oiel3_.ApplicationReference as Applicat3_2_, oiel3_.ExpiryDate as ExpiryDate2_, oiel3_.Considerations as Consider4_2_, licencetyp4_.ID as ID3_, licencetyp4_.TypeName as TypeName3_ FROM dbo.OIEL_Products products0_ inner join dbo.Product product1_ on products0_.elt=product1_.ID left outer join dbo.[Licence] licence2_ on product1_.LicenceID=licence2_.ID left outer join dbo.OIEL oiel3_ on licence2_.OIELID=oiel3_.ID left outer join dbo.LicenceType licencetyp4_ on licence2_.LicenceTypeID=licencetyp4_.ID WHERE products0_.OIELID=@p0

Actually, products0_ is an "OIEL", can you provide its mapping (and the code you are executing)?

Hi,

Since the project I showed you is a commercial one, I made one to demonstrate the bug. The bug seems to appear with many to many relationships. You can download it at: http://www.geocities.com/danbunea/NHBug.zip

It is built on VS 2005. I have a User and UserGroup classes with a many to many relationship between them. I generate the sql script, the many to many table contains and elt column.

This is the generated script:

create table Users (UserID INT IDENTITY NOT NULL, Version INT not null, Canceled BIT not null, Name NVARCHAR(255) not null, Pass NVARCHAR(255) not null, primary key (UserID))

create table UserGroups (UserGroupID INT IDENTITY NOT NULL, Version INT not null, Canceled BIT not null, Name NVARCHAR(255) not null, Description NVARCHAR(255) not null, primary key (UserGroupID))

create table UsersGroups (GroupID INT not null, elt INT not null, UserID INT not null)

alter table UsersGroups add constraint FKEC3AF233983803017185C17C foreign key (elt) references Users
alter table UsersGroups add constraint FKEC3AF23398380301 foreign key (elt) references Users
alter table UsersGroups add constraint FKEC3AF2332BE07FEB foreign key (UserID) references UserGroups
alter table UsersGroups add constraint FKEC3AF233284FF848 foreign key (GroupID) references Users

The mapping file for User:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="Server.BusinessLayer.BusinessObjects.User, Server" table="Users">
<id name="Id" column="UserID" type="Int32" unsaved-value="0" access="nosetter.camelcase">
<generator class="identity">
</generator>
</id>
<version name="Version" column="Version" type="Int32" access="nosetter.camelcase"></version>
<property name="Canceled" column="Canceled" type="System.Boolean" not-null="true" access="nosetter.camelcase"/>
<property name="Name" column="Name" type="System.String" not-null="true"></property>
<property name="Pass" column="Pass" type="System.String" not-null="true"></property>
<bag name="Groups" table="UsersGroups" lazy="true" cascade="save-update">
<key column="GroupID"/>
<many-to-many class="Server.BusinessLayer.BusinessObjects.UserGroup, Server" />
</bag>
</class>
</hibernate-mapping>

The mapping file for UserGroup:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="Server.BusinessLayer.BusinessObjects.UserGroup, Server" table="UserGroups">
<id name="Id" column="UserGroupID" type="Int32" unsaved-value="0" access="nosetter.camelcase">
<generator class="identity">
</generator>
</id>
<version name="Version" column="Version" type="Int32" access="nosetter.camelcase"></version>
<property name="Canceled" column="Canceled" type="System.Boolean" not-null="true" access="nosetter.camelcase"/>
<property name="Name" column="Name" type="System.String" not-null="true"></property>
<property name="Description" column="Description" type="System.String" not-null="true"></property>
<bag name="Users" table="UsersGroups" >
<key column="UserID"/>
<many-to-many class="Server.BusinessLayer.BusinessObjects.User, Server" />
</bag>
</class>
</hibernate-mapping>

I have used NHibernate 1.0.

Thank you,
Dan

You may read http://www.hibernate.org/hib_docs/reference/en/html/collections.html#collections-ofvalues
There are some examples of <many-to-many> mapping...

Was there ever a sollution found for this problem because I am having the same problems ...

SQL: SELECT paymentsun0_.PersonId as PersonId__1_, paymentsun0_.elt as elt1_, paymentsun0_.RowGuid as RowGuid__1_, payment1_.PaymentId as PaymentId14_0_, payment1_.AccountNumber as AccountN2_14_0_, payment1_.AccountHolder as AccountH3_14_0_, payment1_.AmountADay as AmountADay14_0_, payment1_.Amount as Amount14_0_, payment1_.Collect as Collect14_0_, payment1_.ReferenceId as Referenc7_14_0_, payment1_.CollectDate as CollectD8_14_0_, payment1_.IsCollected as IsCollec9_14_0_, payment1_.FilePartNumber as FilePar10_14_0_, payment1_.SponsorTypeId as Sponsor11_14_0_, payment1_.InvoiceId as InvoiceId14_0_ FROM dbo.Person_Payment paymentsun0_ left outer join dbo.Payment payment1_ on paymentsun0_.elt=payment1_.PaymentId WHERE paymentsun0_.PersonId=?

mappings:
<class name="Emolife.Business.Person, Emolife.Business" table="Person">
<id name="PersonId" type="Int32" column="PersonId">
<generator class="identity" />
</id>

<joined-subclass name="Emolife.Business.Participant, Emolife.Business" table="Participant">
<key column="PersonId"/>
<property name="ParticipantType" column="ParticipantTypeId" type="Int32" />
<property name="DateOfBirth" column="DateOfBirth" type="DateTime" />
<property name="ProfessionOrStudy" column="ProfessionOrStudy" type="String" />
<property name="Motivation" column="Motivation" type="String" />


<idbag name="PaymentsUntyped" table="Person_Payment" lazy="true">
<collection-id column="RowGuid" type="Guid">
<generator class="guid"/>
</collection-id>
<key column="PersonId"/>
<many-to-many class="Emolife.Business.Payment, Emolife.Business"/>
</idbag>
</joined-subclass>
</class>

<class name="Emolife.Business.Payment, Emolife.Business" table="Payment">
<id name="PaymentId" type="Int32" column="PaymentId">
<generator class="identity" />
</id>
<property name="AccountNumber" column="AccountNumber" type="String" />
<property name="AccountHolder" column="AccountHolder" type="String" />
<property name="AmountADay" column="AmountADay" type="Decimal" />
<property name="Amount" column="Amount" type="Decimal" />
<property name="Collect" column="Collect" type="Boolean" />
<property name="ReferenceId" column="ReferenceId" type="Int32" />
<property name="CollectDate" type="DateTime" />
<property name="IsCollected" type="Boolean" />
<property name="FilePartNumber" column="FilePartNumber" type="Int32" />
<property name="SponsorType" column="SponsorTypeId" type="Int32" />
<many-to-one name="Invoice" column="InvoiceId" class="Emolife.Business.Invoice, Emolife.Business" lazy="false" />
<idbag name="AbstractPersonsUntyped" table="Person_Payment" lazy="true">
<collection-id column="RowGuid" type="Guid">
<generator class="guid"/>
</collection-id>
<key column="PaymentId"/>
<many-to-many column="PersonId" class="Emolife.Business.AbstractPerson, Emolife.Business" fetch="join"/>
</idbag>
</class>


class:

public class Participant : Person
{
protected IList paymentsUntyped = new ArrayList();

/// <summary>
/// Collection of all values including expired and future values.
/// </summary>
public virtual IList<Payment> Payments
{
get { return new ListWrapper<Payment>(paymentsUntyped); }
}

#region nhibernate



/// <summary>
/// Untyped version of the Payments property for NHibernate compatibility
/// </summary>
private IList PaymentsUntyped
{
get { return paymentsUntyped; }
set { paymentsUntyped = value; }
}

#endregion

"elt" is the column name (N)Hibernate uses when you don't specify the column attribute in the many-to-many tag. Basically, the ORM is just making crap up because you didn't explicitly tell it how to fulfill the relationship.

So your many-to-many should look something like this, assuming the OIELID column exists in your OIEL_Products table:


IMHO, the column attribute shouldn't be optional in the mapping schema if (N)Hibernate is just going to make weird stuff up at runtime, but it is what it is.