Hello, I am trying to create a category list for a menu on the side of my web page. It is a database driven menu that follows like this

Menu 1 sub menu1 sub menu2 Menu 2 sub menu1 sub menu2

My database is set up in one table like this

menuID | menuName | parentMenuID

my question is how would I build a realationship to the table to reference itself, the first menu would have a parentMenuID as -1, and the rest would have the primary key as their parentMenuID, how can I use this with the llbl gen to bind it to menu I have created?

I have done this using sprocs but am kinda confused on how to use and entity collection to do the same thing. Thanks in advance for you help!!

jaxon wrote:

Hello, I am trying to create a category list for a menu on the side of my web page. ...

My database is set up in one table like this

menuID | menuName | parentMenuID

my question is how would I build a realationship to the table to reference itself, the first menu would have a parentMenuID as -1, and the rest would have the primary key as their parentMenuID, how can I use this with the llbl gen to bind it to menu I have created?

...

Jaxon,

That looks like a fun problem...

(The following assumes you're using the Adapter templates. I havn't used SelfServing, but they may not work efficiently for large trees)

If you create a FK from parentMenuID to menuID (in to the same table) you should get two relationships when you load the schema in to LLBLGen Pro. They might get the same name initially, but you can rename the m:1 relationship to "Parent" and the 1:m relationship to "Children".

You can then use an EntityCollection to fetch all of the tree nodes, and use prefetch paths to load the Children collection in each node. The only problem is that the objects loaded via the prefetch path will be duplicates of the nodes in the collection, so you won't be able to follow the tree down more than one level.

To get around this you can use a Context object. The Context will ensure there is only one copy of each object. You can then follow the tree down recursively to any level.

Here are some code snippets:

// Create an EntityCollection of node entities and attach a context: DataAccessAdapter adapter = new DataAccessAdapter(connection_string); EntityCollection nodes = new EntityCollection(new NodeEntityFactory()); Context tree_context = new Context(); nodes.ActiveContext = tree_context;

// Use a prefetch path to populate the node.Children collection of each node: PrefetchPath2 path = new PrefetchPath2((int)EntityType.NodeEntity); path.Add(NodeEntity.PrefetchPathChildren);

// Fetch all menu items: adapter.FetchEntityCollection(nodes, null, path);

// Find the root node (which, as a hack, is assumed to be PK id 1): TreeEntity root_node = (TreeEntity)tree_context.Get(new TreeEntityFactory(), 1);

You will then have an in-memory tree. You can print or process it with a recursive function:

void PrintNode(TreeEntity node, int depth) { for (int i = 0; i < depth; i++) { Console.Write(" "); }

Console.WriteLine(node.Name);

foreach (NodeEntity child in node.Children)
{
    PrintNode(child, depth + 1);
}

}

I haven't been using LLBLGen Pro for long, so someone more experience might like to come along and confirm that the above isn't an insane thing to do for internal reasons.

PS. One problem with using links to parents to build a tree is that it makes it difficult to do queries on the tree on the server side. If you need to do anything fancy with huge trees (eg, too big to bring back to the client in one go), you might want to do a search for "Nested Sets", "SQL" and "Joe Celko" on Google.

Regards, Ben

ianbanks wrote:

Jaxon,

That looks like a fun problem...

(The following assumes you're using the Adapter templates. I havn't used SelfServing, but they may not work efficiently for large trees)

If you create a FK from parentMenuID to menuID (in to the same table) you should get two relationships when you load the schema in to LLBLGen Pro. They might get the same name initially, but you can rename the m:1 relationship to "Parent" and the 1:m relationship to "Children".

You can then use an EntityCollection to fetch all of the tree nodes, and use prefetch paths to load the Children collection in each node. The only problem is that the objects loaded via the prefetch path will be duplicates of the nodes in the collection, so you won't be able to follow the tree down more than one level.

To get around this you can use a Context object. The Context will ensure there is only one copy of each object. You can then follow the tree down recursively to any level.

Here are some code snippets:

// Create an EntityCollection of node entities and attach a context: DataAccessAdapter adapter = new DataAccessAdapter(connection_string); EntityCollection nodes = new EntityCollection(new NodeEntityFactory()); Context tree_context = new Context(); nodes.ActiveContext = tree_context;

