Join Table Issue

Reply

tdupont replied on Thu, Jan 21 2010 1:59 PM

rated by 0 users

Hello,

The templates will not generate an entity for tables identified as being a many-to-many join table. This is because NHibernate does not require entities to manage the join rows.
If you have two tables, for example User and Role, and they are joined by UserRole, only the User and Role entities will be generated. If you add a Role object to the Role collection on the User object, NHibernate should add a row to the UserROle table automatically.

Thanks,
Tom

Reply

codrsmith replied on Fri, Jan 22 2010 3:25 AM

rated by 0 users

I understood this ... but the problem is: it is not working.

So I try to understand why. Can you give me a sample with 2 entities tables "A" and "B" and 1 join table "ABs" that, when generated with codesmith nhibernate template, works?

I need :

- DB creation script

- codesmith C# code to create objects A and B

- code to put in A's list of Bs the B

- code to save the A so that it automatically also save the B and a "link" row in tale ABs

Actually I was unsuccessfull to make this work.

Reply

codrsmith replied on Fri, Jan 22 2010 4:01 AM

rated by 0 users

Here's my actual test sample doing this.

1- the database

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'accessrights')

EXEC sys.sp_executesql N'CREATE SCHEMA [accessrights] AUTHORIZATION [myadminuser]'

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[accessrights].') AND type in (N'U'))

BEGIN

