Hi. I have been trying to get prefetch to work with adapter. So far so good - until I got to M:N relations. I saw the documentation about it that states:

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.

but I don't totally understand. I have a Windows c# application with this in the form load:

public EntityCollection customers;
customers = new EntityCollection(new CustomersEntityFactory());
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CustomersEntity);
prefetchPath.Add(CustomersEntity.PrefetchPathOrders).SubPath.Add(OrdersEntity.PrefetchPathEmployees);       
IRelationPredicateBucket filter = null;
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(customers, filter, prefetchPath);

I thought this would fill all customers, all orders and all employee objects. I then would be able to, without making any more database calls, look up an employee name based on a customer's order. So I have a button on the form that has this code:

CustomersEntity selectedCustomer = (CustomersEntity)customers[ 
    customers.Find( new EntityPropertyDescriptor2(
        EntityFieldFactory.Create(CustomersFieldIndex.CustomerID), 
        typeof(CustomersEntity), false), 
        "CHOPS" )];
gridOrders.DataSource = selectedCustomer.Employees;

I would expect the grid to be filled with all the employees involved in selectedCustomer's orders but the grid is empty. I also did the following:

gridOrders.DataSource = selectedCustomer.Orders;

and I can see the orders for CHOPS in the grid. However, when I click on the plus sign to see the related links, all I see are links to Products and Order Details. I would like to see Employees also. Could you explain how to do this? My goal is to at the program startup, load all customers meeting a certain criteria (I took the criteria out for the example), load their related orders, and load the employees associated with those orders. I would then like to manipulate the data for as long as needed until I am ready update to the server. Is this possible? If you like I have the sample project where I have tried this and can email it if needed. Thanks very much for your help.

As the documentation says, the intermediate entities are not fetched. This is by design, as it would cause more data, another query to fetch these objects you probably don't want.

In your case, Order is the intermediate entity for the m:n relation between Customer and Employee. So if you want all Employees for every customer, you have to specify that in your path. Doing that will not fetch the orders.

The opposite is also true for now: if you fetch the path you're fetching, the m:n relation is not fetched as well. So your graph does represent an m:n relation, it is not filling Customer's Employees collection nor is it filling Employee's Customers collection.

If you want all Customer's Employees collection as well, add that to the prefetch path as well. This will require an extra query to fetch the employees, at this time. By design doing something with a given collection is not affecting another collection, which is the base for the decision to not fetch the m:n relation collection as well even though the base for that m:n relation collection is read.

The reason for this is that it will get very complex when I do fetch that m:n related data as well when you fetch orders + employees for each customer: what to do when you remove an order entity from the orders collection? Remove the employee from the m:n relation as well? "Yes" you'd say perhaps, but how to detect that, inside the EntityCollection representing Orders? It then requires a supervising piece of code looking for these kind of removals. Now, this is an easy example. But what if you change the EmployeeID of a given order in the Orders collection from value A to B? This will likely to remove an employeeentity from the m:n relation collection. Oh, you made a mistake and set the value back to A. Hmm... how to get that Employee entity back into the Employees collection .

I admit, it would be great if that was all taken care of, that m:n relations were collections which were dynamically mirroring the effects of the state of other relations. Due to the disconnected behavior, this is very hard to do, if not impossible...

Hi. Thanks very much for your response. Your answer explains very well how this is working. My confusion was that I thought you had to add the logic to get the employees through the subpath only. I did not quite get that you could just add the employees as another prefetch and it would work. If I understand correctly, doing something like this:

customers = new EntityCollection(new CustomersEntityFactory());
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CustomersEntity);
            prefetchPath.Add(CustomersEntity.PrefetchPathOrders).SubPath.Add(OrdersEntity.PrefetchPathOrderDetails);
            prefetchPath.Add( CustomersEntity.PrefetchPathEmployees );
            IRelationPredicateBucket filter = null;
            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection(customers, filter, prefetchPath);

will give the result I am looking for and let me do the following:

CustomersEntity selectedCustomer = (CustomersEntity)customers[ customers.Find( 
                new EntityPropertyDescriptor2(EntityFieldFactory.Create(CustomersFieldIndex.CustomerID), 
                typeof(CustomersEntity), false), "CHOPS" )];
gridEmployees.DataSource = selectedCustomer.Employees;

although at the cost of an extra query. Thanks again!

Thanks for your great help with this. I have now gotten to a different part of the project where I want to add/modify/save the related collections and I cannot figure out how to do it. I have the following:

Table A

