Navigators to inherited tables

Posts   
 
    
Rosacek
User
Posts: 155
Joined: 18-Mar-2012
# Posted on: 25-May-2016 22:57:09   

Adapter, LLBLGEN Pro v.4.2.16.414

Hi, say I have Order entity subtyped to SalesOrder and PurchaseOrder Then there is Customer : Order as 1:N ...see attached simple project

There is only navigator Customer->Orders, not Customer->SalesOrders and Customer->PurchaseOrders

If I add manually in designer relationship to SalesOrders, then after generation code I can see error in VS

The type 'Sample.RelationClasses.StaticSalesOrderRelations' already contains a definition for 'CustomerEntityUsingCustomerIdStatic' in ...\DatabaseGeneric\RelationClasses\SalesOrderRelations.cs

I have to go into generated code and rename duplicated definition. It works until the next time I generate code from LLBL designer disappointed

Cannot be this solved by LLBL?

Yes, there is workaround, to delete in designer relationship Customer->Orders and add manually model-only relationships Customer->SalesOrders and Customer->PurchaseOrders plus switch off in project settings option "Relationships follow DB FK constraints ..." But if project has many tables then this is awful ...

Attachments
Filename File size Added on Approval
Sample.zip 122,953 25-May-2016 22:57.26 Approved
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 26-May-2016 08:53:19   

Just curious: If the supertype (Order) already has a m:1 relationship with Customer, why would you need to add another one in the subtype (SalesOrder). As far as I can see, you don't have other fields in SalesOrder to use in this new relationship.

So I wonder how did you do it. I think it has to do with 'CustomerIdStatic' shown in your post, but not present in your .llblgenproj project.

David Elizondo | LLBLGen Support Team
Rosacek
User
Posts: 155
Joined: 18-Mar-2012
# Posted on: 26-May-2016 10:49:40   

That was just simple example, therefore SalesOrder and PurchaseOrder doesn't have addtion fields simple_smile

1/ Databinding issue Say I want to see all customers in Devex grid, incl all their SalesOrders and Purchase Orders as master-detail subgrids (Devex calls that "details views"). Means I need fetch Customer entitycollection incl. SalesOrders and PurchaseOrders (using prefetchpath) . But I can see just Customer.Orders subcollection which I cannot bind simply to subgrid.

2/ Prefetch path issue There is no prefetch path from Customer to SalesOrder If I want to use prefetch patch CustomerEntity.PrefetchPathOrders and this gives me tables Order+SalesOrder+PurchaseOrder In order to get just tables Order+SalesOrder (means subtype SalesOrders) I guess I have to use also type filter when fetching.

3/ Copy related collection issue If I want to copy existing customer into new Customer incl. all SalesOrders (not PurchaseOrders), then I copy Customer entity and then iterate Customer.Orders collection and check type for each order there

        Dim cust As New CustomerEntity(1)
        For Each ord As OrderEntity In cust.Orders
            If TypeOf ord Is SalesOrderEntity Then
                'create copy
            End If
        Next

And again If I add manuall model-only relationships Customer->SalesOrder and Customer->PurchaseOrder than after code is generated by LLBL designer I can see error in VS IDE due to duplicated definitions as I mentioned in my original post.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 26-May-2016 20:57:58   

The relation name is constructed based on the RelatedEntity name and the current entity field name pointing to the other entity (FK name).

Typically you can't have more than one relation to the same entity using the same fields.

I can't think of a scenario where in an inheritance hierarchy, one might need to maintain duplicate relations between the same related entity and a Sub & Super types.

If you have a business case, could you please share it, with us, it might turn out that Inheritance is not the best implementation in this case.

Rosacek
User
Posts: 155
Joined: 18-Mar-2012
# Posted on: 26-May-2016 21:30:29   

Hi Walaa, I work on app for automotive industry, for process engineers.

Say you have mfg. line having few machines. An each machine has several positions where tooling or material can be placed.

And process engineers need some receipe which tooling and which material and where should be prepared before production of some product will start, then verify if everything is on proper position, proper feeder, proper settings, ...

Means I have Table Recipe with recipe header info, like product P/N, line ID etc. Then I need to store "rows". Row can be for tooling or for material. They are several fields same like position, some flags etc. But tooling row has fields for ToolingID..., while Material row has fields for Material P/N, Qty, FeederID etc...

Means I have supertype table "Detail" having common fields for both tooling and material rows. And subtables DetailTooling for Tooling and DetailMaterial for Material having exclusive fields for those types. Without inheritance it would be mess.

