LLBLGen Pro 3.1 Final (June 17th, 2011) SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll version 3.1.11.0907 ASP.NET 4.0 Adapter template group SQL Server 2008

I have been trying unsuccessfully for some time to build a prefetch path for this particular query. Can someone please point me in the right direction?

I have four entities: JobPosting, JobApplication, JobPostingCriteria, and JobApplicationCriteriaValue.

JobPosting represents a job opening. JobApplication represents an individual application to a job opening. JobPostingCriteria represents a criteria used to evaluate applications for an opening, such as GPA or test scores. JobApplicationCriteriaValue represents an individual value of a particular criteria.

I am trying to build a prefetch path that will return all the JobApplications (I will filter them later); for each JobApplication prefetch the JobPosting; for each JobPosting prefetch all of the JobPostingCriteria; for each JobPostingCriteria prefetch the JobApplicationCriteriaValue (if it exists) FOR THIS JOBAPPLICATION. The problem I'm running into is that ALL of the JobApplicationCriteriaValue for the JobPostingCriteria are being returned. How do I specify that for each JobPostingCriteria, I want the related JobApplicationCriteriaValue for the JobApplication at the top of my hierarchy?

In other words, I'm trying to write this SQL query:

select ja., jp., jpc., jacv. from JobApplication ja inner join JobPosting jp on ja.JobPostingID = jp.JobPostingID inner join JobPostingCriteria jpc on jp.JobPostingID = jpc.JobPostingID inner join JobApplicationCriteriaValue jacv on jpc.JobPostingCriteriaID = jpc.JobPostingCriteriaID AND ja.JobApplicationID = jacv.JobApplicationID

It's that last predicate "AND ja.JobApplicationID = jacv.JobApplicationID" that's causing the problem.

Here's the last thing I tried:

        IPrefetchPath2 prefetch = new PrefetchPath2(EntityType.JobApplicationEntity);
        var jobPostingPrefetch = prefetch.Add(JobApplicationEntity.PrefetchPathJobPosting);
        var criteriaPrefetch = jobPostingPrefetch.SubPath.Add(JobPostingEntity.PrefetchPathJobPostingCriteriaCollection);
        var criteriaValuePrefetch = criteriaPrefetch.SubPath.Add(JobPostingCriteriaEntity.PrefetchPathJobApplicationCriteriaValueCollection);
        //criteriaValuePrefetch.Relation.CustomFilter = new PredicateExpression(JobApplicationCriteriaValueFields.JobApplicationId == JobApplicationFields.JobApplicationId);
        criteriaValuePrefetch.FilterRelations.Add(JobApplicationEntity.Relations.JobPostingEntityUsingJobPostingId);
        criteriaValuePrefetch.FilterRelations.Add(JobPostingEntity.Relations.JobPostingCriteriaEntityUsingJobPostingId);
        criteriaValuePrefetch.FilterRelations.Add(JobPostingCriteriaEntity.Relations.JobApplicationCriteriaValueEntityUsingJobPostingCriteriaId);
        criteriaValuePrefetch.Filter.Add(JobApplicationCriteriaValueFields.JobApplicationId == JobApplicationFields.JobApplicationId);

        return GenericEntityUtility.GetCollection<JobApplicationEntity>(bucket, prefetch);

Thanks!

At the .Add method you can use an overload to pass an IPredicateExpression, SorterExpression and RelationCollection. So you must use that in your case:

var criteriaValuePrefetch = criteriaPrefetch.SubPath
     .Add(JobPostingCriteriaEntity.PrefetchPathJobApplicationCriteriaValueCollection, 0, filter, relations, null);

More info and examples: http://www.llblgening.com/archive/2009/10/prefetchpaths-in-depth/#filteringandsorting

Thank you for the response. I read through the page you provided and it has great information, but I didn't see anything that addressed my question.

