CodeSmith Community

Hello,

I have two tables:

|INVOICE    |
|-----------|
|InvoiceId  |
|DateCreated|


|INVOICEROWS|
|-----------|
|InvoiceId  |
|RowNumber  |
|TotalValue |


The red fields are primary key. Here's a relation between Invoice:InvoiceId and InvoiceRows:InvoiceId. The relation is one-to-many. When I generate tiers i expect to see Invoice.InvoiceRowsCollection but here is Invoice.InvoiceRows!
But, if I create a Rows table

|ROWS      |
|----------|
|RowNumber |

and set a relation between InvoiceRows:Rownumber and Rows:RowNumber then Invoice.InvoiceRowsCollection is created.
Why I must create Rows table? The one-to-many relation between Invoice and InvoiceRows should be valid anyway, even without a "Rows" table!

Nobody can check this?

Hi Omar, we're looking into the issue.  I will try and recreate this scenario and see if I get the same results.

Thanks for interesting. I had this problem on a big project database and recreated scenario on a little testdb. Here's the script, this can help your work:

CREATE DATABASE [TestDb]
GO

use [TestDb]
GO

CREATE TABLE [dbo].[Invoice] (
    [InvoiceID] [int] NOT NULL ,
    [DateCreated] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[InvoiceRows] (
    [InvoiceID] [int] NOT NULL ,
    [RowNumber] [int] NOT NULL ,
    [RowValue] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Rows] (
    [RowNumber] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Invoice] ADD
    CONSTRAINT [PK_Invoice] PRIMARY KEY  CLUSTERED
    (
        [InvoiceID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[InvoiceRows] ADD
    CONSTRAINT [PK_InvoiceRows] PRIMARY KEY  CLUSTERED
    (
        [InvoiceID],
        [RowNumber]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Rows] ADD
    CONSTRAINT [PK_Rows] PRIMARY KEY  CLUSTERED
    (
        [RowNumber]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[InvoiceRows] ADD
    CONSTRAINT [FK_InvoiceRows_Invoice] FOREIGN KEY
    (
        [InvoiceID]
    ) REFERENCES [dbo].[Invoice] (
        [InvoiceID]
    ) ON DELETE CASCADE ,
    CONSTRAINT [FK_InvoiceRows_Rows] FOREIGN KEY
    (
        [RowNumber]
    ) REFERENCES [dbo].[Rows] (
        [RowNumber]
    )
GO


This script creates relation between InvoiceRows and Rows. So in this configuration the generation WORKS. But if you delete relation between InvoiceRows and Rows NetTiers will not create InvoiceRowsCollection field.
NOTE: I'm using NetTiers 1.1 and I don't know if problem is on 2.0 too.

Thanks again,

Mario.

Still working on this one...

Ok, thanks..

I think you have a clustered index InvoiceID  + RowNumber on table InvoiceRows.

Your Pk should be  InvoiceID  + RowNumber and add new index RowNumber and use this index in your relation with Rows table, that should work

If I keep the Rows table the generation is always correct (Invoice contains InvoiceRowsCollection property). My problem is that I want to remove Rows table because it has no sense (it should only contains numbers,: 1,2,3,4,5,.. ). If I remove it then the business object Invoice is not correctly generated because contains InvoiceRows property in place of InvoiceRowsCollection .

the problem is, your schema, is fitting our rule for a 1:1 relationship, which is in CommonSqlCode:

public bool IsRelationOneToOne(TableKeySchema keyschema) //, PrimaryKeySchema primaryKey) { bool result = true; // if this key do not contain //if (keyschema.PrimaryKeyMemberColumns.Count != keyschema.PrimaryKeyTable.MemberColumns.Count) // return false; // Each member must reference a unique key in the foreign table foreach(ColumnSchema column in keyschema.ForeignKeyMemberColumns) { bool columnIsUnique = false; // the only way to find the key in the foreign table is to loop through the indexes foreach(IndexSchema i in keyschema.ForeignKeyTable.Indexes) { //The index must be unique and the numer of columns columns //in the FK must match the number of columns in the index if((i.IsUnique || i.IsPrimaryKey) && (keyschema.ForeignKeyMemberColumns.Count == i.MemberColumns.Count)) { //The index must contain the same column if(i.MemberColumns.Contains(column.Name) && (!IsJunctionTable(keyschema.ForeignKeyTable))) { columnIsUnique = true; } } } result = result && columnIsUnique; } return result; }

But I uncommented the lines: // if this key do not contain //if (keyschema.PrimaryKeyMemberColumns.Count != keyschema.PrimaryKeyTable.MemberColumns.Count) // return false; and it worked like you expect it to. I need to verify with the team member that made the change what the purpose was, but hopefully we can get it posted real soon. Thanks for your patience

I'm using NetTiers 1.1 now and cannot switch to 2.0. In version. In 1.1 the method is slightly different:

    ///<summary>
    ///    Returns whether or not a table key is a one to one
    /// relationship with another table.
    /// WARNING: Assumes first column is the FK.
    ///</summary>
    public bool IsRelationOneToOne(TableKeySchema keyschema)
    {
        foreach(IndexSchema i in keyschema.ForeignKeyTable.Indexes)
        {
            if((i.MemberColumns[0].Name == keyschema.ForeignKeyMemberColumns[0].Name) && (!IsJunctionTable(keyschema.ForeignKeyTable)))
            {
                if(i.IsUnique || i.IsPrimaryKey)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }   
        }
        return false;
    }

If I replace it with method you posted (that I suppose comes from NetTiers 2.0), template compiles except if I uncomment the two lines:

//if (keyschema.PrimaryKeyMemberColumns.Count !=keyschema.PrimaryKeyTable.MemberColumns.Count)
// return false;

in which case I get errors:

(2742,51): error CS0117: 'SchemaExplorer.TableSchema' does not contain a definition for 'MemberColumns'
(2121,3): error CS0618: 'System.Xml.Xsl.XslTransform.Transform(System.Xml.XPath.IXPathNavigable, System.Xml.Xsl.XsltArgumentList, System.Xml.XmlWriter)' is obsolete: 'You should pass XmlResolver to Transform() method'
(2239,5): error CS0162: Unreachable code detected

:(

Cheers,

Mario.

It needed the key.

if (keyschema.PrimaryKeyMemberColumns.Count !=keyschema.PrimaryKeyTable.PrimaryKey.MemberColumns.Count)
  return false

IT WORKS! I tested it on a rather complex DB (41 tables and a lot of relations). Four one-to-many relations has been corrected and all other relations has been left untouched (I used a file compare application to compare generated code before and after patch).

Thanks from me and the rest of team!! :-)

Excellent.  We will post it to the repository soon!

Hello Robert,

Sorry but I still don't see this change in snapshots.. The method definitely worked is this one:

 

        public bool IsRelationOneToOne(TableKeySchema keyschema)
        {
            bool result = true;

            // if this key do not contain
            if (keyschema.PrimaryKeyMemberColumns.Count !=keyschema.PrimaryKeyTable.PrimaryKey.MemberColumns.Count)
                        return false;
                       
            // Each member must reference a unique key in the foreign table
            foreach(ColumnSchema column in keyschema.ForeignKeyMemberColumns)
            {
            bool columnIsUnique = false;

            // the only way to find the key in the foreign table is to loop through the indexes
            foreach(IndexSchema i in keyschema.ForeignKeyTable.Indexes)
            {
                //The index must be unique and the numer of columns columns
                //in the FK must match the number of columns in the index
                if((i.IsUnique || i.IsPrimaryKey) && (keyschema.ForeignKeyMemberColumns.Count == i.MemberColumns.Count))
                {
                //The index must contain the same column
                if(i.MemberColumns.Contains(column.Name) && (!IsJunctionTable(keyschema.ForeignKeyTable)))
                {
                    columnIsUnique = true;
                }
                }
            }

            result = result && columnIsUnique;
            }

            return result;
        }

 

Thank you.. : )