Prefetch path question

Posts   
 
    
llblboy
User
Posts: 15
Joined: 18-Apr-2018
# Posted on: 21-Dec-2018 17:28:03   

Hello all, Sorry if I'm asking a question that has been answered already, but I have been struggling for the last couple days with a problem I don't know how to solve. I'm new to the concept of prefetch paths, so I read https://www.llblgen.com/Documentation/2.6/Using%20the%20generated%20code/SelfServicing/gencode_prefetchpaths.htm (I have several questions about this article, but that is for another day I suppose).

Currently, I'm not even sure what I'm getting back from the Db. It appears (from stepping through the program) that I'm getting back what is called an HoursOfService Violation (in this particular instance) is triggered when an employee works more than 6 days consecutively. When the report is run (to show violations for a month) it shows (for one violation):

Date / time shift started Shift Type 12/11/18 3 A Type2 12/12/18 4 A Type1 12/13/18 5 A Type2 12/14/18 6 A Type2 12/15/18 1 P Type1 12/15/18 1 P Type1 12/15/18 1 P Type1 12/16/18 12 P Type1 12/16/18 12 P Type1 12/17/18 3 A Type2

I would like it to show JUST 7 days (the six days prior to the violation and the day of the violation). I honestly feel like I'm shooting in the dark as senior developers here are confused about prefetch paths as well.

Here is some of the code I "think?" applies to the fetching of the Violation... but I'm shooting in the dark. Also, holidays are coming up and I'm also working on a couple other tasks, so I may be slow in responding at times. Thank you for your help and patience.

Public Function GetHOSViols(ByVal startDate As DateTime, ByVal endDate As DateTime) As EntityCollection(Of HosTour_HosViolationTypeEntity) Dim hosViolsCollection As New EntityCollection(Of HosTour_HosViolationTypeEntity) Dim adapter As DataAccessAdapter = Client.Remoting.BaseService.GetAdapter()

' Create a filter by date.
' ToDo: Fix Predicate Expression to filter based on a child relationship
Dim filter As RelationPredicateBucket = New RelationPredicateBucket()
filter.PredicateExpression.Add(New FieldBetweenPredicate(HosTour_HosViolationTypeFields.DateOfViolation, Nothing, startDate, endDate))

' Create all prefetch path scenarios
' Adds specified element to the path (EntityType.HosTour_HosViolationTypeEntity, Integer)
Dim prefetch As New PrefetchPath2(CType(EntityType.HosTour_HosViolationTypeEntity, Integer))

With prefetch.Add(HosTour_HosViolationTypeEntity.PrefetchPathHdt).SubPath
    With .Add(HdtEntity.PrefetchPathHa).SubPath
        .Add(HaEntity.PrefetchPathEmpPos)
        .Add(HaEntity.PrefetchPathHosCd)
        .Add(HaEntity.PrefetchPathEmpTyp)
    End With

    .Add(HdtEntity.PrefetchPathHosComSvc)

    .Add(HdtEntity.PrefetchPathHosDeadheading)

    .Add(HdtEntity.PrefetchPathEmp)
    .Add(HdtEntity.PrefetchPathAmendRec)
End With

prefetch.Add(HosTour_HosViolationTypeEntity.PrefetchPathHosViolationType)

adapter.FetchEntityCollection(hosViolsCollection, filter, prefetch)

hosViolsCollection.Sort(HosTour_HosViolationTypeFieldIndex.DateOfViolation, ComponentModel.ListSortDirection.Ascending)

Return hosViolsCollection

End Function

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Dec-2018 08:49:31   

You're using v2.6?

Anyway, prefetch paths is a way to fetch related data with the main query. So if you fetch customers and you also want to fetch their related order entities, you can specify a prefetch path for Customer and as sub path Order (so it contains Customer - Order).

The runtime will then first fetch the customers, and based on the entities in that set, create a query to fetch all their order entities (in 1 go!) and merge the orders with the customer they're related to in memory and return that.

This is called a graph (it's basically a simple form, a tree), and the example above has 2 nodes, customers and orders. If you want to fetch a more complicated tree of elements, you have to specify more nodes in the path. So in the above example, if you want to fetch customers, their related orders, per order the related orderlines and the employee who filed the order, the tree has 2 branches (at order: -> OrderLInes and -> Employees). Just draw it out on paper.

So in the example above we then have: Customer -> Order -> OrderLines |-----> Employee

We can specify this prefetch path by using Customer as the root (the type you pass to the PrefetchPath constructor). Then add a SubPath element for Order. To that element we add a subpath element for OrderLines and a subpath element for Employee.

If we pass that path to the fetch for Customers, we get a query for Customers, a query for Order, a query for OrderLines and a query for Employee. Everything is merged into the right entities for you by the runtime and you get a set of customers back from the runtime which contain each a set of order entities related to that customer and inside each order you'll have a set of orderlines related to that order and a reference to that employee.

Frans Bouma | Lead developer LLBLGen Pro
llblboy
User
Posts: 15
Joined: 18-Apr-2018
# Posted on: 26-Dec-2018 17:15:21   

Actually I'm using 3.5 - hopefully we will be upgrading soon.

Thank you for explaining more about how prefetch paths work. I think I understand a bit better now.

The original question I had was resolved by a senior developer. I'm not entirely sure how he did it, but it had to do with adding a predicate expression.

Many thanks, Jonathan