I have several tables where rows can be related to another row. I am using a self join to manage this. It is important that the "child" rows know who their parent is and parent and children are identical except that the parent has a column (attribute) that is null and all its children has this column set to the primary key value of its parent.

ItemKey int not null identity ParentItemKey int null data-- data | data ---- same for both parent and children data | . . . | data --

I am wondering what the best way is of handling this. I first thought of having two tables - parent and child and combining them by using a view. However I am starting to have far too many tables and I want to minimise the number. This concept occurs in a number of situations and in one of them I need two or more parent records to be collapsed back into one child (this is a special case)

Do you have any recommendations on how best to handle this with llblgen Pro

Currently the tables ( SQL Sever 2008 ) have a unique primary key (ItemKey) maintained by the system as indentity column. Children of a parent will have their own unique primary key (ItemKey) while children will all have their ParentItemKey value set to the parent's unique primary key while the parent will have this value set to null.

If I am using identity columns, can LLBLGen Pro handle this? Are their better ways such as 1. Using a singleton web service to provide keys and drop the use of identity columns 2. Use GUIDs

For both 1 and 2, I will update the entity collections with the key values myself before using a unit of work to save them back to the database. I am using Infragistics grids and I can use events to manage the keys.

Nothing is wrong with your current implementation, using an Identity PK. LLBLGen will perfectly handle this, same way it handles relations with other tables.

I suggest you go on and and produce some code, and come back if something stops you.

Ok Thanks

Currently I am still building my database schema. I just wanted to know if LLBLGen Pro could handle this or not.

I am not sure how one codes for this. As I want to persist all the rows within a transaction, how can I associate the child foreign keys with the parent's identity primary key before I use a DataAccessAdaptor to save the changes to the database. The parent's primary key is not known until it is inserted into the database. Also within one unitofwork I may several parents which may have from 0 to many children. In other words I do I mark which children belong to which parent if the primary identifier is still an unknown.

The only way I can think of doing this is to save each parent, obtain the primary key and then update the children.

Is there a code snippit that you have that would clarify this. If I use GUIDs for this relationship, I can generate these prior to the the database update and I have no problem with this. Also I could use a web service to generate the keys I need, again this is easy to understand (just more work).

Hi Peter, what you decribe is done thanks to PK-FK synchronization. For example see this example:

OrderEntity newOrder = new OrderEntity();
// set the fields of the newOrder entity
// ...

// -----------------------------------
// add childs

OrderDetailEntity orderDetail1 = new OrderDetailEntity();
// set orderDetail fields
// ...

// add the child to the parent.. remember that all this is done in memory
newOrder.OrderDetails.Add(orderDetail1);

// do the same with the other details.

// -----------------------------------
// now save all at once

//  using Adapter templateSet, you must specify that you are saving recursively
using (DataAccessAdapter adapter = new DataAccesAdapter())
{
     adapter.SaveEntity(newOrder, true);
}

// using SelfServicing
newOrder.Save(true);

As the join between _Order _and _OrderDetail _is the _OrderId field (_Order.OrderId <- OrderDetail.OrderId) and the Order.OrderId field is autocalculated (either by autoincrement, GUID, trigger, etc.) LLBLGen will catch that value and assign it to the childs, all at once in the recursive save. Cool eh! you don't have to worry about syncing the PK-FK yourself

Thanks for that.

I now need to find out how one handles this scenerio inside a grid bound to a entity collection. I assume that if one inserts a new row that has identity column, it sets the value to zero or null or something. When it is committed to the database, the database engine will set it to the appropriate value.

The question is how does one associate children to a parent in such a situation where children are joined to the parent through a self join. If there is only 1 parent with 0..n children that would not be a problem. If the grid contains several parents which then have 0..n children, how can one ensure that the correct children are associated with its parent.

Maybe this is a question for the grid vendor.

If I generate keys via a web service, then the dilemna does not exist.

he00273 wrote:

Maybe this is a question for the grid vendor.

Right. LLBLGen provides the PK-FK sync but the Grid provides the way of relate entities (parent - children) at GUI. With some GUI control vendors this is relatively easy. However, in general, this could be a pain. You must trick a little bit.

Please take a look at the GUI/Databinding examples to get an idea of how you could do that: http://llblgen.com/pages/examples.aspx

Thanks

I have had a look at the examples previously and got things to work ok if I use the standard

