How to add parent-child relations with BLToolkit

Question

i'd like to add some new records to a set of tables in a parent/child relationship by using BLToolkit. Unfortunately, i can't figure out what i'm missing, since after the commit, i only have the parent rows in my database.

When I inspect the Parent-Object before i add it to the parents-List, it contains the Child-Object like expected. I'm quite sure I'm missing something in the SQLQuery-Bit, but i don't know what.

Here are the things i've set up.

the sql code to create the tables

    CREATE TABLE [dbo].[PARENTS](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [SOME_VALUE] [int] NOT NULL,
        CONSTRAINT [PK_PARENT] 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]

    CREATE TABLE [dbo].[CHILDS](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [PARENT_ID] [int] NOT NULL,
        [SOME_TEXT] [nvarchar](50) NOT NULL,
        CONSTRAINT [PK_CHILD] 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]

    GO

    ALTER TABLE [dbo].[CHILDS]  WITH CHECK ADD  CONSTRAINT [FK_CHILD_PARENT] FOREIGN KEY([PARENT_ID])
    REFERENCES [dbo].[PARENTS] ([ID])
    GO

    ALTER TABLE [dbo].[CHILDS] CHECK CONSTRAINT [FK_CHILD_PARENT]

the classes

[TableName("PARENTS")]
public class Parent
{
    public Parent()
    {
        Children = new List<Child>();
    }

    [MapField("ID"), PrimaryKey, NonUpdatable]
    public int Id;

    [MapField("SOME_VALUE")]
    public int SomeValue;

    [Association(ThisKey = "ID", OtherKey = "PARENT_ID", CanBeNull = false)]
    public List<Child> Children;
}

[TableName("CHILDS")]
public class Child
{
    [MapField("ID"), PrimaryKey, NonUpdatable]
    public int Id;

    [MapField("SOME_TEXT")]
    public string SomeText;

    [Association(ThisKey = "PARENT_ID", OtherKey = "ID", CanBeNull = false)]
    public Parent Parent;
}

the code to insert the rows

using (DbManager db = new DbManager())
{
    db.BeginTransaction();
    SqlQuery<Parent> query = new SqlQuery<Parent>();

    IList<Parent> parents = new List<Parent>();
    for (int i = 0; i < 10; i++)
    {
        Parent parent = new Parent();
        parent.SomeValue = i;

        Child child = new Child();
        child.SomeText = i.ToString();

        parent.Children.Add(child);
        parents.Add(parent);
    }
    query.Insert(db, 10, parents);
    db.CommitTransaction();
}

score 2 · Answer 1 · answered Aug 07 '12 at 20:01

2

There is nothing wrong with your code. BLToolkit is a lightweight ORM, which means it does not keep track of the state of your objects. This is different from other ORMs like EF, L2SQL, NHibernate. In BLToolkit you work directly with the database. What you should do is:

Insert Parent and get it's identity.
Then Insert Child and set it the parent id.

Also, when you insert/update or delete do it inside the try/catch like this

using (DbManager db = new DbManager())
{
    try
    {
        db.BeginTransaction();

        // do something here...

        db.CommitTransaction();
    }
    catch
    {
        db.RollbackTransaction();
    }
}

answered Aug 07 '12 at 20:01

Mladen Macanović

1,099
7
17

Thank you for clearing this up. As an addition: is there any smart way to receive the ID right after inserting the parent-value? Or would i have to fire a second query like "SELECT @SCOPE_IDENTITY" in a second step? – BigBlackDog Aug 08 '12 at 07:51
You can do an InsertWithIdentity() see http://www.bltoolkit.net/Doc.LinqExtensions.ashx?HL=insertwithidentity#InsertWithIdentity_1 – David DV Aug 08 '12 at 11:10

How to add parent-child relations with BLToolkit

1 Answers1