Greetings,

Just to level set everyones expectations I am new to this product and just getting started so excuse some of the newbie questions.

Environment: LLBLGen Pro 2.5 VS 2005 Professional SP1 .NET Framework 2.x

Template Group: Adapter

Schema: UserProfile Role UserProfileRole (Intermediate Table for M-M Relationship between UserProfile and Role)

The issue I am having is trying to get my mind around prefetch paths and how to use them. I have written some code to return a collection of UserProfileEntity of which it contains a member UserProfileRoleEntity and of course that entity contains two member fields one representing the role, RoleEntity and the other the user, UserProfileEntity.

What I am trying to do is just a simple query that returns all user profiles and each profile should also contain the roles.

The end result is one piece of code does not return the roles back while the other one does but if I look at the prefecth path they look the same.

I believe this one topic alone could warrent a guide just to show how to use it properly or give us insight to the power of prefecth paths.

        static void Main(string[] args)
        {
            IPrefetchPath2 prefetchPath = null;
            IPrefetchPathElement2 prefetchPathElement = null;

            EntityCollection myUserProfiles = new EntityCollection(new MyUserProfileEntityFactory());

#if true
           //results missing the role
            prefetchPath = new PrefetchPath2(EntityType.UserProfileEntity);
            prefetchPathElement = MyUserProfileEntity.PrefetchPathUserProfileRole;

            prefetchPathElement.SubPath.Add(MyUserProfileRoleEntity.PrefetchPathRole);
            prefetchPathElement.SubPath.Add(MyUserProfileRoleEntity.PrefetchPathUserProfile);
            prefetchPath.Add(prefetchPathElement);
#else
            //results contains the role and the user profile but not sure why
            prefetchPath = new PrefetchPath2(EntityType.UserProfileEntity);
            prefetchPath.Add(MyUserProfileEntity.PrefetchPathUserProfileRole).SubPath.Add(MyUserProfileRoleEntity.PrefetchPathRole);
#endif
            using (DataAccessAdapter da = new DataAccessAdapter())
            {
                da.FetchEntityCollection(myUserProfiles, null, prefetchPath);
                foreach (MyUserProfileEntity myUserProfile in myUserProfiles)
                {
                    foreach (MyUserProfileRoleEntity myUserProfileRole in myUserProfile.UserProfileRole)
                    {
                        Debug.WriteLine(String.Format("User Id:{0} User Logon:{1} User Secure Key:{2}", myUserProfileRole.UserProfile.UserId, myUserProfileRole.UserProfile.UserLogon, myUserProfileRole.UserProfile.UserSecureKey));
                        Debug.WriteLine(String.Format("Role Id:{0}  Role Name:{1}", myUserProfileRole.Role.RoleId, myUserProfileRole.Role.Name));
                    }
                }
            }
        }

I look forward to your response.

I was reading the LLBLGen Pro documentation and came across this information uner Prefetch Paths and not sure I understand the explanation but began to wonder if this is related to my previous question:

M:N related entities Prefetch Paths can also be used to fetch m:n related entities, they work the same as other related entities. There is one caveat: the intermediate entities are not fetched with an m:n relation Prefetch Path. For example, if you fetch a set of Customer entities and also their m:n related Employee entities, the intermediate entity, Order, is not fetched. If you specify, via another PrefetchPathElement2, to fetch the Order entities as well, and via a SubPath also their related Employee entities, these Employee entities are not the same objects as located in the Employees collection of every Customer entity you fetched.

Hi BrettV,

BrettV wrote:

here is one caveat: the intermediate entities are not fetched with an m:n relation Prefetch Path. For example, if you fetch a set of Customer entities and also their m:n related Employee entities, the intermediate entity, Order, is not fetched.

Yes, this is by design. This scenario is designed for those people that aren't interested in the intermediate entities.

BrettV wrote:

If you specify, via another PrefetchPathElement2, to fetch the Order entities as well, and via a SubPath also their related Employee entities, these Employee entities are not the same objects as located in the Employees collection of every Customer entity you fetched.

A. If you use the M:N prefetch approach, you will be able to:

EntityCollection<EmployeeEntity> allEmployeesFromCertainCustomer = myCustomer.EmployeesViaOrdersCollection;

B. If you use the prefetchPath + subPath aproach, you will be able to:

EmployeeEntity employeeFromCertainCustomerAndCertainOrder = myCustomer.Orders[xxx].Employee;

So, in both A and B, the number of employees should be the same.

int countA = myCustomer.EmployeesViaOrdersCollection.Count;