Table1 - child table 2 - child table 3 type relationship. Bit tricky with deletes as one has to keep track of these.

Is there an example which handles a table with a self join?

Possibly I can treat this as two conceptual tables by using entity inheritance.

You don't need inheritance here. The important question is how you'd handle this at the GUI level. How you'd want the end user to create a parent and children and associate them together.

I contacted the vendor (Infragistics) and they provide me with a simple working example plus reference links.

Of course their example was with datatable that was related to itself. I need to try this out with a entity collection. I think this will be ok. If not I can project it into a datatable but I doubt this will be necessary.

Its looking good now.

Thanks for the help. It is very much appreciated.

Its been a long time since I first posted this. Its now on my critical pathway.

Ok did what you suggested and it worked fine.

I created this very simple table

CREATE TABLE [dbo].[SelfTest]( [PrimaryKey] [int] IDENTITY(1,1) NOT NULL, [ParentKey] [int] NULL, [Data] nvarchar NULL, [RecordDate] [datetime] NOT NULL )

primary key is PrimaryKey

it has a foreign key relationship Foreign Key (ParentKey) References SelfTest(PrimaryKey)

When I run the generator, I get two entities

SelfTest and SelfTest_ so it works as

    private void btnGo_Click(object sender, EventArgs e)
    {
        SelfTestEntity newTest = new SelfTestEntity();
        newTest.Data = "parental data";

        // -----------------------------------
        // add childs

        SelfTestEntity newChildTest = new SelfTestEntity();
        newChildTest.Data = "childrens data";

        // add the child to the parent.. remember that all this is done in memory
        **newTest.SelfTest_.Add(newChildTest);**
        // do the same with the other details.

        // -----------------------------------
        // now save all at once

        // using Adapter templateSet, you must specify that you are saving recursively
        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
            adapter.SaveEntity(newTest, true);
        }

    }

Not sure how this works exactly but I am impressed that it does. After all that, my question is how can I change SelfTest_ to something a little more meaningful like SubSelfTest?

Ok. Please ignore my previous message.

Right Click and select (Rename F2)

Tested! Works fine !!

Spoke too soon

The infragistics example uses a standard dataset in which a relation is created thus

    private void Form1_Load(object sender, EventArgs e)
    {

        this.employeesTableAdapter.Fill(this.db2DataSet.Employees);
        this.db2DataSet.Relations.Add("rel1", this.db2DataSet.Tables[0].Columns["EmployeeNumber"], this.db2DataSet.Tables[0].Columns["ReportsTo"]);
        this.db2DataSet.Relations[0].Nested = true;

        this.ultraGrid1.DataSource = this.db2DataSet;

    }

I am trying to do the equivalent with an entitycollection

    private void btnPopulate_Click(object sender, EventArgs e)
    {
        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
            items = new EntityCollection(new SelfTestEntityFactory());
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.SelfTestEntity);
            prefetchPath.Add(SelfTestEntity.PrefetchPathSubSelfTest);

            adapter.FetchEntityCollection(items, new SelfTestEntity().GetRelationInfoSubSelfTest(), prefetchPath);

        }
        if (items.Count > 0)
            ultraGrid1.DataSource = items;
    }

How ever I get no entities in my entity collection so clearly I am doing something wrong (there is data in the table)

PrimaryKey ParentKey Data RecordDate 1 NULL parental data 2010-02-09 16:05:28.797 2 1 childrens data 2010-02-09 16:05:28.857 3 NULL parental data two 2010-02-09 16:26:01.390 4 3 childrens data two A 2010-02-09 16:26:01.450 5 3 childrens data two B 2010-02-09 16:26:01.450 6 NULL parental data Three 2010-02-09 16:32:08.840 7 6 childrens data Three A 2010-02-09 16:32:08.890 8 6 childrens data Three B 2010-02-09 16:32:08.890

The debug trace is

Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [selfjoin].[dbo].[SelfTest].[PrimaryKey], [selfjoin].[dbo].[SelfTest].[ParentKey], [selfjoin].[dbo].[SelfTest].[Data], [selfjoin].[dbo].[SelfTest].[RecordDate] FROM [selfjoin].[dbo].[SelfTest] WHERE ( ( [selfjoin].[dbo].[SelfTest].[ParentKey] = @ParentKey1)) Parameter: @ParentKey1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.

Method Exit: CreateSelectDQ