// Use a prefetch path to populate the node.Children collection of each node: PrefetchPath2 path = new PrefetchPath2((int)EntityType.NodeEntity); path.Add(NodeEntity.PrefetchPathChildren);

// Fetch all menu items: adapter.FetchEntityCollection(nodes, null, path);

// Find the root node (which, as a hack, is assumed to be PK id 1): TreeEntity root_node = (TreeEntity)tree_context.Get(new TreeEntityFactory(), 1);

You will then have an in-memory tree. You can print or process it with a recursive function:

void PrintNode(TreeEntity node, int depth) { for (int i = 0; i < depth; i++) { Console.Write(" "); }

Console.WriteLine(node.Name);

foreach (NodeEntity child in node.Children)
{
    PrintNode(child, depth + 1);
}

}

I haven't been using LLBLGen Pro for long, so someone more experience might like to come along and confirm that the above isn't an insane thing to do for internal reasons.

PS. One problem with using links to parents to build a tree is that it makes it difficult to do queries on the tree on the server side. If you need to do anything fancy with huge trees (eg, too big to bring back to the client in one go), you might want to do a search for "Nested Sets", "SQL" and "Joe Celko" on Google.

Regards, Ben

Hey thanks Ben, this looks like it could work. I am using it as a dynamic menu for a shopping cart. This menu is the categories, let's say you have snowmobiles as the parent category, and then you have new, used, and accessories as the children. I really didn't see the need to have a one to many realationship with a parentTable to a childTable with it being dependent on the parentTable pKey. I thought it might be more useful to put them in one table since they pretty much have all the same properties.

Thanks a lot for the example, I haven't really used LLBL Gen for long at all. I have been reading through the documentation but I'm still a little confused. I have been using M$ starter kits as my building blocks but they seem to be not so good as far as architecture is concerned. I wish there was a good example of how to build a great asp.net application with llbl gen. I downloaded the sample code for pet shop here, but in the readme it said not to be used as a real n-tier example.....I would like to know where these "good" examples are..if you know of any I would love to check it out. Thanks again!

jaxon

Creating a 'this is how you should do it' application as an example is hard, because what's good depends on the situation, so it's not one size fits all. Unfortunately. The Petshop warning is meant to avoid having people look at it and say "this is how we should do it".

Just a suggestion to the Menu example:

when using the following line of code:

// Fetch all menu items: adapter.FetchEntityCollection(nodes, null, path);

Instead of the null parameter, use a bucket-filter (RelationPredicateBucket) to get the root Menu nodes, and still use the prefetchpath, to get their children.

You can be satisfied with just one depth of children, yet you can use subpathes to dig deep in the children tree.

The result will be a collection of Main Menu Nodes with each Menu item holding a collection of its childern Menu items...and so on.

Otis wrote:

Creating a 'this is how you should do it' application as an example is hard, because what's good depends on the situation, so it's not one size fits all. Unfortunately. The Petshop warning is meant to avoid having people look at it and say "this is how we should do it".

But wouldn't a basic store application be pretty much the same for every store? Why can't there be a good "basec web store" to be used over and over again by just modying the look and feel a little bit? This is what I'm aiming for. I guess what I'm looking to do is build certain apps a certain way that can be reused for other projects. Not everytime I need to create a store take a new approach.

I just want to be able to say "Here is my library of code applications, here is a store I made, a knowledge base, a ticketing system, a this, a that. Here is what the client needs, (takes store code from library and customizes it) and done!" That's all I'm really getting at.

Most of my customers could care less how I arrive at the solution as long as I arrive at it and it works well. If I build three dirrerent types of stores that have different prices and options then that would rule! I just want to be able to use llbl gen pro to build them. Since I'm so new at it I need a good example along with the llbl gen documentation. The documentation was great, but I need to know how to apply it. I am not that smart I'm not saying I need my hand held but even a basic customer admin example would be a tremendous help. Like here is an app where you can add customers, nothing else, just customers but the app is a solid n-tier application. That would make my day! I'm not trying to sound needy and bratty, but I just love to program and will do it for a long long time. I read tons and tons of books about it. The reason I was going to starter kit way is because there are so many books that are like here is how you code it, and here is why you code it this way. With these O/R Mappers it's always like here is why you code it this way, but we won't show you any code that uses it properly, just a bunch of examples that say "not to be used as a real life n-tier application".. I guess I'm just frustrated.

take it easy guys,

Jaxon