Welcome to the CodeSmith Community!

Re: Parent/Child object structure, Foreign Keys and Indexes

Template Frameworks

A description has not yet been added to this group.

Parent/Child object structure, Foreign Keys and Indexes

Answered (Verified) This question is answered

I have a Parent/Child business object structure.  The parent has Info, InfoList and EditableRoot object types.  The Child has EditableChild and EditableChildList object types.  We also use a factory pattern so the Parent and Child objects each have a factory.  The factories are "Generate Once" files so manual factory methods can be added and not get wiped out with each generation.

I was having trouble generating my business object recently where the List factory methods were not being generated as expected; however 2 other similar business object sets had been generated just a few days before and I have something on the magnitutde of 500 business objects that all generate correctly.  What was special about these two?  Well, just so happens, we added some new indexing to our database to improve performance.  Each foreign key on our tables now have indexes to improve query performance. 

If I drop the indexes, my business objects generate as expected without error.  However, if I generate with the indexes in place, it does not build my List factory methods correctly.

The template for the Info and/or Editable object looks similar to this:

<% For Each sc as SearchCriteria In Entity.SearchCriteria(SearchCriteriaEnum.NoForeignKeys) %>

     Friend Function <%= sc.MethodName%>(<%= sc.Members.BuildParameters() %>)...

       ... The meat of the function; not really in question.

     End Function

<% Next %>

The template for List type factory methods looks like this:

<% For Each sc as SearchCriteria In Entity.SearchCriteria(SearchCriteriaEnum.ForeignKeys) %>

     Friend Function <%= sc.MethodName%>List(<%= sc.Members.BuildParameters() %>)...

       ... The meat of the function; not really in question.

     End Function

<% Next %>

When I have no indexes defined but proper ForeignKeys, meaning I get factory methods GetByID built off of the PrimaryKey and GetByParentIDList to fetch a list of child objects by their parent id.  However, if I add indexes, the GetByParentIDList methods show up as GetByParentID.  The ForeignKey's are picked up in the "NoForeignKeys" code instead of the "ForeignKeys" code.  It is as if by adding indexes that the ForeignKey is hidden and treated as if it is actually a Primary key.

Is this expected behavor in CodeSmith?  How do I add Indexing to improve DB performance and still get generation to work with FK and PK factory methods in the correct place?

I would copy and paste the template here, but I am not able.  DOD prevents transfer of data between development and this box that has email on it.

Thanks

  • Post Points: 65