I understand and agree that using the overloads of the Add method provide an alternate way to create a prefetch subpath and set the FilterRelations property all in one statement, but the effect is the same as creating the prefetch subpath first and then setting the FilterRelations property in separate statements, which I have already done (as indicated in the sample code I provided). But this doesn't solve my problem.

Perhaps an example will help clarify my issue:

Say we have two JobPostings, "Business Analyst" and "Technical Analyst". "Business Analyst" has two JobPostingCriteria, "GPA" and "ACT Score". "Technical Analyst" has two JobPostingCriteria, "GPA" and "SAT Score".

Say we have four people who apply to these openings. "Jane Doe 1" applies to "Business Analyst". The JobApplicationCriteriaValue for her GPA is 3.5 and the JobApplicationCriteriaValue for her ACT Score is 28. "Jane Doe 2" also applies to "Business Analyst". "John Smith 1" applies to "Technical Analyst" and "John Smith 2" also applies to "Technical Analyst". Each of these job applicants similarly have JobApplicationCriteriaValues.

So our world looks like this:

JobPostings Business Analyst, ID=1 Technical Analyst, ID =2

JobPostingCriteria BA/GPA, ID=3, JobPostingID=1 BA/ACT, ID=4, JobPostingID=1 TA/GPA, ID=5, JobPostingID=2 TA/SAT, ID=6, JobPostingID=2

JobApplications Doe 1, ID=11, JobPostingID=1 Doe 2, ID=12, JobPostingID=1 Smith 1, ID=21, JobPostingID=2 Smith 2, ID=22, JobPostingID=2

JobApplicationCriteriaValues Doe 1/GPA, ID=311, JobPostingCriteriaID=3, JobApplicationID=11 Doe 1/ACT, ID=411, JobPostingCriteriaID=4, JobApplicationID=11 Doe 2/GPA, ID=312, JobPostingCriteriaID=3, JobApplicationID=12 Doe 2/ACT, ID=412, JobPostingCriteriaID=4, JobApplicationID=12 Smith 1/GPA, ID=521, JobPostingCriteriaID=5, JobApplicationID=21 Smith 1/ACT, ID=621, JobPostingCriteriaID=6, JobApplicationID=21 Smith 2/GPA, ID=522, JobPostingCriteriaID=5, JobApplicationID=22 Smith 2/ACT, ID=622, JobPostingCriteriaID=6, JobApplicationID=22

I want to retrieve all JobApplications. I can do that fine. For each JobApplication, I want to prefetch all JobPostings. I can do that fine. For each JobPosting, I want to prefetch all JobPostingCriteria. I can do that fine. For each JobPostingCriteria, I want to prefetch all JobApplicationCriteriaValues for that JobPostingCriteria AND THE APPLICABLE JobApplication. Instead, what I get is all JobApplicationCriteriaValues for that JobPostingCriteria REGARDLESS OF JobApplication.

When I retrieve the graph, I get this:

JobApplication -> Doe 1, ID=11, JobPostingID=1 JobPosting -> Business Analyst, ID=1 JobPostingCriteria -> BA/GPA, ID=3, JobPostingID=1 JobApplicationCriteriaValue -> Doe 1/GPA, ID=311, JobPostingCriteriaID=3, JobApplicationID=11 Doe 2/GPA, ID=312, JobPostingCriteriaID=3, JobApplicationID=12 *** NO! BA/ACT, ID=4, JobPostingID=1 JobApplicationCriteriaValue -> Doe 1/GPA, ID=311, JobPostingCriteriaID=3, JobApplicationID=11 Doe 2/GPA, ID=312, JobPostingCriteriaID=3, JobApplicationID=12 *** NO! Doe 2, ID=12, JobPostingID=1 ... Smith 1, ID=21, JobPostingID=2 ... Smith 2, ID=22, JobPostingID=2 ...