CREATE TABLE [accessrights].(

[Bid] [int] IDENTITY(1,1) NOT NULL,

[name] [varchar](10) NULL,

 CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED 

(

[Bid] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[accessrights].') AND type in (N'U'))

BEGIN

CREATE TABLE [accessrights].(

[Aid] [int] IDENTITY(1,1) NOT NULL,

[name] [varchar](10) NULL,

 CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED 

(

[Aid] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[accessrights].[ABs]') AND type in (N'U'))

BEGIN

CREATE TABLE [accessrights].[ABs](

[Aid] [int] NOT NULL,

[Bid] [int] NOT NULL

) ON [PRIMARY]

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[accessrights].[FK_AB_A]') AND parent_object_id = OBJECT_ID(N'[accessrights].[ABs]'))

ALTER TABLE [accessrights].[ABs]  WITH CHECK ADD  CONSTRAINT [FK_AB_A] FOREIGN KEY([Aid])

REFERENCES [accessrights]. ([Aid])

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[accessrights].[FK_AB_B]') AND parent_object_id = OBJECT_ID(N'[accessrights].[ABs]'))

ALTER TABLE [accessrights].[ABs]  WITH CHECK ADD  CONSTRAINT [FK_AB_B] FOREIGN KEY([Bid])

REFERENCES [accessrights]. ([Bid])

2- my hibernate.cfg.xml (set in copy always) :

<?xml version='1.0' encoding='utf-8'?>

<configuration>

<configSections>

<section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />

<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />

</configSections>

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">

<session-factory>

<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>

<property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>

<property name="connection.connection_string">Data Source=mydatawsource;Initial Catalog=mycatalog;Persist Security Info=True;User ID=myadminuser;Password=mypassword</property>

<property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>

<property name="show_sql">false</property>

<property name="proxyfactory.factory_class">NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>

<property name="use_outer_join">true</property>

<mapping assembly="ConsoleApplication1" />

</session-factory>

</hibernate-configuration>

<log4net>

<appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender, log4net">

<layout type="log4net.Layout.PatternLayout, log4net">

<param name="ConversionPattern" value="%m" />

</layout>

</appender>

<root>

<priority value="WARN" />

<appender-ref ref="ConsoleAppender" />

</root>

</log4net>

</configuration>

Reply

codrsmith replied on Fri, Jan 22 2010 11:39 AM

rated by 0 users

I tried anything I could:

- turning on/off lazy

- playing with inverse option

- specifying full class name

- specifying the join table ("ABs") instead of entities ones (A or B) in the bag definition

- adding generic to the bag definition

Nothing worked... I hope you have a solution for me.

Reply

tdupont replied on Mon, Jan 25 2010 1:31 PM

rated by 0 users

Hello,

Please try using the following...

static void Main(string[] args)
{
    IManagerFactory mgrFactory = new ManagerFactory();
    using (IAManager aMgr = mgrFactory.GetAManager())
    using (IBManager bMgr = mgrFactory.GetBManager())
    {
        A a = new A();
        a.Name = "a object";

        B b = new B();
        b.Name = "b object";

        a.Bs.Add(b);
        aMgr.Save(a);

        // Call CommitChanges to flush the session.
        aMgr.Session.CommitChanges();

        Console.WriteLine("complete");
        Console.ReadKey();
    }
} 

Thanks,
Tom 

Reply

codrsmith replied on Tue, Jan 26 2010 2:51 AM

rated by 0 users

First I am glad to announce you I have a license of CodeSmith 5.2 Pro! :o)

Nice reactivity from the team if you ask me!

By the way, it doesn't work out of the box yet. When you generate the bags in the mappings, there is an error. The table should not be

[accessrights]. or [accessrights].

it should be instead

[accessrights].[ABs]

in both A and B mappings. Please fix it and I will try again generated code out of the box.

Reply

codrsmith replied on Wed, Jan 27 2010 4:05 AM

rated by 0 users

It works on my simple A/B sample project. But when I try my real project I get some errors. It seems the Save cascade doesn't resolve the save order correctly. I get the following error:

"object references an unsaved transient instance - save the transient instance before flushing. Type: MyAssembly.BusinessObjects.MyDatatable, Entity:MyAssembly.BusinessObjects.MyDatatable"

Reply

codrsmith replied on Wed, Jan 27 2010 9:26 AM

rated by 0 users

Here is my DB and here's what I do to get this error:

            IManagerFactory mgrFactory = new ManagerFactory();

            IRManager rMgr = mgrFactory.GetRManager();

            IUManager uMgr = mgrFactory.GetUManager();

            ISTManager stMgr = mgrFactory.GetSTManager();

            U u = uMgr.GetUniqueByQuery("from U where Name='uName'");

            R r = new R();

            r.F = fMgr.GetUniqueByQuery("from F where Name='fName'");

            S s = new S();

            s.Name = "sName";

            s.ST = stMgr.GetUniqueByQuery("from ST where Name='stName'");

            r.S = s;

            u.Rs.Add(r);

            // uncomment next line to make the CommitChanges() work

            // sMgr.Save(s);

            uMgr.Save(u);

            uMgr.Session.CommitChanges();

So, first I got an error saying I should make Sid nullable in R in the Database, and when i did that, i got the error stated, that my s object should be saved before saving u object.... while I was expecting it to be cascaded.

Am I doing something wrong?

Reply

codrsmith replied on Wed, Jan 27 2010 9:45 AM

rated by 0 users

Ok, I made it working, here's the thing I had to modify in the mappings to make it work (it resolves both errors: the nullable field is no longer necessary and it's not necessary to save the S object in the code):

R.hbm.xml before:

....

<many-to-one name="F" column="[Fid]" class="F" />

<many-to-one name="S" column="[Sid]" class="S" />

...

R.hbm.xml after:

....

<many-to-one name="F" column="[Fid]" class="F" cascade="all" />

<many-to-one name="S" column="[Sid]" class="S" cascade="all" />

...

I don't know if it's intended or not, but no cascade here means problems at least for me :o)

Reply

tdupont replied on Wed, Jan 27 2010 11:26 AM

rated by 0 users

Hello,

I have made an update to the templates, in summary:

• Your Many-To-One collections should now default to a cascade value of collapse.
• Cascade is now configurable using the "cs_cascade" extended property. 

Please grab the latest nightly build (1330), and let me know how it goes.
http://community.codesmithtools.com/nightly/NHibernate/ 

For more details on the update, here is the code:

public static string GetCascade(ColumnSchema column, bool isOneToMany)
{
    var ep = column.ExtendedProperties["cs_cascade"];
    if (ep != null && ep.Value != null)
    {
        var eps = ep.Value.ToString();
        if (String.IsNullOrEmpty(eps))
            return eps;
    }

    if (isOneToMany)
        return column.AllowDBNull ? "all" : "all-delete-orphan";

    return column.AllowDBNull ? String.Empty : "all";
}

Thanks,
Tom 

Reply

codrsmith replied on Thu, Jan 28 2010 3:27 AM

rated by 0 users

I tried 1332... and most unit tests fail now with:

Failed: AssertionException: NHibernate.ObjectDeletedException: deleted object would re-saves by cascade (remove deleted objec

Reply

tdupont replied on Fri, Jan 29 2010 12:14 AM

rated by 0 users

Hello,

Thank you for continuing to work with me on resolving these issues.

I have reverted the default value for the ManyToOne cascade to none, so you will have to control your cascade associations using the cs_cascade extended property.
I have also made several much need updates to the Unit Tests, they should now be working better with the updated Session architecture, but there are still far from perfect.

In summary: the unit tests are still not perfect, and you will have to update the cascade using cascade properties.
Good luck with build 1338! http://community.codesmithtools.com/nightly/NHibernate/

Thanks,
Tom 

Reply

codrsmith replied on Fri, Jan 29 2010 4:00 AM

rated by 0 users

Tests failed with 1332 but code worked. Objects should auto cascade. A temporary extended property might help but....

If it has to be manually set, please add it to the csp "edit output" window properties in "Database options". While you are at it, it might be a good idea to also add a similar options choice for the datatables to force as entities and not join tables.

Remember, we have often not enough privileges to add/modify the extended properties of a datatable.

I tried 1338 too, and the unit tests are better now, though it raised me again the 2 errors I had prior to 1332.

Also I can't get the cascade to be generated. If I add cs_cacade with a value of true or a column name.... it doesn't do anything. I can't find a way to add an extended property just to a column either..... can you do the modification as requested upper here? it would be easier i guess?!?