Then for example I show in grid list of all recipes (from table Recipe) and I want to use detailview (subgrid) for Tooling and detailview (subgrid) for Material. User can see expansion "+" at the very left side of row, after click, row will expand and user can see related tooling on one tabbed subgrid, and related material on the second tabbed subgrid. Simple to do with Devex grid.

And how to bind DetailTooling subgrid, when I can fetch just Recipe.Details ? I need Recipe.DetailToolings and Recipe.DetailMaterial - in this form for databinding.

On the other hand it is useful to have also Recipe.Details to get e.g all "rows" into one grid or report etc.

Everything is fine, just the problem is the duplicated naming issue when I add manually relationship Recipe->DetailTooling and Recipe->DetailMaterial

I was told recently by Otis, that LLBL doesnot detect all relationships in inheritance scenario. OK, I can add model-only relationships manually when I need it. But then generated code should hanlde this situation and generate unique relationname otherwise I end in VS IDE with error due to duplicated name as I mentioned.

When I add manual relationship I can change navigators name. I expected those names could be used to avoid duplicated relationsname. But obviously not used.

I hope, it is more clear what and why I need.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 27-May-2016 11:06:32   

Rosacek wrote:

Hi Walaa, I work on app for automotive industry, for process engineers.

Say you have mfg. line having few machines. An each machine has several positions where tooling or material can be placed.

And process engineers need some receipe which tooling and which material and where should be prepared before production of some product will start, then verify if everything is on proper position, proper feeder, proper settings, ...

Means I have Table Recipe with recipe header info, like product P/N, line ID etc. Then I need to store "rows". Row can be for tooling or for material. They are several fields same like position, some flags etc. But tooling row has fields for ToolingID..., while Material row has fields for Material P/N, Qty, FeederID etc...

Means I have supertype table "Detail" having common fields for both tooling and material rows. And subtables DetailTooling for Tooling and DetailMaterial for Material having exclusive fields for those types. Without inheritance it would be mess.

It wouldn't be ideal, but a mess, not so sure: you e.g. can create different tables for tools/materials, and add relationships to each from Recipe.

It's a set of m:n relationships: Recipe m:n Tools and Recipe m:n Materials, both having an intermediate entity for their particular relationship, which you can 'objectify' (add non-pk fields) so fields defined on the relationship can be expressed (example: 'StartDate' on the relationship Employee - Department if an employee can work for more than one department)

Btw: that material and tool share some fields should never be a reason to use inheritance, if anything, it should be a reason to create an additional entity from these shared fields (but that would mean you get a deeply normalized database, which might be less efficient in practice, i.e. 3rd normal form vs. 5th normal form).

Then for example I show in grid list of all recipes (from table Recipe) and I want to use detailview (subgrid) for Tooling and detailview (subgrid) for Material. User can see expansion "+" at the very left side of row, after click, row will expand and user can see related tooling on one tabbed subgrid, and related material on the second tabbed subgrid. Simple to do with Devex grid.

IMHO with m:n relationships this is easy to do, see above. Please see the UI design as something separate from your entity model: the UI is for offering a way to operate the functionality, not to reflect the entity model and vice versa.

And how to bind DetailTooling subgrid, when I can fetch just Recipe.Details ? I need Recipe.DetailToolings and Recipe.DetailMaterial - in this form for databinding.

On the other hand it is useful to have also Recipe.Details to get e.g all "rows" into one grid or report etc.

Isn't that just a query result?

Everything is fine, just the problem is the duplicated naming issue when I add manually relationship Recipe->DetailTooling and Recipe->DetailMaterial

Yes, because what you want isn't valid in an entity model: Recipe has a relationship with 'Detail', and that's a polymorphic relationship, which implies that the elements at runtime can be of different types. This isn't great for databinding, but it's the same as when you have a List<T> typed property on a class and the List<T> can contain instances of subtypes of T as well. The model works fine with the inheritance you defined, it's just that for databinding it doesn't, you then have to cast, but that's equal to my little List<T> example above.

In general, choose composition over inheritance in this case.

I was told recently by Otis, that LLBL doesnot detect all relationships in inheritance scenario.

I don't recall saying that? It uses 1:1 pk-pk relationships to determine inheritance relationships and it does that OK?

OK, I can add model-only relationships manually when I need it. But then generated code should hanlde this situation and generate unique relationname otherwise I end in VS IDE with error due to duplicated name as I mentioned.

What you are doing is defining duplicate relationships to obtain a code-only workaround: you want to have a non-polymorphic relationship using inheritance. That only works if the relationship is between leafs of inheritance hierarchies or if the entities in the relationship aren't in a hierarchy. Otherwise the relationship is always polymorphic.

Frans Bouma | Lead developer LLBLGen Pro