How do I ensure that the collection only contains the JobApplicationCriteriaValues for the corresponding JobApplication at the top of the graph, and not ALL JobApplicationCriteriaValues for the parent JobPostingCriteria?? Stated another way, JobApplicationCriteriaValue has two foreign keys: one is for JobPostingCriteria, the other is for JobApplication. The first foreign key is being applied but the second is not. How do I apply the second foreign key?

Mmm. I see.

I thought that you already did that:

criteriaValuePrefetch.Filter.Add(JobApplicationCriteriaValueFields.JobApplicationId == JobApplicationFields.JobApplicationId);

That should ensure a WHERE clause filtering those JobApplicationCriteriaValue's. So, I think we should examine your JobPostingCriteria - JobApplicationCriteriaValue because I think that relation is just about the criteria FK, and doesn't include the applicationId.

So, if I understood your relations well, you should add a prefetchPath directly from the application to the applicationcriteriaValue. What entities are directly related to JobApplicationCriteriaValue? and How?

It sounds like you've grasped the problem.

Yes, I added that predicate specifically to ensure the collection of JobApplicationCriteriaValues only contained values that belonged to the JobApplication at the top of the graph...but it doesn't work.

Here's the SQL that is generated:

Generated Sql query: Query: SELECT [JobApplication].[CandidateID] AS [CandidateId], [JobApplication].[CreatedBy], [JobApplication].[CreatedOn], [JobApplication].[DeletedBy], [JobApplication].[DeletedOn], [JobApplication].[DeletedReason], [JobApplication].[JobApplicationID] AS [JobApplicationId], [JobApplication].[JobApplicationStatusID] AS [JobApplicationStatusId], [JobApplication].[JobPostingID] AS [JobPostingId], [JobApplication].[ModifiedBy], [JobApplication].[ModifiedOn], [JobApplication].[Version] FROM [JobApplication]
Generated Sql query: Query: SELECT [JobPosting].[ClientID] AS [ClientId], [JobPosting].[ClosedDate], [JobPosting].[CreatedBy], [JobPosting].[CreatedOn], [JobPosting].[DeletedBy], [JobPosting].[DeletedOn], [JobPosting].[DeletedReason], [JobPosting].[HiringLeadUserID] AS [HiringLeadUserId], [JobPosting].[JobPostingID] AS [JobPostingId], [JobPosting].[JobPostingStatusID] AS [JobPostingStatusId], [JobPosting].[MinimumWeightedScore], [JobPosting].[ModifiedBy], [JobPosting].[ModifiedOn], [JobPosting].[OwnerOrganizationID] AS [OwnerOrganizationId], [JobPosting].[PositionsAvailable], [JobPosting].[PostedDate], [JobPosting].[PostingCode], [JobPosting].[PostingDescription], [JobPosting].[PostingName], [JobPosting].[Version] FROM [JobPosting] WHERE ( [JobPosting].[JobPostingID] IN (@p1, @p2, @p3, @p4, @p5)) Parameter: @p1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 613b8631-090a-4946-8959-fb7230a4aefe. Parameter: @p2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 03b05802-d128-44c8-a2be-a5335416e7b8. Parameter: @p3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3f4a2002-ff32-4277-aaf4-880411b0300d. Parameter: @p4 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 19929ba3-bd20-4a25-8f1c-0bb08b71fc36. Parameter: @p5 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: fb6b5aee-8925-4492-973b-e2a1ee669c85. Generated Sql query: Query: SELECT [JobPostingCriteria].[AccessLevelID] AS [AccessLevelId], [JobPostingCriteria].[CreatedBy], [JobPostingCriteria].[CreatedOn], [JobPostingCriteria].[CriteriaID] AS [CriteriaId], [JobPostingCriteria].[ExpirationOption], [JobPostingCriteria].[IsAttachmentRequired], [JobPostingCriteria].[IsCandidateCriteria], [JobPostingCriteria].[IsRequired], [JobPostingCriteria].[JobPostingCriteriaID] AS [JobPostingCriteriaId], [JobPostingCriteria].[JobPostingID] AS [JobPostingId], [JobPostingCriteria].[ModifiedBy], [JobPostingCriteria].[ModifiedOn], [JobPostingCriteria].[PassingOptionValueID] AS [PassingOptionValueId], [JobPostingCriteria].[PassingScore], [JobPostingCriteria].[SortOrder], [JobPostingCriteria].[Version], [JobPostingCriteria].[Weight] FROM [JobPostingCriteria] WHERE ( [JobPostingCriteria].[JobPostingID] IN (@p1, @p2, @p3, @p4, @p5)) Parameter: @p1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3f4a2002-ff32-4277-aaf4-880411b0300d. Parameter: @p2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 613b8631-090a-4946-8959-fb7230a4aefe. Parameter: @p3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 03b05802-d128-44c8-a2be-a5335416e7b8. Parameter: @p4 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 19929ba3-bd20-4a25-8f1c-0bb08b71fc36. Parameter: @p5 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: fb6b5aee-8925-4492-973b-e2a1ee669c85. Generated Sql query: Query: SELECT DISTINCT [JobApplicationCriteriaValue].[CreatedBy], [JobApplicationCriteriaValue].[CreatedOn], [JobApplicationCriteriaValue].[ExpirationDate], [JobApplicationCriteriaValue].[IsNA] AS [IsNa], [JobApplicationCriteriaValue].[JobApplicationCriteriaValueID] AS [JobApplicationCriteriaValueId], [JobApplicationCriteriaValue].[JobApplicationID] AS [JobApplicationId], [JobApplicationCriteriaValue].[JobPostingCriteriaID] AS [JobPostingCriteriaId], [JobApplicationCriteriaValue].[ModifiedBy], [JobApplicationCriteriaValue].[ModifiedOn], [JobApplicationCriteriaValue].[OptionValueID] AS [OptionValueId], [JobApplicationCriteriaValue].[Value], [JobApplicationCriteriaValue].[Version] FROM ((( [JobPosting] INNER JOIN [JobApplication] ON [JobPosting].[JobPostingID]=[JobApplication].[JobPostingID]) INNER JOIN [JobPostingCriteria] ON [JobPosting].[JobPostingID]=[JobPostingCriteria].[JobPostingID]) INNER JOIN [JobApplicationCriteriaValue] ON [JobPostingCriteria].[JobPostingCriteriaID]=[JobApplicationCriteriaValue].[JobPostingCriteriaID]) WHERE ( [JobApplicationCriteriaValue].[JobPostingCriteriaID] IN (@p1, @p2) AND ( [JobApplicationCriteriaValue].[JobApplicationID] = [JobApplication].[JobApplicationID])) Parameter: @p1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 93f4fe52-02d1-4900-9f83-897ba6458479. Parameter: @p2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: f65bacb2-d787-4178-b68c-7b94776121e6.

