Ok Code is

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

            List<IViewProjectionData> projectionData = new List<IViewProjectionData>();
            projectionData.Add(new ViewProjectionData<SelfTestEntity>(itemProjections));

            items.CreateHierarchicalProjection(projectionData, projection);

           // Think the Primary key is set already - need to check to make sure.
           // If not this is how you set it

            //DataColumn[] keyColumns = new DataColumn[1];
            //keyColumns[0] = projection.Tables[0].Columns["PrimaryKey"];
            //projection.Tables[0].PrimaryKey = keyColumns;

           // Missing Autoincrement stuff - add it here

            projection.Tables[0].Columns["PrimaryKey"].AutoIncrement = true;
            projection.Tables[0].Columns["PrimaryKey"].AutoIncrementSeed = -1;
            projection.Tables[0].Columns["PrimaryKey"].AutoIncrementStep = -1;

            //dataGridView1.DataSource = projection.Tables[0];

            ultraGrid1.DataSource = projection[/color];

Result is that is that works - Yah!!!!!!!!!!!!!!!!!!!!!!!

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

** Why not ????????**

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

Because I wanted to use a hierarchical grid for those instances where I have a table with a self join. See Attachment.

Now all I have to do is process the table and insert the new rows and update those that have changed. I use something like this code

            SelfTestEntity newTest = new SelfTestEntity();
            newTest.Data = "Mother";

            // -----------------------------------
            // add children

            SelfTestEntity newChildTestA = new SelfTestEntity();
            newChildTestA.Data = "Baby A ";

            SelfTestEntity newChildTestB = new SelfTestEntity();
            newChildTestB.Data = "Baby B";

            SelfTestEntity newChildTestC = new SelfTestEntity();
            newChildTestC.Data = "Baby C";

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

            // 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);
            }

Its a pain that the entitycollection cannot be used as a datasource to my grid and allow me to insert new records against existing parents. I can display the records ok. I can insert a new parent and then new children. If I try to insert a new child to an existing parent I get an exception.

--------------------------
Data Error
---------------------------
Unable to add a row:

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

Its a bit more work but it is doable (at least I believe so)

There are a couple of things: 1) 'EntityType' is the enum which defines the entity types. So you have 1 table (the 'SelfTest' table in your example), so there's an entry in EntityType for SelfTest, as SelfTest is an entity. 2) a relationship with itself results in a 1:n and a m:1 relationship. This also means that there are two fields in the entity mapped onto these, one on the 1:n part and one on the m:1 part. These are the SelfTest and SelfTest_ fields in the entity 'SelfTest'. So these are not entities, these are fields to refer to entity instances, so an instance of the SelfTestEntity. This is the same as Northwind.Employee: it has a field 'ReportsTo', which is an Fk to Employee.employeeid. This means Employee has two fields mapped onto these relations: 1 on the 1:n part (which could be called 'ManagesEmployees') and one on the m:1 part ('Manager').

This is typically the textbook example of storing a tree in a database. The problem is that you can't fetch a part of the tree in 1 query, as the tree has to be interpreted in memory to a full tree (assigning the child with the parent). If you just have 1 level, so item and subitems, it's not a big deal: use a prefetch path and the sub items are fetched. The 'sub items' aren't different entities though, they too are 'Item' entity instances. They're just called 'sub items' because they refer to an entity over a m:1 relationship which is semantically interpreted as 'child refers to parent' (employee refers to manager).

Creating a hierarchical projection isn't for this kind of data, it's for creating a dataset from a graph of entities. A relationship with itself causes a problem as it creates an infinite loop hence we don't support it.

If you want to create the tree back without prefetch paths, use a dictionary lookup: sort the entity collection of items on 'parent' ascending, then on id ascending. This should give the root at the top (as it has NULL for parent). Traverse the collection from front to back: the element is refering to a parent, so you look up the entity using the dictionary which stores the PK value as key and the entity as value. This quickly gives you a way to create the tree back from a flat list.