int countB = 0;
foreach (OrderEntity order in myCustomer.Orders)
{
     if (order.Employee != null)
     {
          countB++;
     }
}

After above code, countA should be equal to countB. Are you experimenting something different?

Additionally: if you want to use A and B together, you obviously want the same entity INSTANCES in the m:n relation collection as you have via the intermediate entity: in that case, use a Context together with the fetch.

Greetings,

I am still not clear on your answer and perhaps I am not being clear so here I go again. If you want to retrieve a collection that is part of a m:n relation and you want to receive both sides in one call is this not possible.

If you look at this example code it behaves differently and I am not sure why.

static void Main(string[] args)
        {
            IPrefetchPath2 prefetchPath = null;
            IPrefetchPathElement2 prefetchPathElement = null;

            EntityCollection myUserProfiles = new EntityCollection(new MyUserProfileEntityFactory());

#if true
         //results missing the role
            prefetchPath = new PrefetchPath2(EntityType.UserProfileEntity);
            prefetchPathElement = MyUserProfileEntity.PrefetchPathUserProfileRole;

            prefetchPathElement.SubPath.Add(MyUserProfileRoleEntity.PrefetchPathRole);
            prefetchPathElement.SubPath.Add(MyUserProfileRoleEntity.PrefetchPathUserProfile);
            prefetchPath.Add(prefetchPathElement);
#else
            //results contains the role and the user profile but not sure why
            prefetchPath = new PrefetchPath2(EntityType.UserProfileEntity);
            prefetchPath.Add(MyUserProfileEntity.PrefetchPathUserProfileRole).SubPath.Add(MyUserProfileRoleEntity.PrefetchPathRole);
#endif
            using (DataAccessAdapter da = new DataAccessAdapter())
            {
                da.FetchEntityCollection(myUserProfiles, null, prefetchPath);
                foreach (MyUserProfileEntity myUserProfile in myUserProfiles)
                {
                    foreach (MyUserProfileRoleEntity myUserProfileRole in myUserProfile.UserProfileRole)
                    {
                        Debug.WriteLine(String.Format("User Id:{0} User Logon:{1} User Secure Key:{2}", myUserProfileRole.UserProfile.UserId, myUserProfileRole.UserProfile.UserLogon, myUserProfileRole.UserProfile.UserSecureKey));
                        Debug.WriteLine(String.Format("Role Id:{0} Role Name:{1}", myUserProfileRole.Role.RoleId, myUserProfileRole.Role.Name));
                    }
                }
            }
        }

What I am trying to do is return a collection of users and their associated roles. The relationship between users and roles is an intermediate table to resolve the m:n relationship.

The code produced by the designer contains the UserProfileEntity and and the intermediate entity UserProfileRoleEntity and it is in this entity the UserProfileEntity and RoleEntity is also present. So my intent was to create a prefetch path to pull back all users and have the intermediate entity UserProfileRoleEntity loaded and its UserProfileEntity and RoleEntity populated.

In the code snippet the true code block does not load as I would expect but the false one does. If you could help me understand why that is happening it would maybe clear up my confusion perhaps.

As far as I can see, both of these code blocks should return the same result.

Would you please post the LLBLGen Pro runtime library version used? Would you please examine the generated SQL Queries in both cases and make sure they return the same results? (ref: LLBLGen Pro manual: "Using the generated code -> Troubleshooting and debugging")

Also the following line is not needed in the first code block:

prefetchPathElement.SubPath.Add(MyUserProfileRoleEntity.PrefetchPathUserProfile);

Greetings,

Okay I have updated my test harness and I am attaching the output for two test runs which fall under case 1 and case 3 in the test harness.

case 1 When i use the prefetch path for case 1 it does not bring back the role information but if I remove the PrefecthPathUserProfile then it does bring back the role information.

case 3 When i use the prefetch path for case 3 it returns the role information as well. So i guess is it the intention of the library to not return the objects that make up the intermediate table that is used to resolve the m:n relationship.

The LLBLGen Pro v2.5 documentation seems to just touch upon this prefecth concept and I think all would benefit if you were to provide a seperate document going into more detail on how to use it and provide examples to correlate back to the documentation.

LLBLGen Pro runtime library version 2.5.07.1019

BrettV wrote:

Greetings,