As you can see, the last query includes that predicate, "[JobApplicationCriteriaValue].[JobApplicationID] = [JobApplication].[JobApplicationID]", but since LLBLGen generates four separate queries, one for each entity, this doesn't do anything to limit what is being returned, and doesn't seem to be taken into account when LLBLGen adds the entities into the parent collection.

It appears to me that LLBLGen uses the IPrefetchPathElement2.Relation to determine how to match up the parent/child entities returned from each SQL statement and therein lies the problem. The JobApplicationCriteriaValue entities can't be matched to the appropriate JobPostingCriteria entity based on a single relationship and there doesn't seem to be any way to specify multiple relationships for IPrefetchPathElement2.Relation or to tell LLBLGen to use any other relationship.

Does that mean this is a shortcoming of LLBLGen, or is there some other way to do this? This seems like an ability LLBLGen would need to have since this issue could potentially arise for any query that includes an entity that joins to two or more other entities in the query.

In response to your last paragraph:

you should add a prefetchPath directly from the application to the applicationcriteriaValue.

I could certainly do this, which would populate the JobApplicationCriteriaValue collection for each JobApplication, but that collection would contain all of the JobApplicationCriteriaValues for that JobApplication. I need to get the JobApplicationCriteriaValues for that JobApplication AND each individual JobPostingCriteria. And lest you ask, I cannot simply retrieve the JobPostingCriteria from the JobApplicationCriteriaValues because I need ALL the JobPostingCriteria and the associated JobApplicationCriteriaValue if one is defined (i.e. the collection will contain 0 or 1 elements), whereas if I start with the JobApplicationCriteriaValues and retrieve the associated JobPostingCriteria I will end up with only those JobPostingCriteria that have an associated JobApplicationCriteriaValue.