Method Exit: CreatePagingSelectDQ: no paging.

Method Enter: DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery

Method Enter: DataAccessAdapterBase.OpenConnection

Connection physically opened. Method Exit: DataAccessAdapterBase.OpenConnection Executed Sql Query: Query: SELECT [selfjoin].[dbo].[SelfTest].[PrimaryKey], [selfjoin].[dbo].[SelfTest].[ParentKey], [selfjoin].[dbo].[SelfTest].[Data], [selfjoin].[dbo].[SelfTest].[RecordDate] FROM [selfjoin].[dbo].[SelfTest] WHERE ( ( [selfjoin].[dbo].[SelfTest].[ParentKey] = @ParentKey1)) Parameter: @ParentKey1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.

Method Exit: DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery Method Exit: DataAccessAdapterBase.FetchEntityCollectionInternal(7) Method Enter: DataAccessAdapterBase.FetchPrefetchPath Method Exit: DataAccessAdapterBase.FetchPrefetchPath: No root objects. Method Enter: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.FetchEntityCollection( The thread 0xc80 has exited with code 0 (0x0).

Any help most appreciated.

I am attaching the generated entity class if thats any help

Such a steep learning curve

Ok I stuffed up. This works

    private void btnPopulate_Click(object sender, EventArgs e)
    {
        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
            items = new EntityCollection(new SelfTestEntityFactory());

            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.SelfTestEntity);
            prefetchPath.Add(SelfTestEntity.PrefetchPathSubSelfTest);
            IRelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(SelfTestFields.PrimaryKey > 0);

            //IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.SelfTestEntity);
            //prefetchPath.Add(SelfTestEntity.PrefetchPathSubSelfTest);

            adapter.FetchEntityCollection(items, filter, prefetchPath);
            //(items, new SelfTestEntity().GetRelationInfoSubSelfTest(), prefetchPath);

        }
        if (items.Count > 0)
            ultraGrid1.DataSource = items;
    }

Sorry to bother you but submitting a post often results in me working out the answer but only after submitting the post.

I am not sure whether this is a llblgen pro problem or a infragitsics problem

I am able to add new records to my entity collection

I am able to add new records to the collection via the UltraData Grid.

For an existing parent row I am unable to add new child rows

I get

Data Error

Unable to add a row:

Unable to add a new row. Underlying DataSource does not support adding new rows.

OK

I know that I can add new rows to the entity collection my self but is there any thing that might be blocking adding this to the grid.

prefetchPath.Add(SelfTestEntity.PrefetchPathSubSelfTest);

Is this relation a result of a m:n relation? If that is the case, such collection is read-only.

How does one test that?

I am sure its a 1:m

This code which adds entities directly to the collection works fine

  private void btnGo_Click(object sender, EventArgs e)
    {
        SelfTestEntity newTest = new SelfTestEntity();
        newTest.Data = "parental data Three";

        // -----------------------------------
        // add childs

        SelfTestEntity newChildTestA = new SelfTestEntity();
        newChildTestA.Data = "childrens data Three A";

        SelfTestEntity newChildTestB = new SelfTestEntity();
        newChildTestB.Data = "childrens data Three B";

        // add the child to the parent.. remember that all this is done in memory
        newTest.SubSelfTest.Add(newChildTestA);
        newTest.SubSelfTest.Add(newChildTestB);

        // do the same with the other details.

        // -----------------------------------
        // now save all at once

        // using Adapter templateSet, you must specify that you are saving recursively
        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
            adapter.SaveEntity(newTest, true);
        }

    }

I can do this repeatedly but this mimics adding a new parent plus new children in the grid.

I might do another test to see if I can add children to an existing parent.

I added this code

    private void btnSub_Click(object sender, EventArgs e)
    {
        int PrimaryKey;
        if (Int32.TryParse(tbParentItem.Text, out PrimaryKey))
        {
            foreach (SelfTestEntity row in items)
            {
                if ((row.PrimaryKey == PrimaryKey) && (!row.ParentKey.HasValue))
                {
                    var newChild = new SelfTestEntity();
                    newChild.Data = "this was added programatically";
                    row.SubSelfTest.Add(newChild);
                }
            }
        }
        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
            adapter.SaveEntityCollection(items, true, true);
        }
        ultraGrid1.DataSource = items;
    }