static void Main(string[] args)
{
     ...

     #if true

     //results missing the role
     prefetchPath = new PrefetchPath2(EntityType.UserProfileEntity);
     prefetchPathElement = MyUserProfileEntity.PrefetchPathUserProfileRole;

     prefetchPathElement.SubPath.Add(MyUserProfileRoleEntity.PrefetchPathRole);
     prefetchPathElement.SubPath.Add(MyUserProfileRoleEntity.PrefetchPathUserProfile);
     prefetchPath.Add(prefetchPathElement);
    
     ...

In the code snippet the true code block does not load as I would expect ...

Try by removing this line:

prefetchPathElement.SubPath.Add(MyUserProfileRoleEntity.PrefetchPathUserProfile);

as this seems to be a circular problem: you are adding a prefetchpath element that in fact is the root element (_UserProfileEntity_).

I did try that and I got the same results as case 3 in my test harness which is what I was trying to achieve but I am not to sure if I understand prefetch paths completely. Is there any more documentation that goes into more detail with examples and produced SQL that can help with the explanation?

I also would like to ask why doesn't the generated code handle creating the intermediate table entries it seems like unnecessary work to have to code that as well and then on the delete side it seems the same issue is present where you must delete the intermediate table rows as well when deleting one side the m:n relationship.

I also would like to ask why doesn't the generated code handle creating the intermediate table entries it seems like unnecessary work to have to code that as well and then on the delete side it seems the same issue is present where you must delete the intermediate table rows as well when deleting one side the m:n relationship.

The explanataion is here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=728&StartAtMessage=0&#3496

BrettV wrote:

I did try that and I got the same results as case 3 in my test harness which is what I was trying to achieve but I am not to sure if I understand prefetch paths completely. Is there any more documentation that goes into more detail with examples and produced SQL that can help with the explanation?

Ok, I'll explain to you more in detail, but there's not that much more to tell really. I think you missed an important element, and after you've understood this, it will be clear

Say I have Customer and Order. When I do this: myOrder.Customer = myCustomer;

LLBLGen Pro makes sure that this is done as well: myCustomer.Orders.Add(myOrder);

Vice versa, this is also true: when you do: myCustomer.Orders.Add(myOrder);

it will make sure that this is done as well: myOrder.Customer = myCustomer;

Ok, back to prefetch paths first. Prefetch paths are actually simple things: you define which edges of a graph of entities you want to fetch. Each node in the graph is an entity type. Per node in the graph you can specify under which conditions you want to fetch that node: sorter, filter etc.

So if you want to fetch the graph with the edges: Customer - Order Order - OrderDetails Order - Employee Customer - Employee (m:n)

You do that by specifying the paths, which are the paths through the graph to reach all nodes. The above graph with 1 m:n edge then becomes in code:

PrefetchPath2 path = new PrefetchPath(EntityType.Customer);  // customer is root
PrefetchPathElement2 orderNode = path.Add(CustomerEntity.PrefetchPathOrders);
orderNode.SubPath.Add(OrderEntity.PrefetchPathOrderDetails);
orderNode.SubPath.Add(OrderEntity.PrefetchPathEmployee);
path.Add(CustomerEntity.PrefetchPathEmployees);

As it's a multi-branch path, I can't specify it on 1 line, so I have to grab the ordernode as it represents 1 branch, the other one is customer-employee.

Ok, this graph fetches the entities as described above and stores them in a connected graph, so I can traverse from Customer to OrderDetails via Order and also to Employee via Customer and via Order. I can also traverse back because LLBLGen Pro makes sure myCustomer.Orders.Add(myOrder) results also in myOrder.Customer = myCustomer, as prefetch paths use the same fetch code as you'd do otherwise.

This means that you don't have to specify the paths BACK from Order to Customer or from Employee to Order, as that's all been taken care of, you specify edges, the rest is done for you.

Ok, Let's move on and discuss uniquing a bit.

WHen you fetch the graph above, you'll notice that Employee is specified twice. LLBLGen Pro doesn't return unique entities by default. The reason is that most of the time you don't need unique entity OBJECTS in your appdomain, and it hurts performance a bit. If you DO NEED to have unique objects (so employee with id 1 in Customer.Employees is the same OBJECT as Customer.Orders[2].Employee, and not a different object (although with the same data), you've to use a context.

So to fetch the graph above to get unique entity OBJECTS, use a Context object, add the customer collection to that context prior to fetching it with the path above and simply fetch the graph. Contexts aren't meant to be used as a cache, you should use them with method scope and if you're planning to keep around the entities for a long time, don't use a context as the graph keeps the context around as well.

Thanks everyone for their responses and especially Otis I think the light bulb is burning brighter now