- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
PrefetchPath built for recursive table not working as expected
Joined: 12-Jan-2008
Hello,
I have a database table that contains a self-referential relationship. I’m writing a function that will take a DataTable, filled through a FetchTypedList(), and return a prefetch path constructed recursively to allow for the creation of a collection that represents the tree stored in the original table.
The function will take a params IPrefetchPathElement2[] argument to allow for each node in the tree to have additional prefetch paths set when the collection is filled. The part of the function that creates the prefetch path for the tree itself works well. What isn’t working as expected are the additional arguments passed in through the params field. When I add additional PrefetchPathElement2s to the PrefetchPath, the first layer of the tree has its related property instantiated and filled, but all levels below the first are not prefetched and there related propery is null.
Here is an example from the watch window after the EntityCollection<PanelBarEntity> collection has been filled using the returned prefetch path:
collection[0].MenuKey : 4 collection[0].Menu : {MMDB.EntityClasses.MenuEntity} collection[0].PanelBarChildren[0].MenuKey : 4 collection[0].PanelBarChildren[0].Menu : null
I seem to have run into the limit of my understanding of prefetch paths. I stepped through the code dozens of times and the PrefetchPathElement2 element to fill the MenuEntity property of the PanelBarEntity instance is added to the path. My only guess at this point is that I’m not adding it in the right place. The overall idea of building the path is to ensure that for every level of the tree, there is a corresponding set of PrefetchPathElement2s that should ensure the related Entities for the nodes at any a given level are filled.
I’m thinking the result I’m getting has to have something to do with how I am constructing the paths, but I cannot see why. I would appreciate any insight anyone can offer as to why I’m getting this result and possibly how to fix it.
Sorry about the long post, but I can’t really shorten the code example and still have it make sense.
LLBL info: LLBLGen Pro. Version 2.5 Final (August 28th, 2007) This is a runtime problem: The File Version of SD.LLBLGen.Pro.SqlServer.Net20.dll is 2.5.7.820 There is no exception thrown The Generated Code uses the Adapter template , Target Language: C#, Target Platform .net 3.0. The code is being used in VS2008 in a website targeting .net 3.5 Database: SQLServer 2005
I will be happy to provide any further information, examples etc...
Thank you, Tony Barton.
Database structure: The table PanelBar contains a PrimaryKey called PanelBarKey and a foreign key that relates to PanelBarKey called ParentKey. It also contains a foreign key called MenuKey that relates to a second table called Menu. The Menu table’s primary key is called MenuKey
The following code is contained in the Page_Load method of default.aspx
DataAccessAdapter adapter = new DataAccessAdapter("data source=192.168.1.100;initial catalog=MMDB");
DataTable dt = new DataTable();
adapter.FetchTypedList(new PanelBarEntityFactory().CreateFields(), dt, new RelationPredicateBucket());
dt.PrimaryKey = new DataColumn[] { dt.Columns[PanelBarFields.PanelBarKey.Name] };
int depth = 0;
//this is a container class for passing a group of arguments to the
//function that builds the pat
PrefetchArgClass args = new PrefetchArgClass()
{
eType = EntityType.PanelBarEntity,
dt = dt,
ChildPrefetch = PanelBarEntity.PrefetchPathPanelBarChildren,
ParentKey = PanelBarFields.ParentKey.Name,
TableKey = PanelBarFields.PanelBarKey.Name
};
//Create recursive prefetch path
PrefetchPath2 path = new Recursion().GetRecursivePrefetchPath(args, out depth, PanelBarEntity.PrefetchPathMenu);
RelationPredicateBucket filter = new RelationPredicateBucket(PanelBarFields.ParentKey == DBNull.Value);
EntityCollection<PanelBarEntity> collection = new EntityCollection<PanelBarEntity>();
adapter.FetchEntityCollection(collection, filter, path);
The following code is from an object in a file contained in App_Code. It contains the logic to create and return a new prefetch path.
public class Recursion
{
int maxDepth = 0;
int currentDepth = 0;
DataTable dt = null;
public Recursion() { }
//******** Recursive Prefetch Path Methods
public PrefetchPath2 GetRecursivePrefetchPath(PrefetchArgClass args, out int depth, params IPrefetchPathElement2[] additionalPaths)
{
dt = args.dt; //global because BuldPrefetchPath() needs to use the DataTable's AsEnumerable function
if (dt.TableName == String.Empty) dt.TableName = "NewTable";
PrefetchPath2 path = new PrefetchPath2(args.eType);
//start recursion
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][args.ParentKey].ToString() == String.Empty)
{
IncDepth();
GetPrefetchPath(args.GetPrefetchRecursiveClass(dt.Rows[i], path), additionalPaths);
currentDepth--;
}
}
depth = maxDepth;
return path;
}
private void GetPrefetchPath(PrefetchRecursiveClass args, params IPrefetchPathElement2[] additionalPaths)
{
//the if ensures that a prefetch path is only added once per level
if (args.path.Count == 0)
{
//Add prefetch path for current entity
args.path.Add(GetPrefetchElement(args.ChildPrefetch));
//*************************************
//THIS IS WHERE THE PROBLEM SEEMS TO BE
//*************************************
foreach (PrefetchPathElement2 path in additionalPaths)
{
args.path.Add(GetPrefetchElement(path));
}
}
int lastIndex = args.path.Count - 1;
//get first layer of child nodes for current node
List<DataRow> rows = dt.AsEnumerable().Where(x => x[args.ParentKey].ToString() == args.Row[args.TableKey].ToString()).ToList();
for (int i = 0; i < rows.Count(); i++)
{
IncDepth();
GetPrefetchPath(args.GetPrefetchRecursiveClass(rows[i], args.path[0].SubPath), additionalPaths);
currentDepth--;
}
}
private IPrefetchPathElement2 GetPrefetchElement(IPrefetchPathElement2 ppe)
{
return new PrefetchPathElement2(ppe.RetrievalCollection,
ppe.Relation,
ppe.DestinationEntityType,
ppe.ToFetchEntityType,
0,
ppe.Sorter,
ppe.Filter,
ppe.FilterRelations,
ppe.EntityFactoryToUse,
ppe.PropertyName,
ppe.TypeOfRelation);
}
//****** General Helper methods
private void IncDepth()
{
currentDepth++;
if (currentDepth > maxDepth) maxDepth = currentDepth;
}
}
//Helper classes to keep function argument list short
public class PrefetchRecursiveClass
{
public IPrefetchPath2 path { get; set; }
public IPrefetchPathElement2 ChildPrefetch { get; set; }
public DataRow Row { get; set; }
public string ParentKey { get; set; }
public string TableKey { get; set; }
public PrefetchRecursiveClass() { }
public PrefetchRecursiveClass GetPrefetchRecursiveClass(DataRow r, IPrefetchPath2 pp)
{
return new PrefetchRecursiveClass()
{
ChildPrefetch = this.ChildPrefetch,
ParentKey = this.ParentKey,
TableKey = this.TableKey,
Row = r,
path = pp
};
}
}
public class PrefetchArgClass
{
public IPrefetchPath2 path { get; set; }
public EntityType eType { get; set; }
public IPrefetchPathElement2 ChildPrefetch { get; set; }
public DataTable dt { get; set; }
public string ParentKey { get; set; }
public string TableKey { get; set; }
public PrefetchArgClass() { }
public PrefetchRecursiveClass GetPrefetchRecursiveClass(DataRow r, IPrefetchPath2 pp)
{
return new PrefetchRecursiveClass()
{
ChildPrefetch = this.ChildPrefetch,
ParentKey = this.ParentKey,
TableKey = this.TableKey,
Row = r,
path = pp
};
}
}
Joined: 12-Jan-2008
I did some further testing. I "Hard coded" a PrefetchPath that does the same thing as the recursive one. When I fill a collection using this test path, everything is filled as I expect.
PrefetchPath2 testPath = new PrefetchPath2(EntityType.PanelBarEntity);
testPath.Add(PanelBarEntity.PrefetchPathPanelBarChildren);
testPath.Add(PanelBarEntity.PrefetchPathMenu);
testPath[0].SubPath.Add(PanelBarEntity.PrefetchPathPanelBarChildren);
testPath[0].SubPath.Add(PanelBarEntity.PrefetchPathMenu);
testPath[0].SubPath[0].SubPath.Add(PanelBarEntity.PrefetchPathPanelBarChildren);
testPath[0].SubPath[0].SubPath.Add(PanelBarEntity.PrefetchPathMenu);
EntityCollection<PanelBarEntity> testCollection = new EntityCollection<PanelBarEntity>();
adapter.FetchEntityCollection(testCollection, filter, testPath);
Here's the output from the watch window: testCollection[0].Menu : {MMDB.EntityClasses.MenuEntity} testCollection[0].PanelBarChildren[0].Menu : {MMDB.EntityClasses.MenuEntity} testCollection[1].Menu : {MMDB.EntityClasses.MenuEntity} testCollection[1].PanelBarChildren[0].Menu : {MMDB.EntityClasses.MenuEntity} testCollection[1].PanelBarChildren[1].Menu : {MMDB.EntityClasses.MenuEntity}
I've been studying the two PrefetchPath objects (one recursively built, the other from above) in the watch window to see if I can find any differences. So far, they look identical.
Joined: 22-Feb-2005
Hey Tony,
Could you give a little more background on what you are trying to do in general? I'm not clear on what the datatable is for--you want to fetch the whole tree flat, then reconstruct it in an actual object graph?
If the table is only self-referencing on a single key, it should be as simple as adding a recursive function that adds the prefetch paths repeatedly in a loop, to a limit that you set (say, 1000). The framework is smart enough to stop querying when it stops getting data returned. There is not a ton of cost (performance-wise) to adding prefetch paths, so if you pick a reasonable limit it will likely be a less costly alternative to fetching the datatable.
I didn't fully read through your code, so I might be missing the point on what you are trying to do. But from your description it sounds like the solution might be (relatively) straightforward.
HTH,
Phil
Joined: 12-Jan-2008
Hi Phil,
Essentially, yes, the idea is to be able to fill a collection to be an exact match of a tree that is stored in a flat, but self referential, sql table and to do it with as few database trips as possible (which is why the prefetch paths).
This project began at work where I have a couple self-referential sql tables that are usually bound to aspx tree objects and used as navigation for one thing or another. We recently switched from self-servicing to the adapter template and I noticed that (for obvious reasons) I could no longer traverse a collection filled from the table as if it were a tree. The first solution I came up with was the one you suggest – just figure out what a reasonable depth is and build a prefetch path that’s just a little deeper. The first function I wrote along this line simply returns the depth of the table, from here I could create a simple recursive function to add as many paths as the tree is deep.
Then I thought that it would be simpler if I had a function that just built the paths as it went and returned a filled PrefetchPath2 object. This would also be more general as it would be able to do this for any sql table with a recursive structure. The first argument object passed into the function GetRecursivePrefetchPath contains the necessary information to start the construction. This part of the function works great.
One of the tables I have at work contains a tree that is the basis for an element grouping on a report. Only the terminating branches of the tree contain leaves and these leaves are in a different table. I thought next that it would be a good idea to be able to pass in additional prefetch paths to allow for (in this case) the fetching of the leaf objects when my tree is built. This is where things stopped working as I expected.
The building of the tree works well. When I use the filter to only get nodes whose ParentKey == null and the correct count of nested prefetch paths, I get a collection that is an exact representation of the tree I have stored in the flat sql table. The filter ensures that I don’t get any redundant nodes at the top level and the prefetch paths ensure that the nodes below are filled.
I created the test case in my second post to see if maybe what I was trying with the additional prefetch paths wasn’t possible. But the test case worked. I wrote a function that loops through a PrefetchPath2 (passed in) and writes to the screen some information about each node in the path. I ran both my generated path and the “hard coded” path through this function and they both came out looking exactly the same. As far as I can tell, there is no difference between the way I generate the path and the path that I explicitly create as a test. So the question is: why does one work and one not?
I’m not convinced that this is the absolute best way to go about getting this information, but it’s where I was when I ran into my problem. The question though is not whether this approach is optimal or even if there’s another (more optimal) way of doing it, but why the above code does not work correctly. As far as I can tell, it should and I’m rather stumped as to why it isn’t.
This may be rather academic at this point, but I thought I understood prefetch paths rather well and not being able to figure this out is making think there must be a gaping hole in my understanding which I feel I need to fill before I continue.
Joined: 12-Jan-2008
Below are the printouts I get from the two PrefetchPath2 objects built as described above. The first section is from the path that I dynamically create. The second is from the "hard coded" path whose code is shown in my second post.
The code is generated through a loop in a recursive function. The result is that each level of the path is printed completely before the next level is printed. The current path level is shown by the GraphLevel property
Recursive (dynamic) Path:
SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2
PropertyName: PanelBarChildren
GraphLevel: 0
TypeOfRelation: OneToMany
Relation.MappedFieldName: PanelBarChildren
Relation.StartEntityIsPkSide: True
RetrievalCollection: MMDB.HelperClasses.EntityCollection`1[MMDB.EntityClasses.PanelBarEntity]
RetrievalCollection.EntityFactoryToUse: MMDB.FactoryClasses.PanelBarEntityFactory
RetrievalCollection.EntityFactoryToUse.ForEntityName: PanelBarEntity
SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2
PropertyName: Menu
GraphLevel: 0
TypeOfRelation: ManyToOne
Relation.MappedFieldName: Menu
Relation.StartEntityIsPkSide: False
RetrievalCollection: MMDB.HelperClasses.EntityCollection
RetrievalCollection.EntityFactoryToUse: MMDB.FactoryClasses.MenuEntityFactory
RetrievalCollection.EntityFactoryToUse.ForEntityName: MenuEntity
-----------------------------
SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2
PropertyName: PanelBarChildren
GraphLevel: 1
TypeOfRelation: OneToMany
Relation.MappedFieldName: PanelBarChildren
Relation.StartEntityIsPkSide: True
RetrievalCollection: MMDB.HelperClasses.EntityCollection`1[MMDB.EntityClasses.PanelBarEntity]
RetrievalCollection.EntityFactoryToUse: MMDB.FactoryClasses.PanelBarEntityFactory
RetrievalCollection.EntityFactoryToUse.ForEntityName: PanelBarEntity
SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2
PropertyName: Menu
GraphLevel: 1
TypeOfRelation: ManyToOne
Relation.MappedFieldName: Menu
Relation.StartEntityIsPkSide: False
RetrievalCollection: MMDB.HelperClasses.EntityCollection
RetrievalCollection.EntityFactoryToUse: MMDB.FactoryClasses.MenuEntityFactory
RetrievalCollection.EntityFactoryToUse.ForEntityName: MenuEntity
-----------------------------
SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2
PropertyName: PanelBarChildren
GraphLevel: 2
TypeOfRelation: OneToMany
Relation.MappedFieldName: PanelBarChildren
Relation.StartEntityIsPkSide: True
RetrievalCollection: MMDB.HelperClasses.EntityCollection`1[MMDB.EntityClasses.PanelBarEntity]
RetrievalCollection.EntityFactoryToUse: MMDB.FactoryClasses.PanelBarEntityFactory
RetrievalCollection.EntityFactoryToUse.ForEntityName: PanelBarEntity
SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2
PropertyName: Menu
GraphLevel: 2
TypeOfRelation: ManyToOne
Relation.MappedFieldName: Menu
Relation.StartEntityIsPkSide: False
RetrievalCollection: MMDB.HelperClasses.EntityCollection
RetrievalCollection.EntityFactoryToUse: MMDB.FactoryClasses.MenuEntityFactory
RetrievalCollection.EntityFactoryToUse.ForEntityName: MenuEntity
Static ("Hard Coded") Path:
SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2
PropertyName: PanelBarChildren
GraphLevel: 0
TypeOfRelation: OneToMany
Relation.MappedFieldName: PanelBarChildren
Relation.StartEntityIsPkSide: True
RetrievalCollection: MMDB.HelperClasses.EntityCollection`1[MMDB.EntityClasses.PanelBarEntity]
RetrievalCollection.EntityFactoryToUse: MMDB.FactoryClasses.PanelBarEntityFactory
RetrievalCollection.EntityFactoryToUse.ForEntityName: PanelBarEntity
SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2
PropertyName: Menu
GraphLevel: 0
TypeOfRelation: ManyToOne
Relation.MappedFieldName: Menu
Relation.StartEntityIsPkSide: False
RetrievalCollection: MMDB.HelperClasses.EntityCollection
RetrievalCollection.EntityFactoryToUse: MMDB.FactoryClasses.MenuEntityFactory
RetrievalCollection.EntityFactoryToUse.ForEntityName: MenuEntity
-----------------------------
SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2
PropertyName: PanelBarChildren
GraphLevel: 1
TypeOfRelation: OneToMany
Relation.MappedFieldName: PanelBarChildren
Relation.StartEntityIsPkSide: True
RetrievalCollection: MMDB.HelperClasses.EntityCollection`1[MMDB.EntityClasses.PanelBarEntity]
RetrievalCollection.EntityFactoryToUse: MMDB.FactoryClasses.PanelBarEntityFactory
RetrievalCollection.EntityFactoryToUse.ForEntityName: PanelBarEntity
SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2
PropertyName: Menu
GraphLevel: 1
TypeOfRelation: ManyToOne
Relation.MappedFieldName: Menu
Relation.StartEntityIsPkSide: False
RetrievalCollection: MMDB.HelperClasses.EntityCollection
RetrievalCollection.EntityFactoryToUse: MMDB.FactoryClasses.MenuEntityFactory
RetrievalCollection.EntityFactoryToUse.ForEntityName: MenuEntity
-----------------------------
SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2
PropertyName: PanelBarChildren
GraphLevel: 2
TypeOfRelation: OneToMany
Relation.MappedFieldName: PanelBarChildren
Relation.StartEntityIsPkSide: True
RetrievalCollection: MMDB.HelperClasses.EntityCollection`1[MMDB.EntityClasses.PanelBarEntity]
RetrievalCollection.EntityFactoryToUse: MMDB.FactoryClasses.PanelBarEntityFactory
RetrievalCollection.EntityFactoryToUse.ForEntityName: PanelBarEntity
SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2
PropertyName: Menu
GraphLevel: 2
TypeOfRelation: ManyToOne
Relation.MappedFieldName: Menu
Relation.StartEntityIsPkSide: False
RetrievalCollection: MMDB.HelperClasses.EntityCollection
RetrievalCollection.EntityFactoryToUse: MMDB.FactoryClasses.MenuEntityFactory
RetrievalCollection.EntityFactoryToUse.ForEntityName: MenuEntity
Here is the function used to generate the above text. The variable Textbox1 is just a standard aspx TextBox set to multiline.
int pathLevel = 0;
protected void DisplayPath(IPrefetchPath2 path)
{
pathLevel++;
for (int i = 0; i < path.Count; i++)
{
TextBox1.Text += path.ToString() + "\r";
TextBox1.Text += "PropertyName:" + " " + path[i].PropertyName + "\r";
TextBox1.Text += "GraphLevel:" + " " + path[i].GraphLevel.ToString() + "\r";
TextBox1.Text += "TypeOfRelation: " + path[i].TypeOfRelation.ToString() + "\r";
TextBox1.Text += "Relation.MappedFieldName: " + path[i].Relation.MappedFieldName + "\r";
TextBox1.Text += "Relation.StartEntityIsPkSide: " + path[i].Relation.StartEntityIsPkSide + "\r";
TextBox1.Text += "RetrievalCollection: " + path[i].RetrievalCollection.ToString() + "\r";
TextBox1.Text += "RetrievalCollection.EntityFactoryToUse: " + path[i].RetrievalCollection.EntityFactoryToUse.ToString() + "\r";
TextBox1.Text += "RetrievalCollection.EntityFactoryToUse.ForEntityName: " + path[i].RetrievalCollection.EntityFactoryToUse.ForEntityName + "\r";
TextBox1.Text += "\r";
}
TextBox1.Text += "-----------------------------\r";
//recursive call to display next level of path
for (int i = 0; i < path.Count; i++)
{
DisplayPath(path[i].SubPath);
}
pathLevel--;
}
Joined: 22-Feb-2005
Disclaimer: I still haven't had time to really look at your original code in depth, nor do I claim to understand where your code is going wrong.
tbarton wrote:
Essentially, yes, the idea is to be able to fill a collection to be an exact match of a tree that is stored in a flat, but self referential, sql table and to do it with as few database trips as possible (which is why the prefetch paths).
This project began at work where I have a couple self-referential sql tables that are usually bound to aspx tree objects and used as navigation for one thing or another. We recently switched from self-servicing to the adapter template and I noticed that (for obvious reasons) I could no longer traverse a collection filled from the table as if it were a tree. **The first solution I came up with was the one you suggest – just figure out what a reasonable depth is and build a prefetch path that’s just a little deeper. The first function I wrote along this line simply returns the depth of the table, from here I could create a simple recursive function to add as many paths as the tree is deep. **
The emphasized part of the quote is exactly what I was going to suggest next, although I think even this is probably unnecessary.
I think I may understand where you thinking is incorrect (I mean no offense by this 
).
It seems to me like the prefetch tree you are trying to fetch is linear. So parent=>child=>grandchild=>etc, and maybe you are trying to build it more like a Christmas tree?
(EDIT: to clarify, the resulting object graph will look like a Christmas tree, but the object graph and the prefetch path node graph will not, and should not, look the same).
When using prefetch paths, you don't need to be concerned with the actual VALUES that are returned, you just need to identify the nodes/relationships between the objects that you want returned. The filtering at each node/level is built into the framework.
So when I glance through your code (and as I said, it's only a glance), it looks like you are using the PK values from the datatable to filter your paths as you add them. This in essense looks like you are trying coerce the framework into doing something that it already does out of the box.
My suggestion: try the method I described in my first post (and you had already thought of and rejected). Watch the queries as they get generated in SQL profiler--if it looks like it is generating too many queries, then investigate the option of prequerying the table to get a max depth. However, even without pre-setting the max depth, I think the worst case is that the system will emit the required number of queries + 1.
If I am still way off on what you are trying to accomplish, my apologies.
Phil
Joined: 12-Jan-2008
More testing: I wrote a couple of small functions in order to test Phil's suggestion. The main difference between these test functions and the original code is that I use the PrefetchPathMenu property of the PanelBarEntity to add the paths, rather than passing one in and attempting to copy it. It did this because I thought the function GetPrefetchElement() in the original code might be the problem.
The first version is non-recursive. It assumes that each IPrefetchPath object contained in the original path contains only one element and that the element's SubPath also contains only one element. In my current testing scenario, these assumptions should be true
//Function call to first test method:
PrefetchPath2 path = new Recursion().GetRecursivePrefetchPath(args, out depth);
AddPrefetchPath(path, depth);
//Test Method:
protected void AddPrefetchPath(IPrefetchPath2 passed, int depth)
{
IPrefetchPath2 path = passed;
for (int i = 0; i < depth; i++)
{
path.Add(PanelBarEntity.PrefetchPathMenu);
path = path[0].SubPath;
}
}
The second version uses recursion and does essentially what the code in the first post does, only it does it after the hierarchical path has already been built instead of at the same time.
//Function call to second test method:
PrefetchPath2 path = new Recursion().GetRecursivePrefetchPath(args, out depth);
AddPrefetchElement(path);
//Test Method:
protected void AddPrefetchElement(IPrefetchPath2 path)
{
for (int i = 0; i < path.Count; i++)
{
bool found = false;
for (int x = 0; x < path.Count; x++)
{
if (path[x].PropertyName == "Menu")
{
found = true;
break;
}
}
if(!found) path.Add(PanelBarEntity.PrefetchPathMenu);
AddPrefetchElement(path[i].SubPath);
}
}
While the second test method is probably not the most efficient code ever written, it does cover all the possibilities in a path. The result though for both test is the same as with the original code: The first layer of the tree will be filled and all others will not.
From the watch window: collection[0].MenuKey : 4 collection[0].Menu : {MMDB.EntityClasses.MenuEntity} collection[0].PanelBarChildren[0].MenuKey : 4 collection[0].PanelBarChildren[0].Menu : null
I've looked at different index values in the collection in various combinations, but the result is always as above. The printed values of the path elements are the same in all three cases (the original and the two tests).
Is there some limitation to the amount of nesting that a prefetch path will tolerate before it starts ignoring things (for lack of a better way to phrase this question)? I'd be perfectly happy to find out this is the case. I just want to understand why it doesn't work. Once I know why, I'll come up with a work around and continue on my merry way.
Joined: 12-Jan-2008
Hi Phil, Thanks for following up.
It’s funny you should suggest the Christmas tree of paths, because when I first wrote this that was my approach. However, attempting to add a PrefetchElement to a level that already contains that element results in a runtime error, which I got the first time I ran this.
I’ve been trying to find a reasonable way to explain how I’m looking this and I think you just gave me a good idea.
If I draw the desired data tree on a piece of graph paper in such a way that each level of the tree sits on one horizontal line and I label my axis in the traditional manner of x for horizontal and y for vertical, then the prefetch path is essentially a line on the graph that runs parallel to the y axis. The nodes in the Path line are related via the path[0].Subpath property of the path node above and any additional PrefetchElements are added non-recursively (and only once) at each level. The first element in a path is always the recursive relationship between parent and child. As you pointed out, when viewed this way, I need have only one specific PrefetchElement per level per entity (or collection) I want to prefetch.
Unfortunately this is what I do. The function GetPrefetchPath() wraps the path.add() function in an if statement that ensures I only add PrefetchElements to paths containing no nodes; that is, the actual add part of the function only happens the first time I enter a new level. As I pass path[0].Subpath into the next level of the function and the Prefetch for the Child elements is what I add first, I can ensure that I am always adding additional PrefetchElements to the correct parent Element. All the recursion through the table is doing is ensuring that I actually get to the bottom of each branch. For example: If the first root node contains two layers of sub-branches and the second root node contains 5 layers, the recursive pattern will ensure that I get a PrefetchPath2 object that contains five layers of PrefetchElements related via the path[0].Subpath property.
I think it’s somewhere in the logic in the above paragraph that I’m making a mistake, but I don’t see where. When I’ve passed something other than path[0].Subpath into the recursion (i.e. path[_x_].Subpath, where 0 <= x < path.Count), I get runtime errors that I’ve either already added the path or that I’m adding a path of the wrong type. This makes sense though as doing it this way attempts to build a tree of paths, which we agree is the wrong approach.
I’m further perplexed by the failure of the two test functions. The non-recursive one, while restrictive, should have worked (for that matter so should the recursive one). But it didn’t . This makes me think that there is something going on in the internals of the PrefetchPath2 object that I’m unaware of or that I really am adding the additional paths to the wrong place.
It may also be that to avoid ridiculously large (and unnecessary) object structures, there is some limit to prefetching that LLBL has implemented. If my approach isn’t done carefully, I’ll end up with a huge tree filled with redundant objects, but I think I’m being careful enough to avoid that. If this was the case though, then the test from my second post should have failed. So I must be doing something wrong.
I will spend some more time with SQLProfiler though and try to find a difference at the query level.
Thanks again, Tony.
Joined: 12-Jan-2008
I looked at the two sets of queries with profiler and they are definitely different. I've attached the resulting profiler output.
The first set of queries was generated using the dynamic (recursive) path
PrefetchPath2 path = new Recursion().GetRecursivePrefetchPath(args, out depth, PanelBarEntity.PrefetchPathMenu);
RelationPredicateBucket filter = new RelationPredicateBucket(PanelBarFields.ParentKey == DBNull.Value);
EntityCollection<PanelBarEntity> collection = new EntityCollection<PanelBarEntity>();
adapter.FetchEntityCollection(collection, filter, path);
and the second set from the test code in the second post.
PrefetchPath2 testPath = new PrefetchPath2(EntityType.PanelBarEntity);
testPath.Add(PanelBarEntity.PrefetchPathPanelBarChildren);
testPath.Add(PanelBarEntity.PrefetchPathMenu);
testPath[0].SubPath.Add(PanelBarEntity.PrefetchPathPanelBarChildren);
testPath[0].SubPath.Add(PanelBarEntity.PrefetchPathMenu);
testPath[0].SubPath[0].SubPath.Add(PanelBarEntity.PrefetchPathPanelBarChildren);
testPath[0].SubPath[0].SubPath.Add(PanelBarEntity.PrefetchPathMenu);
EntityCollection<PanelBarEntity> testCollection = new EntityCollection<PanelBarEntity>();
adapter.FetchEntityCollection(testCollection, filter, testPath);
The first two queries using the PanelBar Prefetch are the same from both path creation methods. But the third query is quite different. Also the dynamic path contains only one query for the Menu entity prefetch, while the test path contains three. This corresponds to the results I'm getting where with the dynamic path only the first layer of the tree gets prefetched and with the test path all three layers are fetched.
So it looks like I'm definitely adding the additional paths in the wrong place. Even though they are present in the PrefetchPath2 object, they don't appear to be tied together in such away that they translate into a query. I suppose the question now is: where should I be adding them?
Any further suggestions or ideas are welcome.
Joined: 22-Feb-2005
I've read all three of your replies. I'm still fairly convinced that the problem you are trying to resolve is relatively straightforward, and that while you do need recursion, you don't need to precalculate the graph that you want ahead of time.
Let me restate the requirement, and you can reply if it's incomplete in some way:
You have a table (panelbar) that is self-referencing, that also references a related (menu) table. You would like to fetch a graph of entities that represents the full set of data.
(One thing I'm not clear on is whether these self-referencing relationships can be circular directly or indirectly. For example, PanelBar "A" is a parent of PanelBar "B" which is a parent of PanelBar "C" which is a parent of PanelBar "A". But I'm not even sure that would affect the solution).
If this is an accurate description, then your non-recursive example looks like it should work:
PrefetchPath2 testPath = new PrefetchPath2(EntityType.PanelBarEntity); testPath.Add(PanelBarEntity.PrefetchPathPanelBarChildren); testPath.Add(PanelBarEntity.PrefetchPathMenu); testPath[0].SubPath.Add(PanelBarEntity.PrefetchPathPanelBarChildren); testPath[0].SubPath.Add(PanelBarEntity.PrefetchPathMenu); testPath[0].SubPath[0].SubPath.Add(PanelBarEntity.PrefetchPathPanelBarChildren); testPath[0].SubPath[0].SubPath.Add(PanelBarEntity.PrefetchPathMenu); EntityCollection<PanelBarEntity> testCollection = new EntityCollection<PanelBarEntity>(); adapter.FetchEntityCollection(testCollection, filter, testPath);
If my description of the problem above is inaccurate, let me know where it is incorrect. If it is correct, refactoring the above code to do the exact same thing using recursion (with a max depth) should be all you need:
public static EntityCollection<PanelBarEntity> GetPanelBarCollection()
{
int maxDepth = 1000;
PrefetchPath2 testPath = new PrefetchPath2(EntityType.PanelBarEntity);
IPrefetchPathElement2 childNode = testPath.Add(PanelBarEntity.PrefetchPathPanelBarChildren);
testPath.Add(PanelBarEntity.PrefetchPathMenu);
AddRecurseNodes(childNode, maxDepth);
EntityCollection<PanelBarEntity> testCollection = new EntityCollection<PanelBarEntity>();
RelationPredicateBucket filter = new RelationPredicateBucket(PanelBarFields.ParentKey == DBNull.Value);
adapter.FetchEntityCollection(testCollection, filter, testPath);
return testCollection;
}
public static void AddRecurseNodes(IPrefetchPathElement2 node, int depthLeft)
{
if (depthLeft > 0)
{
IPrefetchPathElement2 childNode = node.SubPath.Add(PanelBarEntity.PrefetchPathPanelBarChildren);
node.Subpath.Add(PanelBarEntity.PrefetchPathMenu);
depthLeft--;
AddRecurseNodes(childNode, depthLeft);
}
}
(Consider the above as pseudocode--completely off the cuff and without any editor, so it probably won't even compile without some fixing).
Give that a shot and let me know if it does/doesn't do what you're trying to get done.
Phil
Waw, that's a big threads to read in a little time. I know you are not after other ideas but here are my 2 cents:
What you are doing if you succeed in doing it, will result in many SQL queries, one for each Level to prefetchPath it. Since you extensivly make use of PrefetchPaths and their subPaths. By doing the following you can avoid this.
Assuming you need to have the graoh built using Entities: 1- Fetch the entire PanelBar table into an EntityCollection (yes...flat) 2- Use a prefetchPath to fetch the related Menu entities (The above steps will only execute 2 queries)
3- Recursivly build you graph in memory. (Recursion takes time, but you already use it in building the prefetchPaths.)
3.1- Create a new empty EntityCollection of PanelBarEntity. 3.2- With the help of resursion and EntityViews filter the already fetched collection to get The required entities to be inserted in the new EntityCollection graph.
In general the PrefetchPath logic should be doing the above graph construction, but in your case PrefetchPaths might be an overkill if you have a huge or a 100-levels tree to build. And using the above you won't need to pre-calculate the tree depth and you won't need to fetch the TypedList, unless you need it for something else.
Joined: 12-Jan-2008
Hello Walaa,
Thank you for your reply.
Your suggestion is a good one for the specific case of a known entity type. However, the approach I’m attempting is generic and should not be bound to any specific type. If you look at the original code, the function to generate the prefetch path does not reference any specific entity type.
Your suggestion also does not address the actual question of my post. I’m not asking what is the best way to do x, rather I am asking why does the above approach not work as expected?
Any of the three different ways I have posted to generate prefetch paths should (given my level of understanding) work. Phil’s suggestion is essentially a combination of the two test functions I posted. The depth is unnecessary if the function is recursive, because the point of the recursion is to find the depth. If the depth is known, then a simple loop should suffice. But either way, looping through a PrefetchPath2 object and adding additional Elements to it and its subpaths should work, but it doesn’t.
It is possible there is a bug (more likely an oversight or protection) in the llbl framework, however, given the flexible nature of llbl, it’s more likely that I’m missing something in the hooking up of a PrefetchPathElement2 object to the given path. The subpaths and their elements are present, but they are not translating into queries. The question is: why?
Clearly if I believe the above code should produce a given result and it doesn't, then there is a hole my understanding. Understanding why and filling in that hole is more important than finding a work around for a specific case. The work around will not help me in the future avoid a repetition of this problem is some other scenario if I still fail to understand the underlying cause of the problem.
Thank you, Tony.
Joined: 12-Jan-2008
Solved! I made a slight change to the original recursive function and appear to have solved my problem. Although, I have to admit I don't really understand why the code below works and my original approach didn't. But this does work.
GetPrefetchPath() function modified from original post.
private void GetPrefetchPath(PrefetchRecursiveClass args, params IPrefetchPathElement2[] additionalPaths)
{
//the if ensures that a prefetch path is only added once per level
if (args.path.Count == 0)
{
//*************************************
//THIS IS WHERE THE PROBLEM SEEMS TO BE
//*************************************
foreach (IPrefetchPathElement2 path in additionalPaths)
{
args.path.Add(GetPrefetchElement(path));
}
//Add prefetch path for current entity
args.path.Add(GetPrefetchElement(args.ChildPrefetch));
}
int lastIndex = args.path.Count - 1;
//get first layer of child nodes for current node
List<DataRow> rows = dt.AsEnumerable().Where(x => x[args.ParentKey].ToString() == args.Row[args.TableKey].ToString()).ToList();
for (int i = 0; i < rows.Count(); i++)
{
IncDepth();
//GetPrefetchPath(args.GetPrefetchRecursiveClass(rows[i], args.path[0].SubPath), additionalPaths);
GetPrefetchPath(args.GetPrefetchRecursiveClass(rows[i], args.path[lastIndex].SubPath), additionalPaths);
currentDepth--;
}
}
All I have done is change the order in which I add the paths. Instead of having the recursive path added first and then traversing the subpaths with path[0].SubPath, I added the recursive path last and traverse the subpaths using path[path.count - 1].SubPath. Clearly there's must be something more at work here than what I'm seeing.
Otis, if you're reading this and have the time: Any idea why the path works when I use this ordering instead of the original one?
tbarton wrote:
Solved! I made a slight change to the original recursive function and appear to have solved my problem. Although, I have to admit I don't really understand why the code below works and my original approach didn't. But this does work.
GetPrefetchPath() function modified from original post.
private void GetPrefetchPath(PrefetchRecursiveClass args, params IPrefetchPathElement2[] additionalPaths) { //the if ensures that a prefetch path is only added once per level if (args.path.Count == 0) { //************************************* //THIS IS WHERE THE PROBLEM SEEMS TO BE //************************************* foreach (IPrefetchPathElement2 path in additionalPaths) { args.path.Add(GetPrefetchElement(path)); } //Add prefetch path for current entity args.path.Add(GetPrefetchElement(args.ChildPrefetch)); } int lastIndex = args.path.Count - 1; //get first layer of child nodes for current node List<DataRow> rows = dt.AsEnumerable().Where(x => x[args.ParentKey].ToString() == args.Row[args.TableKey].ToString()).ToList(); for (int i = 0; i < rows.Count(); i++) { IncDepth(); //GetPrefetchPath(args.GetPrefetchRecursiveClass(rows[i], args.path[0].SubPath), additionalPaths); GetPrefetchPath(args.GetPrefetchRecursiveClass(rows[i], args.path[lastIndex].SubPath), additionalPaths); currentDepth--; } }All I have done is change the order in which I add the paths. Instead of having the recursive path added first and then traversing the subpaths with path[0].SubPath, I added the recursive path last and traverse the subpaths using path[path.count - 1].SubPath. Clearly there's must be something more at work here than what I'm seeing.
Otis, if you're reading this and have the time: Any idea why the path works when I use this ordering instead of the original one?
It will be very time consuming to read all the posts and all the code, as I find the code a bit hard to follow. The main thing is that it doesnt matter which slot you place your recursive path. So apparently you added the wrong nodes perhaps.
What I do want to add is that this way of fetching a hierarchy isn't really efficient. Better is, as Walaa suggested, to read 1 set and in an O(n) algorithm traverse the list once and build the hierarchy from that. You can do that with one hashtable which contains ParentID as key and Parent instance as value. It depends on the sorting if you need to traverse the list twice. http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=62634&ThreadID=11227