LinkTable A -- B (with link ids connecting table a and table b

Table B

of course each table has a corresponding collection generated by llblgen.

I am using the prefetch code with Adapter here to fill collection a, the link collection and collection b very similar to the example in the message above. That works great. Now I would like to modify and add and delete records for table b. I have tried various things including the obvious of getting the related collection and trying to add and modify them that way. I can modify and add new items and it reflects correctly in the datagrid I am using (although I could not get the datagrid to automatically do this using databinding - I manually catch the change and programatically insert/modify the items). However, when I call the adapter.SaveCollection for table A (really collection A), the related Table B collection is not saved. I also do not see the modified entities in the DirtyEntites array for colleciotn B. The added/modifed items in collection A are correctly updated. I understand that it is difficult for Llblgen to do this "automagically" I would just like to know what the best strategy is for accomplishing my goal of saving the Collection B entites is. Thanks very much for any help.

It should recursively save the B collection as well as the intermediate table changes. I'm not sure how you reflect the changes to B in the B collection in the Linktable's collection of B. Could you paste some code?

Hi. Here is some code using Northwind:

In a ButtonLoad_click: This code used prefetch to setup the data for the grids:

// call the database
public EntityCollection customers;
customers = new EntityCollection(new CustomersEntityFactory());
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CustomersEntity);
            prefetchPath.Add(CustomersEntity.PrefetchPathOrders).SubPath.Add(OrdersEntity.PrefetchPathOrderDetails);
            prefetchPath.Add( CustomersEntity.PrefetchPathEmployees );
            IRelationPredicateBucket filter = null;
            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection(customers, filter, prefetchPath);

//find a customer I am interested in
CustomersEntity selectedCustomer = (CustomersEntity)customers[ customers.Find( 
                new EntityPropertyDescriptor2(EntityFieldFactory.Create(CustomersFieldIndex.CustomerID), 
                typeof(CustomersEntity), false), "CHOPS" )];

// set collection from read only so I can modify it once it is in the grid
selectedCustomer.Employees.IsReadOnly = false;

// set the grid to the employees
thegrid.DataSource = selectedCustomer.Employees;

At this point the grid fills up correctly with the employees. I can see all the relations if I click on the plus sign, etc. Now lets say I go change the LastName and FirstName of a few employees in the grid. After that I will click on a Save button.

In a buttonSave_Click: DataAccessAdapter adapter = new DataAccessAdapter(); adapter.SaveEntityCollection( customers, true, true );

However, the employees entities do not save. I have tried this in my real project without the grids but the related tables still do not work.

Actually, as a side note - as I am playing around with the relations on the grid, I notice that the customers related to the employee are empty... Perhaps that is part of the problem?

Thanks for any help with this. I have the Northwind project available if you would like to see it.

the Customers - Employees collection is an m:n relation and these are read-only. The problem is: if you want to add an employee to the Employees collection, you also need to add an Order entity as that's the entity creating the real relation.

If you want to save the changes to that collection, you should save it manually.

Thanks very much for the help! It works great when I do it manually.

Can someone please give me an example of what is meant by "do it manually"? I have a similar situation in that I have a

"News" entity/table "Company" entity/table and a "NewsCompany" entity/table.

I am using the adapter model. On my winform I have a listbox full of "Selected Companies". I am adding the companies to the News.Companies Collection as they are added to the listbox. When I call SaveEntity() on News, the companies are not saved. The newly added companies are also not marked as dirty. How do I figure out which ones are new? I am going to try to add to the NewsCompany Collection too. Is that what you mean by "do it manually?"

PS - i am very impressed with your product and the speed of your responses so far! Keep up the good work!

slackboy13013 wrote:

Can someone please give me an example of what is meant by "do it manually"? I have a similar situation in that I have a

"News" entity/table "Company" entity/table and a "NewsCompany" entity/table.

I am using the adapter model. On my winform I have a listbox full of "Selected Companies". I am adding the companies to the News.Companies Collection as they are added to the listbox. When I call SaveEntity() on News, the companies are not saved. The newly added companies are also not marked as dirty. How do I figure out which ones are new? I am going to try to add to the NewsCompany Collection too. Is that what you mean by "do it manually?"

Yes, m:n relations are read-only as they're an interpretation of a 3-entity relation, i.e.: there is always an intermediate entity involved. As that intermediate entity isn't always hidable (e.g. orderdetails in the m:n relation order - product) by definition in LLBLGen Pro, you always have to use that intermediate entity, so consistency is preserved.

So, when you're adding a new Company to a News object, you should do that by first creating a NewsCompany object and assign its News property to the News object to add the company to and its Company property to the Company to add. When you now save News, the intermediate entity and the Company entity are properly saved.