About the oddness of not being able to store a new entity in the collection: did this occur when you bound the projection result (which is a dataset) to the grid, or the collection? If it's the collection, did you try creating an EntityView2 instance yourself and bind that instead? An entity collection binds though its default view (which is a new EntityView2 instance) to the grid. The view by default exposes a field called 'AllowNew', this should be true (as it is determined from the readonly flag of the collection, which is false unless you bind a m:n based collection)

If you just have 1 level, so item and subitems, it's not a big deal: use a prefetch path and the sub items are fetched.

I have deliberately limited any table that we have with a self join to be no more than one level deep.

In a case of car parts at times it necessary to know what parts are composed of other parts but at other times be able to treat all of these as the same entity. A car is made up of parts; wheels, doors, seats etc. Each of these parts is again can made up of other parts. A car door is made up of parts such as a metal skin, glass window, winding mechanism, handle etc. These can again can be made up of parts etc.

One at times needs to know what parts are made up of other parts so one can obtain all the required sub parts to make that part. At other times one needs to be able to consider all of these as parts as simple entities so one can do some form of stock control. We are low on car doors, we have too many car door handles etc.

Fortunately we do not need to go to that depth.

The 'sub items' aren't different entities though, they too are 'Item' entity instances.

If they were different entities we would be using two tables and be relating them together. It is the need to manage all items as items which resulted in me going down this path while still knowing if an item was related to a parent item.

Perhaps some form of Entity inheritance should be considered

