This project has moved. For the latest updates, please go here.

Child Collection Property Bug?

Apr 8, 2011 at 8:06 PM

Steps to reproduce:

I created a simple database 'Test' with two tables 'Parent' and 'Child1'
Parent
CREATE TABLE [dbo].[Parent](
 [ParentID] [int] IDENTITY(1,1) NOT NULL,
 [Description] [varchar](50) NULL,
 CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
 [ParentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Child1
CREATE TABLE [dbo].[Child1](
 [Child1ID] [int] IDENTITY(1,1) NOT NULL,
 [ParentIDFK] [int] NOT NULL,
 [Description] [varchar](50) NULL,
 CONSTRAINT [PK_Child1] PRIMARY KEY CLUSTERED
(
 [Child1ID] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Child1]  WITH CHECK ADD  CONSTRAINT [FK_Child1_Parent] FOREIGN KEY([ParentIDFK])
REFERENCES [dbo].[Parent] ([ParentID])
GO

ALTER TABLE [dbo].[Child1] CHECK CONSTRAINT [FK_Child1_Parent]
GO

Then I:

1. Open CGF.  Start New File
2. Connect to my Db
3. In Schema Objects right click on 'Parent' Table select 'Create Editable Root'
4. In Schema Objects right click on 'Child1' Table select 'Create Editable Root Collection'
5. In the dialog that pops up type 'Child1Collection' for 'Collection Name'
6. In the dialog that pops up type 'Child1' for 'Item Name'
7. Click on 'Child1Collection' change 'Csla Object Type' to 'Editable Child Collection'
8. For 'Child1Collection' in 'Criteria' Remove the autogenerated 'CriteriaGet'
9. For 'Child1Collection' in 'Parent Type' Set to 'Parent'
10. For 'Child1' in 'Value Properties' click on ParentIDFK and set 'FK Constraint' to 'FK_Child1_Parent'
11. For 'Parent' in 'Child Collection Properties' add a member called 'Child1List'
12. For 'Parent' in 'Child Collection Properties' set 'Type Name' to 'Child1Collection'


When I generate the code and run it like this:
Parent parent = Parent.NewParent();
            Child1 child1 = parent.Child1List.AddNew();
            parent.Description = "TESTING " + DateTime.Now;
            child1.Description = "TESTING " + DateTime.Now;
            parent.Save();

I get:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Child1_Parent". The conflict occurred in database "Test", table "dbo.Parent", column 'ParentID'.
The statement has been terminated.

Because it is trying to insert 0 into ParentIDFK instead of the value for ParentID

The generated Child_Insert Looks like this:
        /// <summary>
        /// Insert <see cref="Child1"/> object to database with or without transaction.
        /// </summary>
        internal void Child_Insert(Parent parent)
        {
            using (var ctx = TransactionManager<SqlConnection, SqlTransaction>.GetManager(Database.TestConnection, false))
            {
                using (var cmd = new SqlCommand("AddChild1", ctx.Connection))
                {
                    cmd.Transaction = ctx.Transaction;

                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Child1ID", ReadProperty(Child1IDProperty)).Direction = ParameterDirection.Output;
                    var args = new DataPortalHookArgs(cmd);
                    OnInsertStart(args);
                    DoInsertUpdate(cmd);
                    OnInsertPre(args);
                    cmd.ExecuteNonQuery();
                    OnInsertPost(args);
                    LoadProperty(Child1IDProperty, (int) cmd.Parameters["@Child1ID"].Value);
                    MarkOld();
                }
            }
        }
and the AddChild1 stored procedure looks like this:
CREATE PROCEDURE [AddChild1]
    @Child1ID int OUTPUT,
    @ParentIDFK int,
    @Description varchar(50)
AS
    BEGIN

        SET NOCOUNT ON

        /* Insert object into Child1 */
        INSERT INTO [Child1]
        (
            [ParentIDFK],
            [Description]
        )
        VALUES
        (
            @ParentIDFK,
            @Description
        )

        /* Return new primary key */
        SET @Child1ID = SCOPE_IDENTITY()


    END


If I modify both of these to look like this:

        /// <summary>
        /// Insert <see cref="Child1"/> object to database with or without transaction.
        /// </summary>
        internal void Child_Insert(Parent parent)
        {
            using (var ctx = TransactionManager<SqlConnection, SqlTransaction>.GetManager(Database.TestConnection, false))
            {
                using (var cmd = new SqlCommand("AddChild1", ctx.Connection))
                {
                    cmd.Transaction = ctx.Transaction;

                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ParentID", parent.ParentID); *****  Added this ********
                    cmd.Parameters.AddWithValue("@Child1ID", ReadProperty(Child1IDProperty)).Direction = ParameterDirection.Output;
                    var args = new DataPortalHookArgs(cmd);
                    OnInsertStart(args);
                    DoInsertUpdate(cmd);
                    OnInsertPre(args);
                    cmd.ExecuteNonQuery();
                    OnInsertPost(args);
                    LoadProperty(Child1IDProperty, (int) cmd.Parameters["@Child1ID"].Value);
                    MarkOld();
                }
            }
        }

CREATE PROCEDURE [AddChild1]
    @Child1ID int OUTPUT,
    @ParentID int, *****  Added this ********
    @ParentIDFK int,
    @Description varchar(50)
AS
    BEGIN

        SET NOCOUNT ON

        /* Insert object into Child1 */
        INSERT INTO [Child1]
        (
            [ParentIDFK],
            [Description]
        )
        VALUES
        (
            @ParentID, *****  Changed this ********
            @Description
        )

        /* Return new primary key */
        SET @Child1ID = SCOPE_IDENTITY()


    END

 

Basically, I'm glad that I can get this to work but I would like to know what I am not setting.  I've tried setting about 50 different this to get this to work right but have not found anything that works.  The list of steps is just the bare minimum to reproduce this.  I need to know if I am doing something wrong or if this is a bug that I should report.

Apr 11, 2011 at 11:43 AM
Edited Apr 11, 2011 at 11:46 AM

I usually don't use the FK stuff - just make sure your FK columns are named the same and use 'Parent Properties'

Basically that line of code

 cmd.Parameters.AddWithValue("@ParentID", parent.ParentID); *****  Added this ********

will generate if you go to 'Parent Properties' on your child collection (Choose child collection in Csla Object panel and then choose 04. Child Object Options in the Csla Object Info panel) and choose the foreign key field (ParentIDFK), but I always have problems that the generated code gives the wrong names to the columns in the sprocs - so to avoid this I just make tables like so:

[Parent]
ParentID
SomeOtherColumn

[Child]
ChildID
ParentID   (this is the FK - I make it the same name as the foreign table's join field)
SomeOtherStuff

I've not had any problems using this, but I can see where your frustrations lie!

Apr 12, 2011 at 12:16 PM

Thank you.  I needed to know that I was setting things up right and it was just a bug that was making things screwey.  I took a look and I fixed the bug in the templates that causes this.  I believe I have things fixed so that it picks up the foreign key field and you can name your fields what you want.  I will test it over the next couple days and if it's working I will package it up and send it to tiago this weekend.

Coordinator
Apr 13, 2011 at 1:46 AM

Hi farkmischer,

if you fixed a bug in the templates, can you contibute with the fix? Just create an issue and attach the corrected template. Please identify clearly what version of the templates it refers to.

Thanks

Tiago

Coordinator
May 1, 2011 at 11:47 PM
Edited May 1, 2011 at 11:59 PM
farkmischer wrote:

1. Open CGF.  Start New File
2. Connect to my Db
3. In Schema Objects right click on 'Parent' Table select 'Create Editable Root'
4. In Schema Objects right click on 'Child1' Table select 'Create Editable Root Collection'
5. In the dialog that pops up type 'Child1Collection' for 'Collection Name'
6. In the dialog that pops up type 'Child1' for 'Item Name'
7. Click on 'Child1Collection' change 'Csla Object Type' to 'Editable Child Collection'
8. For 'Child1Collection' in 'Criteria' Remove the autogenerated 'CriteriaGet'
9. For 'Child1Collection' in 'Parent Type' Set to 'Parent'
10. For 'Child1' in 'Value Properties' click on ParentIDFK and set 'FK Constraint' to 'FK_Child1_Parent'
11. For 'Parent' in 'Child Collection Properties' add a member called 'Child1List'
12. For 'Parent' in 'Child Collection Properties' set 'Type Name' to 'Child1Collection'

Never tryed the way you describe. But it seems it's missing something. Please try again but replace the steps in blue with the following:

4. In the Schema Objects panel, click on the child table

5. In the Columns panel, select the columns you want on the EditableChildCollection

5. In the Columns panel, right click and select Create => EditableChildCollection

6. In the dialog that pops up, fill in:

  • Collection Name
  • Item Name
  • Parent Type
  • Property Name in Parent Type

7. (nothing)

This is simpler to do and should auto fill everything that is needed.

Please note that - on the child table - you shouldn't select the parent ID column. Instead you should:

a) go to your new EditableChildCollection definition

b) have a look at 04. Child Object Options

c) check Parent Type is ok

d) fill in the Parent Properties with the parent ID property (the one that relates the child to the parent and loads the child collection)

