Hello,

I'm migrating from classic ASP to .NET and I was really wanting to use LLBLGENPRO. I've been studying .NET for about 6 months and i've got a good basic understanding of how to use the language. LLBLGen Seems like it could save me alot of time if I could understand how to properly setup my database that is? I'd really appreciate if someone could take the time to help me understand how to set this up.

I've got three tables, Providers (base table) and, ProviderAgent and ProviderOffice which I want to be subtypes of Providers. So i've setup Providers to have a ProviderID PK and a ProviderType int field. I have both ProvderAgent and ProviderOffice using ProviderID as its PK. I want to make ProviderType indicate where the subtype information should be drawn from the ProviderAgent or ProviderOffice.

Maybe i'm just not understanding this correctly. I'd like to have Provider (contains information relative to all providers) then subtypes ProviderAgent (contains all provider info and an extra field or two), ProviderOffice (contains all provider info and an extra field or two) and be able to return the correct type of Provider when given a simple ProviderID key. Is this possible?

Thanks to all who would consider helping me, I really appreciate it!

-Kas

These psuedo tables represent one possible solution:

Provider ProviderID INT [PK] FirstName LastName ...

ProviderOffice ProviderOfficeID INT [PK] ProviderID INT [FK] StreetAddress City ...

ProviderAgent ProviderAgentID INT [PK] ProviderID INT [FK]

Example 1) Find all providers with an office

SELECT * FROM Provider a INNER JOIN ProviderOffice b ON a.ProviderID = b.ProviderID

Example 2) Find all providers with an agent

SELECT * FROM Provider a INNER JOIN ProviderAgent c ON a.ProviderID = c.ProviderID

Example 3) Find all providers with both an office and an agent

SELECT * FROM Provider a INNER JOIN ProviderOffice b ON a.ProviderID = b.ProviderID INNER JOIN ProviderAgent c ON a.ProviderID = c.ProviderID

Example 4) Return all providers with an office or an agent, if they have them

SELECT * FROM Provider a LEFT JOIN ProviderOffice b ON a.ProviderID = b.ProviderID LEFT JOIN ProviderAgent c ON a.ProviderID = c.ProviderID

This example doesn't use a ProviderType as the relationships handle the association. You could add it to the Provider entity if its required, but it isn't necessary.

That's actually almost identical to the setup i've been trying make work my question is this, lets say i have another table(entity) called Posting which has a FK to ProviderID how can I tell what type of Provider this Posting is refering to so if I want to display the provider details when the Posting is clicked on. Is it something like:

VB.NET

Dim pro As ProviderEntity(proID) If pro.ProviderAgent.ProviderAgentID > 0 Then 'Provider is agent? End

If pro.ProviderOffice.ProviderOfficeID > 0 Then 'Provider is office? End If

Thanks a million, your help with this is invaluable! I really appreciate it.

-Kas

Well you can implement your case simply with a relational model as Paul has described to you, or with Inheritance model as follows (provided that a Provider can be either an office or an Agent):

Provider ProviderID INT [PK] FirstName LastName ...

ProviderOffice ProviderOfficeID INT [PK, FK] StreetAddress City ...

ProviderAgent ProviderAgentID INT [PK, FK] Field1 Field2 ...

Then use LLBLGen Pro to create a hierarchy of type TargetPerEntity. Please refer to the LLBLGen Pro documentation "Concepts - Entity inheritance and relational models" "Designer - Inheritance mapping"

KastroNYC wrote:

That's actually almost identical to the setup i've been trying make work my question is this, lets say i have another table(entity) called Posting which has a FK to ProviderID how can I tell what type of Provider this Posting is refering to so if I want to display the provider details when the Posting is clicked on.

I'm not sure the question really got answered and I'm actually heading down this road and have been pondering the exact same thing so I'll try to re-phrase with a little more detail.

Using Otis' example of a library with items that can be derived into Books, Magazines, etc. we have the following table model:

Item ItemID INT [PK] Title Description

Book BookID INT [PK, FK] Author Publisher

Magazine MagazineID INT [PK, FK] MonthPublished SubscriptionEndDate

I understand how I would want to use LLBLGen Pro to create a hierarchy of type TargetPerEntity. However, say I want to query all the items in the library that have 'LLBL' in the Title. My query would obviously be targeted at the Title column in the Item table and it would obviously be pretty easy to get a list of results with ItemID and Title.

The two scenarios that I foresee are: - I want to give each item in the list a different icon based on the type of item it is (book, magazine, etc.) so I would need to know what the item 'type' is. - When the user clicks on the link for a chosen item, we're obviously just passing the ItemID in the querystring but we would also then need to know if we are going to show a book or a magazine.

To solve both problems and Kas' question, would you have a field on the Item table that tells you what 'Type' (book, magazine) the particular item is? Or is there something baked into LLBL that would automagically tell me whether or not the item derives into a book or magazine?

I haven't actually worked with the table inheritance yet in code and am kind of trying to piece things together based on the doco and various threads I've read.

Any clarification is appreciated.

Thanks, Matt

Yes, a field will do the job.

But take care of the following lines that was copied from LLBLgen Pro documentation:

DataAccessAdapter.FetchEntity and hierarchial entities DataAccessAdapter.FetchEntity() is not polymorphic. This is by design as it fetches the entity data into the passed in entity object. As it's already an instance, it would be impossible to change that instance' type to a derived type if the PK values identify an entity which is of a subtype of the type of the passed in entity instance.

In our previous example about BoardMember and CompanyCar, BoardMember is a derived type of Manager which is a derived type of Employee. While this might not be the best OO hierarchy thinkable, it's enough to illustrate the point: if FetchEntity is called by passing in an Employee instance, and the PK identifies a BoardMember, only the Employee's fields are loaded, however if the entity is in a hierarchy of type TargetPerEntity, LLBLGen Pro will perform joins with all subtypes from the supertype, to make sure a type is stored OK.