Hi, I'm using version 1.1.2005.1 Adaptor, and I have a question about adding the same prefetch path twice. This version of LLBLGen doesn't seem to support adding aliases to prefetch paths like you can with relations and predicate expressions. What i'm trying to do is add a prefetch path, or 2 prefetch paths, prefetching from the same table using 2 different predicate expressions on the same table with different aliases. For example, I would like to generate the following query on a ContactEntity prefetch:

Select  * 
From Company c1, Company c2
Where c1.CompanyID = 5 AND c2.CompanyID = 6

When I try to do this however, LLBLGen only returns a result set of c1 companies where CompanyID = 5. What would be nice, is if LLBLGen could support this, and add both result sets (CompanyIDs = 5 and 6) together into a single EntityCollection of Companies.

This is because LLBLGen will currently generate the following from this prefetch path code (note. this is just example code to explain what I’m trying to accomplish):

PrefetchPath2 fetchContact = new PrefetchPath2((int)ContactEntity));
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(Contact.Relations.CompanyEntityOnCompanyID);
bucket.PredicatExpression.Add(FieldCompareValuePredicate(CompanyFields.CompanyID, null, ComparisonOperator.Equal, 5, "c1"));
bucket.PredicatExpression.Add(new FieldCompareValuePredicate(CompanyFields.CompanyID, null, ComparisonOperator.Equal, 6, "c2"));
fetchContact.Add(ContactEntity.PrefetchPathCompany, 0, bucket.PredicateExpression, bucket.Relations);

This prefetch will Generate:

Select  c1*  
From Company c1, Company c2
Where c1.CompanyID = 5 AND c2.CompanyID = 6

The problem with this is that all of the c2 companies are not returned from the query, therefore not returned in the prefetched Contact.Company EntityCollection. I was hoping to be able to either add the same prefetch path twice using different aliases so that both sets of companies (CompanyID = 5 and CompanyID = 6) are returned from the Prefetch. Is this possible, and are there any suggested ideas for performing this action? Any help would be appreciated. We are also moving to 2.0 soon, so if there is a way using that version, which would be helpful too. Thanks!

Just alias C2 and not C1.

Also, remember: prefetch paths fetch related entities, so your query, a return of twice the company info is not what a prefetch path will do.

A prefetchPath is used to fetch related entities / entityCollections.

What's the relation between Contact & Company? is it 1:m , m:1 , 1:1 or m:n? Please post both tables structures, and the result records set you want to obtain.

If the Contact has many comanies, and you only want to fetch some of them (filter them out). Then you should use a filter in the prefetchPath.

PrefetchPath.Add(IPrefetchPathElement2,Int32,IPredicateExpression)

Just alias C2 and not C1.

Thanks for the quick response. I actually tried that and I get the same result. I just noticed that my example was a little off, the genereated SQL queries that I provided were missing the inner joins for the relations that I added. So this is what the prefetch query looks like if I don't alias C1.

Select [Company].*

From 
Contact 
Inner Join [Company]
   ON Contact.CompanyID = [Company].CompanyID 
Inner Join Company c1
   ON Contact.CompanyID = c1.CompanyID 

Where 
[Company].CompanyID = 5
AND c1.CompanyID = 6

This still only brings back an EntityColleciton of Contacts.Company where the CompanyID = 5.

In my example, the contact table can contain multiple rows of the same contacts that contain different companyIDs. So if I have 2 rows for ContactA where the first one has CompanyID = 5, and the other has CompanyID = 6. I would want both contacts to be returned in my querey, both with the same Company EntityCollection that contains both companies. I know this doesn't make much practical sense, but i'm just using it as an example. I will come up with a better one if it will help.

Walaa wrote:

A prefetchPath is used to fetch related entities / entityCollections.

What's the relation between Contact & Company? is it 1:m , m:1 , 1:1 or m:n? Please post both tables structures, and the result records set you want to obtain.

I'm trying to set up a 1:m example. In reality, the ContactID is sitting in the company table, however my example doesn't show that. Either way, the LLBLGen code should be similar.