e) go to the EditableChildCollection item definition and repeat b), c) and d)

f) for real childs (say flats of a building), check Parent Insert Only is true; for associative entities (N to N relations) check it is false.

 

I kow a couple of "How To"s are badly needed...

PS - For N to N concept and example, check http://groups.google.com/group/cslagenerator/browse_thread/thread/dcc5e2ff1d0978da?pli=1 and look for a post that is dated 2010 Jun 17 07:59 (the example is a Boys class and a Girls class)

May 25, 2011 at 8:54 PM

I tracked down my changes to fix this problem.

Okay the DoInsertUpdate.asp template in:

Template\CSLA40\CSharp

Looks like this :

        private void DoInsertUpdate(SqlCommand cmd)
        {
<%
if (Info.ConvertValueProperties.Count > 0)
{
    %>
            ConvertPropertiesOnWrite();
    <%
}
bool bHasTimeStamp = false;
%>
            var args = new DataPortalHookArgs(cmd);
<%
foreach (ValueProperty prop in Info.GetAllValueProperties())
{
    if (prop.DataAccess != ValueProperty.DataAccessBehaviour.ReadOnly &&
        prop.DataAccess != ValueProperty.DataAccessBehaviour.CreateOnly &&
        (prop.DataAccess != ValueProperty.DataAccessBehaviour.UpdateOnly || prop.DbBindColumn.NativeType == "timestamp"))
    {
        if (prop.DbBindColumn.ColumnOriginType != ColumnOriginType.None &&
            prop.PrimaryKey != ValueProperty.UserDefinedKeyBehaviour.DBProvidedPK)
        {

But should look like this:

        private void DoInsertUpdate(SqlCommand cmd)
        {
<%
if (Info.ConvertValueProperties.Count > 0)
{
    %>
            ConvertPropertiesOnWrite();
    <%
}
bool bHasTimeStamp = false;
%>
            var args = new DataPortalHookArgs(cmd);
<%
foreach (ValueProperty prop in Info.GetAllValueProperties())
{
    if (prop.DataAccess != ValueProperty.DataAccessBehaviour.ReadOnly &&
        prop.DataAccess != ValueProperty.DataAccessBehaviour.CreateOnly &&
        (prop.DataAccess != ValueProperty.DataAccessBehaviour.UpdateOnly || prop.DbBindColumn.NativeType == "timestamp"))
    {
        if (prop.DbBindColumn.ColumnOriginType != ColumnOriginType.None &&
prop.PrimaryKey != ValueProperty.UserDefinedKeyBehaviour.DBProvidedPK &&
prop.FKConstraint == "")

This checks if I have set a foreign key and does not include the parameter in the DoInsertUpdate because is should be included in the Child_Insert because it is a foreign Key.

With a foreign key set DoInsertUpdate was this:

private void DoInsertUpdate(SqlCommand cmd)

{ 

 

var args = new DataPortalHookArgs(cmd);

cmd.Parameters.AddWithValue("@ParentIDFK", ReadProperty(ParentIDFKProperty)).DbType = DbType 

.Int32;  

cmd.Parameters.AddWithValue("@Description", ReadProperty(DescriptionProperty) == null ? (object) DBNull.Value : ReadProperty(DescriptionProperty)).DbType = DbType

.String;  

}

 

 

 but is now this:

private void DoInsertUpdate(SqlCommand

cmd)

 

 

 

 

var args = new DataPortalHookArgs (cmd);

cmd.Parameters.AddWithValue("@Description", ReadProperty(DescriptionProperty) == null ? (object) DBNull.Value : ReadProperty(DescriptionProperty)).DbType = DbType 

.String;

}

 

{ 

 

Coordinator
May 25, 2011 at 10:20 PM
Edited May 25, 2011 at 10:22 PM

Hi farkmischer,

I did presume your parent reference (ParentIDFK) was named just like the parent PK (ParentID). CslaGenFork refuses to add the parent reference to the child collection but this check looks for name and data type match. If you rename ParentIDFK to ParentID and try the steps described above, you will notice the parent reference won't be added to the child Value Properties.

In fact, child objects can not include the parent reference as a Property Value just like Csla objects. You can read a lot of threads in the forums about this subject. All child objects have a Parent property that is used just like Charleh explained. You can also read about this in the Cheat Sheet.

Child Child1 Value Properties must be just the two below:

  • Child1ID
  • Description

Please remove ParentIDFK from Child1 Value Properties and re-generate.

May 26, 2011 at 3:17 PM

So what about a database that I am inheriting from another devloper that I can't change?  The example above is just a simple test database I created.  I can't remeber offhand if in the real database my Foreign Key was named the same as the parent ID.  I know in several databases I have inherited over the years this has not been the case.  Is there a way to tell CSLAGenFork if your legacy database does not follow the convention?  I'm all for convention as long as if you have an exception to the rule you can change the default.

I've spent some time trying things and I did find this:

If I keep the value property but change the ParentIDFK "Data Access Behavior" to "CreateOnly" the DoInsertUpdate generates correctly:

private void DoInsertUpdate(SqlCommand cmd)
        {
            var args = new DataPortalHookArgs(cmd);
            cmd.Parameters.AddWithValue("@Description", ReadProperty(DescriptionProperty) == null ? (object) DBNull.Value : ReadProperty(DescriptionProperty)).DbType = DbType.String;
        }

with the CreateOnly property the ParentIDFK does not seem to have Child1.ParentIDFK, which is what I want to happen, as you said this db field should not have a property on the object.

The Child_Insert looks like this however:

internal void Child_Insert(ParentItemFKDiff parent)
        {
            using (var ctx = TransactionManager<SqlConnection, SqlTransaction>.GetManager(Database.TestConnection, false))
            {
                using (var cmd = new SqlCommand("AddChild1FKDiff", ctx.Connection))
                {
                    cmd.Transaction = ctx.Transaction;

                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ParentID", parent.ParentID);
                    cmd.Parameters.AddWithValue("@Child1ID", ReadProperty(Child1IDProperty)).Direction = ParameterDirection.Output;
                    cmd.Parameters.AddWithValue("@ParentIDFK", _parentIDFK);
                    var args = new DataPortalHookArgs(cmd);
                    OnInsertStart(args);
                    DoInsertUpdate(cmd);
                    OnInsertPre(args);
                    cmd.ExecuteNonQuery();
                    OnInsertPost(args);
                    LoadProperty(Child1IDProperty, (int) cmd.Parameters["@Child1ID"].Value);
                    MarkOld();
                }
            }
        }

I assumes the ParentID is the same and also and also adds my real FK.  I did add my FK Constraint in the Value property editor.  I could write the logic to make this work but I don't know if this is the right place to specify this.

Should this instead be specified at "04. Child Object Options" with another property right under "Parent Insert Only" called map to Child FK or something.  You leave it blank for the default behavior of ParentID is same name as ChildIDFK and if they are not the same pick a child db field for the non default behavior??

May 26, 2011 at 3:26 PM

Or in the Value Property Collection Editor if "ForeignKeyOnly" was added to the dropdown I could pick that instead of "CreateOnly".  I could look for that in the template generator and override the convention if I see a property that has "ForeignKeyOnly" set.

May 26, 2011 at 4:22 PM

As an example I wrote this really quickly.  I'm not well versed in CSLA so if I am totally off base just let me know.

I added UserDefinedFK  to DataAccessBehaviour but now that I think about it maybe it should have gone under UserDefinedKeyBehaviour

public enum DataAccessBehaviour
{

ReadWrite,

ReadOnly,

WriteOnly,

UpdateOnly,

CreateOnly,

DBProvidedFK

}


if (parentType.Length > 0) 
{

foreach (Property prop in Info.ParentProperties) 
{

if (prop.PropertyType == TypeCodeEx.SmartDate) 
{

%>SmartDate l<%

= prop.Name %> = new SmartDate(parent.<%= 
prop.Name %>); 
cmd.Parameters.AddWithValue("@<%

= prop.ParameterName %>", l<%= 
prop.Name %>.DBValue); 
<%

}
else
{
Boolean ForeignKeyPropertyFound = false; 

string ForeignKeyFieldName = ""; 
foreach (ValueProperty FKprop in Info.GetAllValueProperties()) 
{
if (FKprop.DataAccess == ValueProperty.DataAccessBehaviour.DBProvidedFK) 
{

ForeignKeyPropertyFound = true; 
ForeignKeyFieldName = FKprop.ParameterName;

}

}
if (ForeignKeyPropertyFound) 
{

%>cmd.Parameters.AddWithValue("@<%

= ForeignKeyFieldName %>", parent.<%= 
prop.Name %>); 
<%

}
else
{

%>cmd.Parameters.AddWithValue("@<%

= prop.ParameterName %>", parent.<%= 
prop.Name %>); 
<%

}

}

}

}



 

 
Coordinator
May 27, 2011 at 6:58 AM
Edited May 27, 2011 at 9:44 PM
farkmischer wrote:

As an example I wrote this really quickly.  I'm not well versed in CSLA so if I am totally off base just let me know.


CslaGenFork is a complex program. Sometimes it seems it doesn't fit your needs but in fact you are using it the wrong way. I found that the hard way. This doesn't mean it doesn't need corrections and improvements; but that is a last resort.

[Note - I didn't make it. I just took an existing product with a long line of developers and forked the project.]

Using the tables you provide above as samples, everything is allright. Just follow the suggestion on my last message and remove by hand the FK property from the child collection.

Again, CslaGenFork is a complex program. Some of its features were conceived to work in a given way but somehow they now work in a different way. So the program concepts aren't entirely clear and different parts of the UI handle the same parts of code generation. That makes life hard to the end user. The fact that the end user is a programer doesn't mean he has to understand how the program works. [Edit - Let's rephrase it. Even programers must understand how a program work. The fact of being a programer doesn't mean one has to dive into the code and investigate how a program works.] The CGF Criteria concept is one of the most confusing parts. The interaction of Criteria with Data Access Behaviour isn't self evident. That's why I think documentation is badly needed. 

It's a know fact that CGF works best with proper tables with proper PK and proper FK relationships.

[Note - I think this is true for all code generation tools.]

Using views or tables without FK is a challenge. Your example tables follow both rules and the fact that automatic exclusion (and warning) doesn't work is due to the fact that the validation rule doesn't use FK but name match. This isn't the best way of doing it, but sure it's the easiest way. A correction is in order and a work item was added to solve it. The lack of program smartness in this area isn't critical as you can always use your own smartness to correct the the situation and get the proper result. That was my suggestion on the latest post.

Sometimes using tables is not an option since one must use an existing database that one isn't allowed to change. The "using views instead of tables" and related issues can be overcome with the provision for adding metadata by hand. I mean you should be able to use a CGF form to tell CGF about the missing PK and FK relationships. Kind'a converting a view data model into a comprehensive table data model.

May 27, 2011 at 12:55 PM

"Using the tables you provide above as samples, everything is allright. Just follow the suggestion on my last message and remove by hand the FK property from the child collection."

I want to make sure I understand.  Above you mean, Remove the FK value property and change the field names in the table.  CGF does not support tables with fields that are not named properly.

"[Note - I didn't make it. I just took an existing product with a long line of developers and forked the project.]"

I used the original CG and you are doing a great job with this fork.  It is so much better on so many levels.

"The fact that the end user is a programer doesn't mean he has to understand how the program works."

"The interaction of Criteria with Data Access Behaviour isn't self evident. That's why I think documentation is badly needed."

The reason I am posting so much trying to flesh this out is that I was hoping to understand how the program works and to write some documentation and give it to you to post.  I'm still trying to figure out to use CGF so that I can write something up.

Coordinator
May 27, 2011 at 9:50 PM
Edited May 27, 2011 at 9:53 PM
farkmischer wrote:

"Using the tables you provide above as samples, everything is allright. Just follow the suggestion on my last message and remove by hand the FK property from the child collection."

I want to make sure I understand.  Above you mean, Remove the FK value property  

 Correct

 

farkmischer wrote:

and change the field names in the table.

No need for that.

farkmischer wrote:

CGF does not support tables with fields that are not named properly.

It does support tables no matter how you name the columns. That particular "smart" feature wasn't implemented in smart enough way as it only looks for name and data type match. This will be corrected as you can see on work item #552 Thorough validation of parent properties for exclusion

Coordinator
Jun 3, 2011 at 12:00 AM

Hi farkmischer,

Concerning this issue, can you please try Change Set 7196 ? There should be a warning no matter how you name your columns. Please confirm.