Foreign Key Relationships

Hi,

Did you find a solution to this problem? I am running into a similar problem with my tables...

Does anyone know of a "fix"... or is this how .NetTiers works?

No.  I didn't find a solution.  My less than ideal workaround was to create a pseudo primary key.  I also posted this question that did get some feedback:

http://community.codesmithtools.com/forums/thread/8568.aspx

 

I'm kind-of surprised that others have not mentioned this bug/feature as it is breaks almost any database design that implements any 1-N foreign key relationships.

If I were better versed in .NET and codesmith I'd assist in getting a solution.  So at this point I still don't know if it is 'the way it is' or if it should be a bug.  I'm guessing it's a bug ...

I think you're confusing netTiers with your first example, where you have two columns in your OrderDetail PK, one of which points to the the PK of OrderHeader. This is by definition a 1:1 relationship. Your second example looks more like the standard 1:n. Any reason why you can't use that?

-- Tom

Why is it "by definition a 1:1 relationship"? 

I do a better job of explaining the issue here: http://community.codesmithtools.com/forums/thread/8568.aspx  But to save you the click here is my post from that thread.

--Cut--

--Cut--

 

tojo wrote:

I think you're confusing netTiers with your first example, where you have two columns in your OrderDetail PK, one of which points to the the PK of OrderHeader. This is by definition a 1:1 relationship. Your second example looks more like the standard 1:n. Any reason why you can't use that?

 

How is this 1:1? Because the second table has a composite PK... it actually makes it a 1:Many relationship. Other O/R mappers map this relatioship correctly (i.e. Wilson, LLBLGen...) only .NetTiers maps it incorrectly :(

I've found some more information on this issue.  I did a test with the Northwind DB and just generated the Orders and OrderDetails table.  The 1-N relationship is built correctly and a OrderDetailsCollection property (1:N) is available in the Orders entity. 

If you remove the Foreign key relationship from the ProductId column and regenerate the Orders entity now only has an OrderDetails property (1:1). 

So... I think this changes my original problem definition to:

If a child table column in a multi-column primary key participates in a 1:N foreign key relationship it will only be represented as a 1:N elationship in the generated Entity object only if ALL the other primary key columns in the child table also participate in foreign key relationship. 

 

Hmmm...  Doesn't the Orders and Order Details in Northwind match situation #1?  Orders contains a single column primary key (OrderID).  Order Details has a composite PK of OrderID and ProductID.  I just generated the EntityBase for the Orders table and I get an OrderDetailCollection as expected.  What version of the templates are you using?  I have tested with the latest CVS version of the .Net 2 CTP templates and the .Net 1.1 Caribert templates....

One other thing you could do is post your DDL scripts for the tables in question.  This will allow us to see exactly what the table, PK and FK relationships looks like.

I'm using 0.92 - the one listed on .NetTier's website. Is that the latest stable version?

If you are using .Net 1.1, then you should be using 0.9.2 (Caribert).  If you are using .Net 2.0, you should use the CTP version, which uses .Net 2.0 features (generics, nullable types, etc).  Regardless of which version you are using, I would strongly recommend that you download the latest nightly build or use a CVS tool (I use TortoiseCVS) to get the latest code bits.  The team makes fairly regular updates to the templates to fix issues that are found in the templates.  If you run into a bug, you should get the latest code to see if it has been resolved.  "Stable" is a bit of a tricky word...

Yes.  You are correct.  However in my last post I outlined new information I found which I think pin-points the issue.  In summary: 

"If a child table column in a multi-column primary key participates in a 1:N foreign key relationship it will only be represented as a 1:N elationship in the generated Entity object only if ALL the other primary key columns in the child table also participate in foreign key relationship. " 

Hmmm...  Doesn't the Orders and Order Details in Northwind match situation #1?  Orders contains a single column primary key (OrderID).  Order Details has a composite PK of OrderID and ProductID.  I just generated the EntityBase for the Orders table and I get an OrderDetailCollection as expected.  What version of the templates are you using?  I have tested with the latest CVS version of the .Net 2 CTP templates and the .Net 1.1 Caribert templates....

One other thing you could do is post your DDL scripts for the tables in question.  This will allow us to see exactly what the table, PK and FK relationships looks like.

---

Ben Johnson
------------------------------
 Member of the .NetTiers team
 Visit http://www.nettiers.com
------------------------------

 

 

Bear with me...  I still don't understand.  I have created a simple master child scenario (DDL below):

CREATE TABLE [dbo].[OrderMaster] (
 [ID] [char] (10)  NOT NULL ,
 [OrderName] [varchar] (50)  NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderMaster] WITH NOCHECK ADD
 CONSTRAINT [PK_OrderMaster] PRIMARY KEY CLUSTERED
 (
  [ID]
 ) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrderDetail] (
 [OrderLineNum] [int] NOT NULL ,
 [OrderID] [char] (10)  NOT NULL ,
 [LineAmount] [float] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderDetail] WITH NOCHECK ADD
 CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED
 (
  [OrderLineNum] ,
  [OrderID]
 ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderDetail] WITH NOCHECK ADD
 CONSTRAINT [FK_OrderDetail_OrderMaster] FOREIGN KEY
 (
  [OrderID]
 ) REFERENCES [dbo].[OrderMaster] (
  [ID]
 ) ON DELETE CASCADE ON UPDATE CASCADE
GO

Interesting... I wonder why it works for you... and not me!

 

I guess I need to do more testing on my end... should I post a DDL?

That would help...  Does it work if you use the Orders and Order Details tables in Northwind?

Ben,  You are right on the money.  Your understanding of the issue is correct.  In all my parent-child table relationships the 1:N mapping is not represented in .nettiers generated code.

I did some more digging:  I think we are getting closer to the issue.  If I modify your schema and re-order the sequence of the PK columns you no longer get the a 1:N OrderDetailCollection in the OrderMaster entity object.  You get a 1:1 mapping.

CREATE TABLE [dbo].[OrderMaster] (
 [ID] [char] (10)  NOT NULL ,
 [OrderName] [varchar] (50)  NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderMaster] WITH NOCHECK ADD
 CONSTRAINT [PK_OrderMaster] PRIMARY KEY CLUSTERED
 (
  [ID]
 ) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrderDetail] (
 [OrderLineNum] [int] NOT NULL ,
 [OrderID] [char] (10)  NOT NULL ,
 [LineAmount] [float] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderDetail] WITH NOCHECK ADD
 CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED
 (
  [OrderID],
  [OrderLineNum]
 ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderDetail] WITH NOCHECK ADD
 CONSTRAINT [FK_OrderDetail_OrderMaster] FOREIGN KEY
 (
  [OrderID]
 ) REFERENCES [dbo].[OrderMaster] (
  [ID]
 ) ON DELETE CASCADE ON UPDATE CASCADE
GO

 

This results in the the following code in the OrderMaster

private OrderDetail _OrderDetail = new OrderDetail();

/// <summary>

/// Holds a OrderDetail object

/// which is related to this object through the relation FK_OrderDetail_OrderMaster

/// </summary>

public OrderDetail OrderDetail

{

get { return _OrderDetail; }

set { _OrderDetail = value; }

}