Hi,

I'm not sure to understand what you got here. Can you please provide the exact structure since this is a little confusing?

If your company table holds a contactID fk, then I don't see how you can have different contacts pointing back to the same company. Similarly, what "several rows of the same contact" means in the context of the contact table? Do you mean you have an intermediate table or something? Isn't that what you're actually looking for?

Then again, as Frans stated, prefetch path will only let you retrieve the naturally joined entities.

Thanks for some more information

Sorry for the confusion. I will be a little more explicate. Remember, I’m only posting an untested example of what I’m trying to do. If you would like the actual code, or a fully tested example I will email it to you because I would rather not post it on this site.

Here is what I have: Table 1 Contact: ContactID int (PK) Name string CreatedDate datetime

Relation: Contact.ContactID (1:n) Company.ContactID

Table 2 Company: CompanyID int(PK) ContactID int Name string CreatedDate datetime

Relation: Company.ContactID (m:1) Contact.ContactID

In this example, there can be multiple company rows with different company names pointing to the same contact row. What I would like to do is retrieve contacts filtered on 2 different companies, and prefetch both companies into the Contact.Company EntityCollection.

So, After I filter and prefetch the Companies like so:

// Create the search bucket
RelationPredicateBucket bucket = new RelationPredicateBucket;
bucket.Relations.Add(ContactEntity.Relations.CompanyEntityUsingContactID);
bucket.PredicateExpression.Add(new PredicateExpression( CompanyFields.CompanyID == 5))
bucket.PredicateExpression.Add(new PredicateExpression (CompanyFields.CompanyID == 6))

// Create the prefetch path filtering out CompanyIDs = 5 and 6
PrefetchPath2 fetchContact = new PrefetchPath2((int)ContactEntity));
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(Contact.Relations.CompanyEntityUsingContactID);
bucket.PredicatExpression.Add(FieldCompareValuePredicate(CompanyFields.CompanyID, null, ComparisonOperator.Equal, 5));
bucket.PredicatExpression.Add(new FieldCompareValuePredicate(CompanyFields.CompanyID, null, ComparisonOperator.Equal, 6, "c1"));

fetchContact.Add(ContactEntity.PrefetchPathCompany, 0, bucket.PredicateExpression, bucket.Relations);

// FetchEntityCollection … 

This code will produce the following SQL during the prefetch:

Select [Company].* 
From 
Contact
Inner Join [Company]
ON [Contact].ContactID = [Company].ContactiD
Inner Join Company c1
ON [Contact].ContactID = c1.ContactID
Where [Company].CompanyID = 5 AND c1.CompanyID = 6

Here is where the problem is. The prefetch query only returns the CompanyEntities where the CompanyID = 5 from the non aliased Company table. I need it to return entities from both Company tables where the CompanyID = 5 from the non aliased table, and the CompanyID = 6 for "c1" Company table. If it were possible to add the same prefetch path twice using an alias for the prefetch path, I would be able to return both company entitites in the Contact.Company EntityCollection. However, this is not supported by LLBLGen.

Now if I don’t add the predicate expression to the prefetch path, I will get all of the companies that have the contactID of the contact it is related to, however I only want to prefetch the companies that I’m filtering on. I also need to make sure I’m searching the company table separately for each companyID that is being searched on, so I need to use aliases. I hope this makes a little more sense, let me know if I need to email you some more code, or create a fully tested example. Thanks, I appreciate the help.

First of all make sure your predicate expression uses AddWithOr to get companies of ID = 5 OR ID = 6.

The PrefetchPath should look like the following:

// Create the prefetch path filtering out CompanyIDs = 5 and 6

PrefetchPath2 fetchContact = new PrefetchPath2((int)ContactEntity));
fetchContact.Add(ContactEntity.PrefetchPathCompany, 0, ((CompanyFields.CompanyID == 5) | (CompanyFields.CompanyID == 6)));

Gotcha, being that my original predicate expression already would have filtered out the Contacts that have Companies 5 AND 6, using OR in the prefetch's predicate expression will work here. Thanks for the help.