Verified Answer
All Replies
  • Hello,

    Thanks for posting this. So to reproduce this you are lets say generating a er and erl and one should look at the erl for the generated search criteria... If i take a table that has an fk on it I should see a method generated for it. However, if I add an index to the same column that has an fk then it is generated improperly? Is this correct.

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

  • I would guess that is correct.  The ones I am seeing are EC and ECL specifically.  The FK methods are being generated as PK methods.

  • Thanks,

    I'll take a look and let you know what I find.

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

  • jasona22:
    I have something on the magnitutde of 500 business objects that all generate correctly

    Jason,

    Wow, that's an impressive volume of business objects! How do you manage so many?

    Sorry this is off topic, but it's been a while since I've dealt with that volume.

    Jamie Clayton
    Senior Application Developer
    Jenasys Design

  • Hello,

    I created the following database tables:

    • Order
    • OrderNote (with only a fk)
    • OrderNoteWithFKIndex (fk also has an index)
    CREATE TABLE [dbo].[Orders](
    	[OrderId] [int] IDENTITY(1,1) NOT NULL,
    	[OrderDate] [datetime] NOT NULL,
    	[Name] [varchar](80) NOT NULL,
    	[ShipAddr1] [varchar](80) NOT NULL,
    	[ShipAddr2] [varchar](80) NULL,
    	[TotalPrice] [decimal](10, 2) NOT NULL,
     CONSTRAINT [PK__Orders__C3905BCF7F60ED59] PRIMARY KEY CLUSTERED 
    (
    	[OrderId] 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
    
    CREATE TABLE [dbo].[OrderNote](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[OrderId] [int] NOT NULL,
    	[Comment] [ntext] NOT NULL,
     CONSTRAINT [PK_OrderNote] 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] TEXTIMAGE_ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[OrderNote]  WITH CHECK ADD  CONSTRAINT [FK_OrderNote_Orders] FOREIGN KEY([OrderId])
    REFERENCES [dbo].[Orders] ([OrderId])
    GO
    
    ALTER TABLE [dbo].[OrderNote] CHECK CONSTRAINT [FK_OrderNote_Orders]
    GO
    
    CREATE TABLE [dbo].[OrderNoteWithFKIndex](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[OrderId] [int] NOT NULL,
    	[Comment] [ntext] NOT NULL,
     CONSTRAINT [PK_OrderNoteWithIndex] 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] TEXTIMAGE_ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[OrderNoteWithFKIndex]  WITH CHECK ADD  CONSTRAINT [FK_OrderNoteWithIndex_Orders] FOREIGN KEY([OrderId])
    REFERENCES [dbo].[Orders] ([OrderId])
    GO
    
    ALTER TABLE [dbo].[OrderNoteWithFKIndex] CHECK CONSTRAINT [FK_OrderNoteWithIndex_Orders]
    GO
    
    CREATE NONCLUSTERED INDEX [IX_OrderNoteWithFKIndex] ON [dbo].[OrderNoteWithFKIndex]
    (
    	[OrderId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

     I then updated the SchemaHelper template to output the search criteria type.

    OrderNote
        Search Criteria
            GetByIdentification (PrimaryKey)
                Identification
            GetByOrderId (ForeignKey)
                OrderId
    OrderNoteWithFKIndex
        Search Criteria
            GetByIdentification (PrimaryKey)
                Identification
            GetByOrderId (Index)
                OrderId

    I then generated CSLA against these tables using a EC and ECL and diffed the generated content of the BO and BO Lists, they each had the same factory methods. I think that the current verion of SchemaHelper (latest nightly build's) handle this correctly in generation. However,  it is a bug that the Search Criteria for the OrderNoteWithFKIndex should have the enum type Index|ForeignKey or multiple search criterias. 

    I'm trying to think about this and need your feedback. Is there any scenario where you want two search criteria objects for the same column (Index + fk)? If the FK exists should we not add one for the index? What if you want to generate something specific for the index?

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

    Suggested by
  • I don't have easy access to getting latest builds, so we are stuck with whatever it was we downloaded when we updated to 6.  

    For my purposes, I don't care anything about the indexing with regards to factory methods.  Our factories are segmented using #Regions between EditableRoot, EditableRootList (which we don't use really), ReadOnlyRoot and ReadOnlyRootList.  Each of those regions build factory methods based on either the primary key or foreign keys.  So as written before, our template for non list objects (EditableRoot, ReadOnlyRoot, EditableChild) does a For Each sc as SearchCriteria in Entity.SearchCriteria(SearchCriteriaEnum.NoForeignKeys).  For the List version of the factory methods, just change the NoForeignKeys to ForeignKeys.  I only posted because I thought it might be unintended behavior.

    Since our factories are generate once, it isn't too bad to be able to generate the factory and then add the indexes to the table.  That is my work around for the problem I have seen.

    But, since it worked for you with the latest builds, it is probably something you have already fixed or perhaps it is related to that Bug you mentiond and we've got something custom that is letting it manifest itself.

    I do apprecite you looking at it.  Your scenario looks very much like I was describing (if not more concise than mine.  :) )

  • Hello,

    Thanks, I'll have a discussion with the team and see what they think and try to get this updated today.

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

  • Hello,

    This has been fixed in the latest nightly build. Here is the change log.

    Fixed a bug where IsColumnRowVersion() returned incorrect results for IBM DataTypes (community.codesmithtools.com/.../12609.aspx).

    Fixed a bug where GetSearchCriteria did not return the correct values in some circumstances.

    Removed SearchCriteria.SearchCriteriaDescription and implemented ToString() override.

    Added SearchCriteria.IsType that checks to see if a flag is on for the SearchCriteriaType. Just like PropertyType...

    Fixed a bug where an FK with an index wouldn't have the correct SearchCriteria.. (community.codesmithtools.com/.../48945.aspx). The SearchCriteria is now merged. For special casing please check IsType().

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

  • Sorry for the delayed response.  Thank you.  I haven't and can't get the "nightly build"; however once the powers that be deem it acceptable and get it for us, then I will try it out.

  • Hello,

    Please let me know what you find. This code will ship with 6.5 and a previous nightly build made it into the 6.5 RC (containing this).

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

Page 1 of 1 (11 items)