What entities are directly related to JobApplicationCriteriaValue? and How?

Of our four entities, JobApplicationCriteriaValue is directly related to (i.e. has a foreign key to) both JobApplication and JobPostingCriteria.

This is a reasonly common pattern that I've seen implemented in a variety of situations. From a generic point of view, JobPosting is the template header and JobPostingCriteria is the template detail (so it has a foreign key to the template header). JobApplication is the instance header (so it has a foreign key to the template header) and JobApplicationCriteriaValue is the instance detail (so it has a foreign key to both the instance header and the template detail). If the data is normalized and blank instance detail rows are not created, then it seems to me the only way to get all of the related data is to walk the graph from instance header -> template header -> template detail -> item detail (on both foreign keys), which is exactly what I'm trying to do.

Yeah that's a common scenario, but I think you are not approaching in the correct way. That is, the way you are constructing the graph path.

Imagine for one second that you only have those three tables: JobApplication - (1:n) - JobApplicationCriteriaValue - (m:1) - JobPostingCriteria

Now, if you want to construct a graph like the one you are suggesting, the best way would be: | JobApplication |-- JobApplicationCriteriaValue |---- JobPostingCriteria

... because your m:n entity (JobApplicationCriteriaValue) is the one that can connect your applications and your posting criteria. If I reverse this I would end up with the same problem.

So, now add the remaining elements: | JobApplication |-- JobApplicationCriteriaValue |---- JobPostingCriteria |------ JobPosting

... and in code:

var prefetch = new PrefetchPath2(EntityType.JobApplicationEntity);
prefetch.Add(JobApplicationEntity.PrefetchPathJobApplicationCriteriaValueCollection)
     .SubPath.Add(JobApplicationCriteriaValueEntity.PrefetchPathJobPostingCriteria)
          .SubPath.Add(JobPostingCriteriaEntity.PrefetchPathJobPosting);

Does that work now?

That's a good thought. I considered that for a bit as well and tried to address that in my previous reply. Unfortunately, not every JobPostingCriteria has a JobApplicationCriteriaValue defined yet, but we still need to retrieve the JobPostingCriteria. For example, if we want to display the list of criteria and values for a specific application, we need to retrieve all of the criteria so we can display all of the defined criteria (i.e. GPA, SAT score, etc.) along with the values that have been added thus far and allow them to add values for any criteria that do not have values.

If, say, only the GPA has been entered, the SAT score would not have a value. So following the logic you proposed, we would end up displaying only the GPA criteria and its value. We would have no knowledge of the existence of the SAT score criteria so it would not be displayed and there would be no way for the user to update it.

As I was thinking about this, I realized I made an obvious mistake in my original sample SQL query which may have led you to the wrong conclusion. The last join should have been a left outer join, not an inner join:

select ja., jp., jpc., jacv. from JobApplication ja inner join JobPosting jp on ja.JobPostingID = jp.JobPostingID inner join JobPostingCriteria jpc on jp.JobPostingID = jpc.JobPostingID left join JobApplicationCriteriaValue jacv on jpc.JobPostingCriteriaID = jacv.JobPostingCriteriaID AND ja.JobApplicationID = jacv.JobApplicationID