And this works fine. I am suspecting that it might be an infragistics problem as the children are displayed in a band under their parent but the top band (0) displays bit parents and children.

I created a new simple project using a vs2008 generated dataset using my self joined table.

I was able to create new parent and with new children and new children for existing parents in my table using an Infragistics UltraGrid.

There seems to be some impediment when using a LLGLGen Pro generated entity collection when inserting new children to an existing parent.

I apply a filter to stop showing child rows in the top level band.

With the simple dataset it works ok.

With the generated enity collection

• Add a new parent - Ok

• For the parent above add a new child - Ok (but I get two added)

• If I select an existing parent and try ad add a new child I get an error stating that the underlying datasource does not support the adding of new rows.

I think I will have to use a dataset with the grid and then use the table in the dataset to update the entity collection.

I have attached a word document with screen captures to show what is going on.

I'm not sure about Infragistics UltraGrid, and what kind of bindingSources are you using?

But what about trying to use normal MS .NET Grid and BindingSource Controls. Yes, you'll have to use 2 Grids, one for the Parent and another for its children (which by the way is better from the user experience point of view, rather than stuffing everything inside one grid).

I bet this approach will work, and if so, it might prove that it's not LLBLGen's issue after all.

Ok. But I am not sure how one sets the datasource of the second grid to the related entity collection of a self join.

    private EntityCollection items = null;
    private EntityCollection subitems = null;

    private void button1_Click(object sender, EventArgs e)
    {
        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
            items = new EntityCollection(new SelfTestEntityFactory());

            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.SelfTestEntity);
            prefetchPath.Add(SelfTestEntity.PrefetchPathSubSelfTest);
            IRelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(SelfTestFields.PrimaryKey > 0);

            adapter.FetchEntityCollection(items, filter, prefetchPath);

        }

        if (items.Count > 0)
        {
            dataGridView1.DataSource = items;
            // This does not work.
            subitems.SetContainingEntityInfo(items, "SubSelfTest");
            dataGridView2.DataSource = subitems;
        }
    }

With the SetContaingEntityInfo method I need an IEntity2 object not an entity collection.

I need to set the first grid to the entity collection and the second to entity records related to parent row.

With a dataset this is not hard to visualize. With LLBLGen I am not sure what to do. Any help here would be appreciated.

I was thinking of projecting the entity collection into a dataset (after I work out how to do that).

Tried to project the entitycollection into a dataset

        DataSet projection = new DataSet("Projection");
        if (items.Count > 0)
        {
            items.CreateHierarchicalProjection(projection);

            foreach (DataTable table in projection.Tables)
            {
                MessageBox.Show(table.TableName);
            }
        }

but got an exception

System.ArgumentException was unhandled Message="These columns don't currently have unique values." Source="System.Data" StackTrace: at System.Data.ConstraintCollection.AddUniqueConstraint(UniqueConstraint constraint) at System.Data.ConstraintCollection.Add(Constraint constraint, Boolean addUniqueWhenAddingForeign) at System.Data.DataTable.set_PrimaryKey(DataColumn[] value) at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase21.CreateHierarchicalProjectionInternal (List1 collectionProjections, DataSet destination, Dictionary2 entitiesPerType) at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase21.CreateHierarchicalProjection(DataSet destination) at SelfTest.twoGrids.button1_Click(Object sender, EventArgs e) in C:\SandBox\SelfJoin\SelfTest\SelfTest\twoGrids.cs:line 49 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods. IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(Form mainForm) at SelfTest.Program.Main() in C:\SandBox\SelfJoin\SelfTest\SelfTest\Program.cs:line 19 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException:

Purchased LLBLGen Pro to save time and effort (which I believe it will do) but at times it feels like reverting back to old ado.net code would help keep me sane and be faster.

I would not have thought someting this simple would prove to be so hard.

        DataSet projection = new DataSet("Projection");
        if (items.Count > 0)
        {
            items.CreateHierarchicalProjection(projection);

            foreach (DataTable table in projection.Tables)
            {
                MessageBox.Show(table.TableName);
            }
        }

This code is wrong.

Please check the Hierarchical projection example from the manual.

Describing the manual's example in details: Say you have a graph of Customer objects with their Orders and Employees, and you want to project this graph to DataTables inside a DataSet.

First you should setup projections per type.