Supertypes / subtypes and NIAM terminology In relational models, entities can derive from another entity, for example to specialize the definition of that other entity. Typically the derived entity is called a subtype and the entity derived from is called a supertype. LLBLGen Pro uses this same terminology. An entity which has subtypes, is called a supertype, and an entity which is a derived entity is called a subtype. In the following sections, two hierarchy types are described which are typical for many situations. The hierarchy types are illustrated with a (simplified) NIAM / ORM (object role modelling: http://www.orm.net). To help the readers who aren't familiar with NIAM and / or ORM, the following brief description should help understand the presented diagrams.

Creating a hierarchical projection isn't for this kind of data, it's for creating a dataset from a graph of entities. A relationship with itself causes a problem as it creates an infinite loop hence we don't support it.

On the other had it did work, possibly because its only 1 level deep. What you said causes me concern as it may break with newer versions of LLBLGen Pro.

Actually just getting the entity collection into dataset was my objective. If using a hierarchical projection is inappropriate, what other mechanisms are there (aside from looping through the entity collection and building the dataset that way. Currently it appears easier to just to use ADO.NET (at the expense of database independance) to create the dataset and then use LLBLGen Pro to handle the resulting updates/deletes in code.

I would prefer to just use the entity collection as a datasource to my hirearchial grid. This is my prefered objective. Projecting it into a dataset was only considered as it enabled me to do what I need to do. Its actually a pain to have a hybrid arrangement.

• DataAdpator --> Entity Collection

• EntityCollection --> DataSet

• Do modifications in Dataset via grid

• Dataset ---> EntityCollection

• EntityCollection --> save using data adaptor

It would nice to do

• DataAdpator --> Entity Collection

• Do modifications in Dataset via EntityCollection

• EntityCollection --> save using data adaptor

Unfortunately the immediate scenerio above gave me exceptions when creating a new subitem for an existing item. Using a dataset as a datasource did not give me this problem.

I find it hard to believe that others who use LLBLGen Pro do not have similar requirements. Maybe they use two grids as you propose but from a users perspective seeing that the items are related in a hierarchical manner is what what makes me believe its worth this amount of trouble. It makes the UI more compact. It also enables the users to hide and unhide subitems so they can focus on what is most pertinent at any point in time.

If you want to create the tree back without prefetch paths

I don't mind using prefetch paths. We have lots of tables with child parent relationships so using prefetched paths will be necessary. Currently we only have two tables that have self joins. However the tables with self joins are the primary work horse tables in the system.

use a dictionary lookup: sort the entity collection of items on 'parent' ascending, then on id ascending. This should give the root at the top (as it has NULL for parent). Traverse the collection from front to back: the element is refering to a parent, so you look up the entity using the dictionary which stores the PK value as key and the entity as value. This quickly gives you a way to create the tree back from a flat list.

I will investigate this. In the mean time, if can you point me at some code in one of your examples that uses this approach it would be appreciated.

Sorry chopped too much off my previous post before doing the post.

About the oddness of not being able to store a new entity in the collection: did this occur when you bound the projection result (which is a dataset) to the grid, or the collection? If it's the collection, did you try creating an EntityView2 instance yourself and bind that instead?

**No it did not. **You guys wrote this product. You know it inside out!!!!. Possibilities that naturally spring to your minds will not to those who are still coming to grips with it.

If it had, maybe I would not be writing this.

An entity collection binds though its default view (which is a new EntityView2 instance) to the grid. The view by default exposes a field called 'AllowNew', this should be true (as it is determined from the readonly flag of the collection, which is false unless you bind a m:n based collection

)

Hmmm. I see this in the documentation

How do I prevent that new entities are added to an entity collection in a grid?

The Adapter's EntityCollection class and the SelfServicing entity collection classes implement the IBindingList.AllowNew property. Setting this property to false/False will assure that there will be no 'new' line in the grid bound to the collection object and the grid is not able to add new entities via the IBindingList interface.

Thanks for pointing this out. My need is the inverse. I want to allow new entities.

Why did I not think of this??!!

Because I could add new entities and new sub entites to these when I bound the entitycollection to the grid. It was only when I tried to add a new sub entity to an existing entity that I encountered some grief.

If it appeared to be in readonly mode or generate errors for all attempted updates then one would start to think along these lines.

Errata

Stuffed up in a list

It would nice to do

• DataAdpator --> Entity Collection

• Do modifications in Dataset via EntityCollection

• EntityCollection --> save using data adaptor

Should have been

It would nice to do

• DataAdpator --> Entity Collection

• Do modifications in** a DataGrid** using the EntityCollection

• EntityCollection --> save using data adaptor

Sorry about any confusion caused.

A question

When I use an item entitycollection and add a new entity to it, the primary key is set to a 0 If I add a child or sub entity to it, the collection knows about the relationship, presumably because of the method used and some internal relationship that is maintained

           //Create subitem
            SelfTestEntity newSubItem = new SelfTestEntity();
            newSubItem .Data = "Data Added";
            // add the child to the parent..
            items.SubSelfTest.Add(newSubItem );

If I use a dataset containing a single table that has a self join relationship (and I use a strongly typed datatable)

When I insert a new item it sets the primary key (primarykey) value to a -1 and the parentkey (foreign key to primarykey) to a null.

If I insert via a hierachial grid, a subitem that belongs to the previous row inserted, it will add a new row with the next primary key value (in this case a -2) and set the foreign key value to a -1.

This makes finding a row in the datasource of it is a datatable that belongs to the row in the grid quite simple.

I know that what I want to achieve in code with a self joined table is possible and if I add a row to the entitycollection which will then be reflected back to the grid.

With a datatable this is easy. With an entitycollection it is more challenging to locate the corresponding parent entity.

I can add a new entity child to an existing entity parent. This is because the parent has an existing primary key value and is easy to find.

But if I add a several **new **parents to an entity collection they will **all **have a a primary key value of 0. I do not have a way of differentiating which parent a child should belong to.

Any solutions as how one can differentiate newly inserted entities. It is possible to use the negative value pattern used by data tables?

About the oddness of not being able to store a new entity in the collection: did this occur when you bound the projection result (which is a dataset) to the grid, or the collection?

The problem was only with the entitycollection. The datatable (with its relationship set) worked fine.

The hierarchial projection created a dataset containing the datatable and the relationship. I had to update the primarykey column so that its autoincrement property was true, and the AutoIncrementSeed and AutoIncrementStep had valid values.

If it's the collection, did you try creating an EntityView2 instance yourself and bind that instead? An entity collection binds though its default view (which is a new EntityView2 instance) to the grid.

I explicity set the datasource to

• the entitycollection

• the entitycollection.DefaultView

• an EntityView2 created from the entitycollection

There was no difference in any of the above scenerios.

The view by default exposes a field called 'AllowNew', this should be true (as it is determined from the readonly flag of the collection, which is false unless you bind a m:n based collection)

I checked the value of

• AllowNew

• AllowEdit

• AllowRemove

They were all true for the entity collection, the entitycollection's DefaultView and the new EntityView2 created from the entitycollection.

Creating a hierarchical projection isn't for this kind of data, it's for creating a dataset from a graph of entities. A relationship with itself causes a problem as it creates an infinite loop hence we don't support it.

On the other had it did work, possibly because its only 1 level deep. What you said causes me concern as it may break with newer versions of LLBLGen Pro.

Ok I found another way of projecting an entitycollection into a DataTable.

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

                DataTable AllItems = new DataTable("SelfJoinedItems");

                items.DefaultView.CreateProjection(propertyList,AllItems);

                ultraGrid1.DataSource = AllItems;

If course this is too limited as

• Its only a datatable

• I need to add it to a dataset

• I will need to set the self join relationship

• Possibly other primary key attributes (selfIncrementing etc)

I think keeping the original entityCollection and then using Grid events to add new entities to the EntityCollection would be easier of I can differentiate between newly added parent item entities.

Waw you have posted 6 long messages in a row, I just read the last one, and I got the impression, you have worked your issue out.

Am I right? and so should we close this thread?

I just read the last one, and I got the impression, you have worked your issue out.

Unfortuately not. I have made good progress however.

I can now project the EntityCollection into a Dataset using without using a Hirearchial Projection.

             // Get the  Entity Collection
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {

                IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.SelfTestEntity);
                prefetchPath.Add(SelfTestEntity.PrefetchPathSubSelfTest);
                adapter.FetchEntityCollection(items, null, prefetchPath);
            }

            // Only proceed if not an empty collection 
            if (items.Count > 0)
            {
                // Project to Datatable - all entity fields
                List<IEntityPropertyProjector>  propertyList =  EntityFields2.ConvertToProjectors(EntityFieldsFactory.CreateEntityFieldsObject(EntityType.SelfTestEntity));

                // The name of the DataTable will determine the add new item
                // if we use Infragistics available "add new" feature (I wouldn't in production
                // but its good for testing)
                DataTable AllItems = new DataTable("Add New Item");

                items.DefaultView.CreateProjection(propertyList,AllItems);

                // Set PrimaryKey to be self incrementing
                AllItems.Columns["PrimaryKey"].AutoIncrement = true;
                AllItems.Columns["PrimaryKey"].AutoIncrementSeed = -1;
                AllItems.Columns["PrimaryKey"].AutoIncrementStep = -1;

                // Create the dataset
                DataSet RelatedItems = new DataSet("RelatedItems");

                // Add our self joined table

                RelatedItems.Tables.Add(AllItems);

                // Create the relationship for the self join
                // The name of the relationship will determine the add new sub item name
                // if we use Infragistic's available "add new" feature (I wouldn't in production
                // but its good for testing)

                RelatedItems.Relations.Add("Add New SubItem",AllItems.Columns["PrimaryKey"], AllItems.Columns["ParentKey"]);
                RelatedItems.Relations["Add New SubItem"].Nested = true;

                // set the grids datasource so we can edit it
                ultraGrid1.DataSource = RelatedItems;
            }

I can now edit the Items, subitems, update items and subitems, create new items, Create new sub items and add them to new items or existing items.

I now need a mechanism to persist the changes to the database. It would be easier to reflect changes back into the entity collection and then use a DataAcessAdaptor to manage this.

I can do this by simply parsing the dataset and apply the changes to the entity collection.

A better alternative is to make changes to the entity collection directly so I can avoid this.

Instead of inserting new rows into the grid, I can capture a button event to make changes to the entitycollection. It would then notify the grid of the changes.

If I want to add a new sub item to an existing item, no problem. As an existing parent entity has a primary key value, I can search for that and add the child item to the parent.

There is a problem if I create a new parent and then add new childdren to it. In the entitycollection the primary key is zero for a new entity. If I create a number of new parents then want to add a new child, I need to know which parent to add the child to. The problem is they all have a primary key value of 0.

With a DataTable, this problem is circumvented by adding unique negative primary key values. I am not sure if this would be compatible with an entity collection.

I guess you'll have to make the changes to the DataSet, then when you want to save it, you should parse it create the collections and save them.

Peter, you seemed to have misunderstood me, so I'll re-try with the post below. There are several things mixed together in this thread and for us it seems all these things together form your problem but it's hard to divide them apart and address the separate things in separate answers.

For example, the error you got with the binding, that you couldn't add new rows, that's either a bug in our code or a bug in infragistics' code. To rule out if we have an error, I asked you to use an EntityView2 instance and check whether it has AllowNew set to true. This should be true by default in your case. So if the infragistics grid fails to honor that, there's little we can do.

It would help us a great deal if you keep the posts small, to the point and focused on a single problem. It's no problem to open 2 or more threads if the problems are not related. Mixing them into the same thread is not making things simpler for us to answer.

Relations to self, like Item which has a relation with itself and a field 'SubItems' can be bound to a master-detail grid setup properly using a single BindingSource. Set the bindingsource.DataSource to the Items entity collection (which has th SubItems fetched using a prefetch path) and then bind that bindingsource to the mastergrid. Bind it also to the detail grid, but for the detail grid you specify as 'datamember' in the grid's binding properties "SubItems". It's exactly the same as in the Northwind example on the website where Customer and Orders are bound.

he00273 wrote:

The 'sub items' aren't different entities though, they too are 'Item' entity instances.

If they were different entities we would be using two tables and be relating them together. It is the need to manage all items as items which resulted in me going down this path while still knowing if an item was related to a parent item.

Perhaps some form of Entity inheritance should be considered

No, your setup is the same as Employee in northwind, no inheritance should be used there, as inheritance means: types, which can't change. Your subitems are items as well, and are only subitems because they refer to a parent item. That's a 'role' not a 'type'.

Actually just getting the entity collection into dataset was my objective. If using a hierarchical projection is inappropriate, what other mechanisms are there (aside from looping through the entity collection and building the dataset that way. Currently it appears easier to just to use ADO.NET (at the expense of database independance) to create the dataset and then use LLBLGen Pro to handle the resulting updates/deletes in code.

We added a lot of code to make entity collections be bindable properly to grids without problems. In other words: you shouldn't have to go through the projection stuff at all. Create two grids: One master, one detail. Bind them as I described above to a bindingsource which gets its DataSource set to the entitycollection of items. Moving through the master grid will reveal the subitems of the current item in the master grid in the detail grid. Adding a new row in master will create a new item entity. selecting it will allow you to create a new set of subitems.

have you tried this and it failed? If so I'll create a master-detail grid for you on Employee in Northwind.

Also keep in mind, that just because it doesn't work with infragistics' grid, it means it's our fault. They have had many issues with custom classes bound to their grid for a very long time. 2 years ago we were fed up with that and told them to either they fixed their bugs or we would tell our customers not to buy their grids anymore. They decided to fix the bugs, although it took a while and they needed me to tell them how to fix it...

Unfortunately the immediate scenerio above gave me exceptions when creating a new subitem for an existing item. Using a dataset as a datasource did not give me this problem.

I find it hard to believe that others who use LLBLGen Pro do not have similar requirements. Maybe they use two grids as you propose but from a users perspective seeing that the items are related in a hierarchical manner is what what makes me believe its worth this amount of trouble. It makes the UI more compact. It also enables the users to hide and unhide subitems so they can focus on what is most pertinent at any point in time.

That can't be done (single grid). The problem is that a datatable has relations OUTSIDE the rows stored. So the grid can create a hierarchy based on the datarelatons in the datatable. A collection of objects doesn't have external relations to tell the grid how the flat list is actually a hierarchy. That is: most grids are build on top of datatables and have little support for anything else. DevExpress' grid has a feature where you can create hierarchical grids based on a flat list and some code which tells the grid how children relate to their parents, but it's not intuitive because the grid doesn't know how to do this.

So in short, what you want is actually master-detail: Item (master) + subitem (detail). I understand you want to do that in 1 grid, but that's not going to work with a flat collection with objects, because grid makers thought they didn't have to spend a lot of work in that area (neither did microsoft), and only work with datatables in that area in most cases.

REthinking about it, I think the error in the grid comes from the fact it doesn't know how to create the datarelation (tying parent to child) for a flat list, as there isn't any way to do that, according to the grid.

The EntityView2 part wasn't a question to you IF it even works, I know it does, I just wanted you to see that the AllowNew property was true so the grid does know it should allow adding a new row and it's not our code which prohibits this.

Hopefully this shades some light onto the topic.

I've created a simple example how to do it in a master-detail scenario (attached, please update the app.config for your situation).. It fetches employees from northwind and binds the same set twice to the grids at the left and once to the datagrid at the right.

Walking the master (top grid) will give the detail (bottom grid) new data: the managed employees for the employee chosen in the top grid. As you can see, the grids allow new rows, as that's enabled by default. As I explained above, displaying it in 1 grid with a sub level depends on the grid's ability to display hierarchies using some extra data. By default a datatable produces this data itself through the DataRelation instances, for normal objects like entity objects in a collection, there has to be supplied a custom piece of code, and this is 1) grid specific and 2) not all grids support this (the devexpress' one does, don't know about the infragistics one). As you can see in the example, at the right, the simple .NET datagrid knows how to navigate from parent to child, by allowing you to click the [+] buttons.