Is there really no way in LLBLGen to represent an outer join across two foreign keys in different tables?

jmercier wrote:

That's a good thought. I considered that for a bit as well and tried to address that in my previous reply. Unfortunately, not every JobPostingCriteria has a JobApplicationCriteriaValue defined yet, but we still need to retrieve the JobPostingCriteria.

I see. But that not how PrefetchPaths work. When you use them you are saying: fetch all B's associated to A. And for that B's fetch all C's associated, etc. In a way, C's are not aware of A, C's just know their parents (B's subset). So in the case you mention, you should use two branches: one for JobApplicationCriteriaValue (the one I proposed above) and one for JobPosting (the one you are introducing with your explanation). Now the graph looks like this:

| JobApplication |-- JobApplicationCriteriaValue |---- JobPostingCriteria |------ JobPosting |-- JobPosting |---- JobPostingCriteria

and in code:

var prefetch = new PrefetchPath2(EntityType.JobApplicationEntity);

prefetch.Add(JobApplicationEntity.PrefetchPathJobApplicationCriteriaValueCollection)
     .SubPath.Add(JobApplicationCriteriaValueEntity.PrefetchPathJobPostingCriteria)
         .SubPath.Add(JobPostingCriteriaEntity.PrefetchPathJobPosting);

prefetch.Add(JobApplicationEntity.PrefetchPathJobPosting)
     .SubPath.Add(JobPostingEntity.PrefetchPathJobPostingCriteria);

That resolves the problem. Now you have one branch for JobApplicationCriteriaValues and one branch for JobPostings, even when they have not any criteria values.

The next concern you will ask me is: But those JobPostings of the second branch doesn't have any information about criteria values. There is two way to solve that:

a. Extend the second branch to include criteria values in the graph of that second branch. The problem is that you have two branches now: one for query the posting and another to query the criteria values. But anyway that works.

b. Use a context. A context warranties that any fetched entity will exists just once in a graph (based on their PK). I'm not really sure if this will work as I never tried in a similar scenario but it's worth to try:

var prefetch = new PrefetchPath2(EntityType.JobApplicationEntity);

prefetch.Add(JobApplicationEntity.PrefetchPathJobApplicationCriteriaValueCollection)
     .SubPath.Add(JobApplicationCriteriaValueEntity.PrefetchPathJobPostingCriteria)
         .SubPath.Add(JobPostingCriteriaEntity.PrefetchPathJobPosting);

prefetch.Add(JobApplicationEntity.PrefetchPathJobPosting)

// ...
Context myContex = new Context();
someAdapter.FetchEntityColection(applications, null, prefetch, myContext);

... in theory, after that fetch, you will be able to read both paths interdependently but they will share the entities in common.

But, with all above recommendations, there is something clear: you are trying to meet two different needs with one graph, and that is now always possible. It's like if I want to show a list of products and then based on the product selection I show the orders that contains such products. Well I can't show the product's list based just on the subset of some used products in some orders.

In your case, I think the two-branches approach is the one you should take. Yes, it's another fetch but that is unavoidable.

Please let me know if that is an acceptable solution

Thank you for your reply.

That is certainly an alternate approach, though it requires more processing on the client side to essentially join together two different branches that could be joined much more easily by the server. As such, no, I don't believe it solves the original problem, i.e. how do I build a prefetch path for LLBLGen that accomplishes the same thing as the sample SQL query I provided, but yes, it is a different approach to solve that problem.

I believe your statement, "you are trying to meet two different needs with one graph, and that is now always possible," indicates that LLBLGen is not able to solve this problem. I disagree that I am trying to meet two different needs: all of this data needed for a single, coherent UI. Furthermore, this data can be easily queried and returned (correctly joined) using SQL, so I anticipated that LLBLGen would be able to generate a comparable query and return the data in a form that is at least as usable as the data returned by the SQL query. But evidently this is not something that LLBLGen is designed to do.

Thank you for your assistance!