PS - i am very impressed with your product and the speed of your responses so far! Keep up the good work!

Thanks!

I have the following code ...

        Dim prefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.CaseEntity, Integer))
        prefetchPath.Add(CaseEntity.PrefetchPathApplicants).SubPath.Add(CaseApplicantEntity.PrefetchPathAddresses)
        prefetchPath.Add(CaseEntity.PrefetchPathLoan)
        prefetchPath.Add(CaseEntity.PrefetchPathProperty)
        prefetchPath.Add(CaseEntity.PrefetchPathQuickReference)
        Dim CaseToReturn As New CaseEntity(sCaseID)
        Dim adapter As New DataAccessAdapter

        adapter.FetchEntity(CaseToReturn, prefetchPath)

This is fine but now I need the Applicant's Employers - do I just add the following line under the existing prefetch for the Applicant's Addresses or is there another way that this must be done. Basically, I need 2 subpaths for the Applicant's prefetch:

        prefetchPath.Add(CaseEntity.PrefetchPathApplicants).SubPath.Add(CaseApplicantEntity.PrefetchPathEmployers) 

if you want more than one subpath, then you may do the following:

IPrefetchPathElement2 prefetchPathElement = CaseEntity.PrefetchPathApplicants;
prefetchPathElement.SubPath.Add(CaseApplicant.PrefetchPathAddresses);
prefetchPathElement.SubPath.Add(CaseApplicant.PrefetchPathEmployers);

prefetchPath.Add(prefetchPathElement);

Otherwise you will have a runtime error if you added more than one of the same prefetchpath item to the same root

I managed it with this:

prefetchPath(0).SubPath.Add(CaseApplicantEntity.PrefetchPathEmployers)

Although I'm sure your solution would also work..

I have another question now. I can return the CaseEntity object and I seem to be able to read the data ok, e.g.

CaseEntity.Applicants.Count etc..

However , are there any examples of how to read the specific fields, write to them and create new records e.g.

CaseEntity.Applicants(0).Employers.Add .... is not available.

CaseEntity.Applicants(0).FirstName is not available..

I managed to see the field names by defining an Applicant object and setting it to the CaseEntity.Applicants(0) - then I could use object.FirstName.dbValue etc.. however, this is fine for reading (although perhaps a slight overhead in creating the objects each time) but is that the correct way to read/set values?.

Any examples of how to read, write and create new entities would be very useful.

ok first of all you don't need to read or set the property's dbValue, you can use it to see hat was the original property's value returned from the database if you happened to change the property's value.

CaseEntity.Applicants.Count

I'm assuming that Applicants is a collection of Applicant Then CaseEntity.Applicants.Count will return the number of items in this collection.

However , are there any examples of how to read the specific fields, write to them and create new records e.g.

if you want to add a new applicant to the collection then:

ApplicantEntity Applicant = new ApplicantEntity();
// fill its fields
CaseEntity.Applicants.Add(Applicant);

to access items from a collection then you will have to cast the item to its type:

((ApplicatnEntity)CaseEntity.Applicants.Items[0]).FirstName
((ApplicatnEntity)CaseEntity.Applicants.Items[0]).Employers.Add();

and so on.

((ApplicatnEntity)CaseEntity.Applicants.Items[0]).FirstName ((ApplicatnEntity)CaseEntity.Applicants.Items[0]).Employers.Add();

Tried converting to Vb.NET... no joy. Is that the correct syntax?

Coolcoder wrote:

((ApplicatnEntity)CaseEntity.Applicants.Items[0]).FirstName ((ApplicatnEntity)CaseEntity.Applicants.Items[0]).Employers.Add();

Tried converting to Vb.NET... no joy. Is that the correct syntax?

VB Translation of the above code should be:

CaseEntity.Applicants.Items(0).FirstName
CaseEntity.Applicants.Items(0).Employers.Add()

or worst case:

Ctype(CaseEntity.Applicants.Items(0), ApplicatnEntity).FirstName
Ctype(CaseEntity.Applicants.Items(0), ApplicatnEntity).Employers.Add()

Yes, it would seem I need to do this....

With (CType(CType(_CaseEntity.Applicants.Items(0), EntityClasses.CaseApplicantEntity).Employers.Items(0), EntityClasses.CaseApplicantEmployerEntity))

                    Me.txtEmpName.Text = .EmployerName
                    Me.txtEmpOccupation.Text = .Occupation
                    Me.txtEmpPostcode.Text = .EmployerAddressPostcode
                    Me.txtEmpAddress1.Text = .EmployerAddressLine1
End With

Crazy but it works..