It's up to you how you want to implement it in your situation, i'd go for master-detail as that's the simplest for the user.

It would help us a great deal if you keep the posts small, to the point and focused on a single problem.

I'll try.

threads if the problems are not related

I think they are related or sufficiently so or I would have created a new thread. I will create a new thread for another question I have.

Set the bindingsource.DataSource to the Items entity collection (which has th SubItems fetched using a prefetch path) and then bind that bindingsource to the mastergrid. Bind it also to the detail grid, but for the detail grid you specify as 'datamember' in the grid's binding properties "SubItems". It's exactly the same as in the Northwind example on the website where Customer and Orders are bound.

Ahhh. Will try that. I thought one should bind the entitycollection directly.

you shouldn't have to go through the projection stuff at all.

Didn't want to. But I did not think I had a viable option. Desperate measures. Bonus - I know how to do this now.

Rethinking about it, I think the error in the grid comes from the fact it doesn't know how to create the datarelation (tying parent to child) for a flat list, as there isn't any way to do that, according to the grid.

There is a relation - a self join. With a dataset, I created this explicitly. It worked fine with Infragistics hierachial grid. A hierarchial projection of the entity collection of self joined table also created the relation. Also worked fine with Infragistics grid.

I've created a simple example how to do it in a master-detail scenario (attached, please update the app.config for your situation).. It fetches employees from northwind and binds the same set twice to the grids at the left and once to the datagrid at the right

Has some issues, but fixed these.

Thanks. Great, LINQ!!. Have not used LINQ previously. OK I need to start as of now.

Looking forward for your tests...

If I bind my entitycollection to my UltraGrid and programmatically add new parents and new children and add new children to existing parents.

What does not happen is that the Grid does not update itself when I change the entitycollection.

Does the entitycollection raise notifications when it has been updated?

If I bind my entitycollection to my UltraGrid and programmatically add new parents and new children and add new children to existing parents.

What does not happen is that the Grid does not update itself when I change the entitycollection.

Are you using a bindingSource?