List<IEntityPropertyProjector> customerProjections = EntityFields2.ConvertToProjectors(
        EntityFieldsFactory.CreateEntityFieldsObject(EntityType.CustomerEntity));

List<IEntityPropertyProjector> orderProjections = EntityFields2.ConvertToProjectors(
        EntityFieldsFactory.CreateEntityFieldsObject(EntityType2.OrderEntity));

List<IEntityPropertyProjector> employeeProjections = EntityFields.ConvertToProjectors(
        EntityFieldsFactory.CreateEntityFieldsObject(EntityType2.EmployeeEntity));

Build the ViewProjectionData list

List<IViewProjectionData> projectionData = new List<IViewProjectionData>();
projectionData.Add(new ViewProjectionData<CustomerEntity>(customerProjections));
projectionData.Add(new ViewProjectionData<OrderEntity>(orderProjections));
projectionData.Add(new ViewProjectionData<EmployeeEntity>(employeeProjections));

Perform the projection:

DataSet result = new DataSet("projectionResult");
customers.CreateHierarchicalProjection(projectionData, result);

Hope this was helpful.

Ok I know that my understanding is wrong.

The difficulty I have is that the example in the documentation follows the typical model of related tables. In the example Customers is related to Orders. Conceptually that is not difficult to understand and I must admit that it is the most prevalent relationship in databases.

What I have in this instance is a table that has a self join. This is also quite common in databases and in my case I have a number as this meets our business needs. I have an items table to which I need to associate a number of sub items. I need to preserve the relationship but also be able to treat items and sub items when processing requires it as the same entity (an item)

In my Entity class generated by llblgen pro, I have the entity selftest and the one that is related to selftest as subselftest

I have this code so far. I have not gone as far to projecting the entity collections into a dataset.

private EntityCollection<SelfTestEntity> items = new EntityCollection<SelfTestEntity>();

    private void button1_Click(object sender, EventArgs e)
    {
        IPrefetchPath2 prefetchPath = new PrefetchPath2((int) EntityType.SelfTestEntity);
        prefetchPath.Add(SelfTestEntity.PrefetchPathSubSelfTest);
        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {

            IRelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(SelfTestFields.ParentKey == DBNull.Value);

            adapter.FetchEntityCollection(items, filter, prefetchPath);

        }

        dataGridView1.DataSource = items;
        dataGridView2.DataSource = items[0].SubSelfTest;

    }

    private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
    {
        dataGridView2.DataSource = items[e.RowIndex].SubSelfTest;
    }

OK that works for displaying data in two datagridviews - some progress at last I will now look at the dataset projection

Ok

List<IEntityPropertyProjector> customerProjections = EntityFields2.ConvertToProjectors( EntityFieldsFactory.CreateEntityFieldsObject(EntityType.CustomerEntity));

List<IEntityPropertyProjector> orderProjections = EntityFields2.ConvertToProjectors( EntityFieldsFactory.CreateEntityFieldsObject(EntityType2.OrderEntity));

List<IEntityPropertyProjector> employeeProjections = EntityFields.ConvertToProjectors( EntityFieldsFactory.CreateEntityFieldsObject(EntityType2.EmployeeEntity));

One - There is no EntityType2 in my version of LLBLGen Pro 2.6. Is there a new version?

Two - I can use

            List<IEntityPropertyProjector> itemProjections = EntityFields2.ConvertToProjectors(
                EntityFieldsFactory.CreateEntityFieldsObject(EntityType.SelfTestEntity));

But I do not have a EntityType.SubSelfTestEntity. So how do I proceed? Sorry to be a pain but I can't imagine myself being the only one in this situation.

he00273 wrote:

One - There is no EntityType2 in my version of LLBLGen Pro 2.6. Is there a new version?

It's a typo in the code snippet. It should be EntityType.

he00273 wrote:

Two - I can use

            List<IEntityPropertyProjector> itemProjections = EntityFields2.ConvertToProjectors(
                EntityFieldsFactory.CreateEntityFieldsObject(EntityType.SelfTestEntity));

But I do not have a EntityType.SubSelfTestEntity. So how do I proceed? Sorry to be a pain but I can't imagine myself being the only one in this situation.

You just need to create one item projectors. The CreateHierarchicalProjection method will figure it out.

(Edit) Reproduced. You can't create a Hierarchical Projection from a collection with a self join.

Since you resolved the two-grid problem, I don't see why exactly you need this projection.