Hi.

Lets say I've a 'tbl_category' that has three rows in it. Each row needs to map to a different sub-class of 'Category' because each row requires a different version of a virtual method from 'Category'. There's no need for the sub-types in the database because the sub-types exist purely to override code - there're are no extra properties.

Is LLBLGen capable of mapping the tbl_category rows to a sub-type based on, say, a category's pk?

Cheers, Ian.

Ian wrote:

Is LLBLGen capable of mapping the tbl_category rows to a sub-type based on, say, a category's pk?

Yes. You should use Hierarchies of type TargetPerEntityHierarchy

Woah, that's awsome.

So if you want an abstract base class somewhere in your hierarchy, are you supposed to map to it a value that simply never appears such as '-1' where the real discriminator field values, say, are all greater than one?

What if you want to map multiple values to the same type? Do you think this implies a bad design?

So if you want an abstract base class somewhere in your hierarchy, are you supposed to map to it a value that simply never appears such as '-1' where the real discriminator field values, say, are all greater than one?

After creating the hierarchy you can right click on the Root Entity and select "Is Abstract" from the context menu.

What if you want to map multiple values to the same type? Do you think this implies a bad design?

I'm afraid so.

After creating the hierarchy you can right click on the Root Entity and select "Is Abstract" from the context menu.

Yes but when you create the class that's going to be abstract, you need to choose a discriminator value to map to it. But because its abstract, this mapping is then invalid because LLBLGen will then possibly be trying to create an instance of the abstract class when it finds a row with the above mentioned discriminator value.

I'm afraid so.

Can you explain why?

Can you explain why?

Mainly because the descriminator field should have values which dscribe and define the type of the tuple/row.

For example, a Car table can have a column called type which defines if an instance/row is an SUV, Sports, Truck...etc.

And for each of these values you can have a corresponding .NET type/class which inherits from the main type (car).

But if you want to treat the SUV and the Sports car as one .NET type, then maybe the value should have been something to better explain the type (eg. "SUV/Sports").

And if you want to specifically identify which is which, then maybe you need another descriminator column to further sub-type them.

Am I making any sense?

Yes it makes sense but I'm can't see any fundamental reason why it shouldn't be possible to map more than one value to the same type.

Going back a bit, don't you think it would be better if instead of selecting a class as 'abstract', you just didn't give it a value in the list of descriminator values? That would avoid having to use dummy values.

entity mapping isn't about classes mapping, it's about entities. You confuse the two.

You have an entity definition, which is abstract. It is projected on both a relational schema (which results in the physical definition of a table) and code (which results in a physical definition of a class).

You then have entity instances, which are the data. As class and table are projection results of the same thing, namely the abstract entity definition, data in the table and class means the same thing: an entity instance of the entity definition represented by class or table.

So if you have 3 rows in a category table, these rows mean something. They're instances of a given entity definition. If you want to store them in 3 different entity class instances (== containers, not the entity themselves), you can only do that if the rows in the table are instances of 3 different entity definitions, not 1.

You want to have three different pieces of code, however they have to contain the same entity definition instances? That doesn't make sense: there's no theoretical basis for the requirement of having these different classes.

You want to have a method which behaves differently based on the entity instance inside the instance of the entity class. that's not done through inheritance but simply by logic inside the method, OR you have to go for the fact that the entity instances inside the table are indeed of different types and THUS will have to be placed inside entity class instances of these types (as only those types give meaning to the data!)

This might sound complex but it's the only way to make [1, "Foo Inc.", "BlablaStreet", 13, "London"] become a customer with ID 1 instead of staying a bucket of random data.

Consider it this way: as an o/r mapper core you get the following row [1, "Foo"] and the row [2, "Bar"]. Which is of type "CategoryA" and which is of type "CategoryB" ? Unclear. So either the type has to be determinable from the data, or the type is the same as the table they're in, which is the entity definition which resulted in the table definition! Because otherwise [1, "Foo"] would simply mean an integer and a string and not 'a category'.

So what is the point of a hierarchy of type TargetPerEntity?

You want to have a method which behaves differently based on the entity instance inside the instance of the entity class.

I'd have thought a hierarchy of type TargetPerEntity would be just what's required here.

An entity instance is stored in multiple tables in that case (across all its supertypes + its own type, which is exactly how you'd expect. )

Ahhh, I mean TargetPerEntityHierarchy.

(You know there's not much difference between 'hierarchies of type TargetPerEntity' and 'hierarchies of type TargetPerEntityHierarchy'. Why not 'hierarchies of type TargetPerEntityInstance' for the latter?)

So again, what is the point of a hierarchy of type TargetPerEntityHierarchy? And...

You want to have a method which behaves differently based on the entity instance inside the instance of the entity class.

I'd have thought a hierarchy of type TargetPerEntityHierarchy would be just what's required here.

Well look, I'm going to have another go at asking my first question. I'm actually a little thrown by you going to so much trouble to convince me that I don't know the difference between a class and an object. (or so it seems.)

So...

Lets say I have a table 'tbl_vehicle_type' and a table 'tbl_flying_vehicle'. They look like this...

tbl_vehicle_type

VehicleTypeID Name

tbl_flying_vehicle

VehicleTypeID FirstFlightDate

There's a 1-1 relationship between the two 'VehicleTypeID' columns so LLBLGen allows me to create a hierarchy of type TargetPerEntity and so I get a 'VehicleType' class and a 'FlyingVehicle' class when I generate the code.

Now, I want to add a virtual method to the base class 'VehicleType' called 'Output()' which returns a string. For 'VehicleType', it would be implemented something like...

return string.Format("Type: {0}", Name);

When overridden, 'FlyingVehicle's version would be something like...

return base.Output + string.Format(" Also I can fly.");

So some sample data would be...

tbl_vehicle_type

[1, Car] [2, Airplane] [3, Hot Air Balloon]

tbl_flying_vehicle

[2, 3/6/1903] [3, 7/4/1657]

When I SELECT 'tbl_vehicle_type' I get one instance of 'VehicleType' and two instances of 'FlyingVehicle'.

Now I decide to change my design. I realise that 'FlyingVehicle's don't need to store data about when they first flew so I remove the column 'FirstFlightDate'. There is now no need for 'tbl_flying_vehicle' because a flying vehicle doesn't have any data.

However, I still want 'FlyingVehicle' to behave differently to 'VehicleType' when I call 'Output()' and I still want to use an inheritance hierarchy in order to achieve this.

(Perhaps this is not the most believable example. The principle is sound though - its like I've got a sub-class of an ASP.net webpage which doesn't add any properties, it just overrides 'onLoad'.)

So in LLBLGen I create a hierarchy of type TargetPerEntityHierarchy, set the discriminator field to 'VehicleTypeID' and set the discriminator value of the super-type to '1'.

Then I add a sub-type with entity name 'FlyingVehicle' with a discriminator value of '2'.

This is where the problem begins. I want 'Hot Air Balloon' to be an instance of the 'FlyingVehicle' class too just as it was when the hierarchy was spread across multiple tables. But if I enter 'FlyingVehicle' as a new entity name for a sub-type, it says, 'The name 'FlyingVehicle' specified for the sub-type is invalid. There is already an Entity definition with this name in the project'.

There are two ways around this.

a) Create one entity each for each of the two flying vehicle instances and duplicate the implementation of 'Output()' so that each generated class has its own copy. This is obviously bad.

b) Create an abstract base class which inherits from 'VehicleType', put the implementation of 'Output()' in the abstract class and then have the two flying vehicle types inherit from the abstract base class. Well now you've for three classes instead of one.

So, really what I think is needed, is the ability to have multiple discriminator values map to the same entity!

It's not about classes. They've nothing to do with this. I'm not under the assumption that you don't know what a class is, I am under the assumption that you don't understand the difference between an entity class instance and an entity instance. Which is ok, it's often mixed up, as people talk about 'entities' while they refer to data OR entity class instances. Hence my explanation.

Please answer this question:

Consider it this way: as an o/r mapper core you get the following row [1, "Foo"] and the row [2, "Bar"]. Which is of type "CategoryA" and which is of type "CategoryB" ? Unclear.

If you have multiple discriminator values mean the same entity definition, what's the point of having multiple discriminator values? 1, 2, 3 mean Animal, 4, 5, 6 mean Mammal. Which means 1 means Animal and 2 means Mammal, because the only purpose a discriminator is there is to understand what type, what entity definition the data is an instance of.

So I have a row with discriminator value 1 and I have a row with discriminator value 2. Both are... Animals. So in which container do I have to put them? In the container related to the Animal entity, which is an instance of the AnimalEntity class.

I see what you're getting at, you want to have an entity definition -> C# class mapping as well where discriminator values are used too, so '1' will be used for AnimalEntity and '2' will be used for SpecialAnimalEntity while in the db they're both 'Animal'.

The problem is: the container gives meaning to the data in the OO environment. So although the data inside SpecialAnimalEntity instances are Animal instances, they're not the same as instances inside AnimalEntity instances, as they're in different containers and the containers give different meaining to the data. However in theory they both map to the same abstract entity definition.

That's a difference: they in theory are instances of the same entity definition however due to the chosen container they're not.

This can only be solved by creating different entity definitions, one for Animal and one for SpecialAnimal, which was suggested earlier.

Back to the code stuff. Working with entities sometimes clashes with thinking in code. Some people think that o/r mapping is about mapping pieces of code onto tables. Nothing can be more wrong than that. As you gave an example with two tables, it still suggests that you didn't totally understood what I meant. Those 2 tables have no real meaning unless the entity definitions they're a projection of are given. For example, you have a table 'vehicle_type'. It seems to define the type for a flying_vehicle. But that's not how it works.

LLBLGen Pro allowed you to create a TargetPerEntity inheritance hierarchy because it saw a 1:1 pk-pk relationship between two tables and assumed the FK side is the subtype and the PK side is the supertype. Did you wonder why that is? It's because when you have an inheritance hierarchy in an abstract entity model, you can convert that hierarchy to tables in a couple of ways, one of them is the 'target per entity' approach: the PK (Identifying set of attributes, i.e. pk field(s)) of the entity is always defined by the root of the hierarchy, and per subtype you define a table, where the PK is an FK to the PK of its supertype's target table. So Animal <- Mammal <- Cow where Animal defines the PK, say ID. This results in three tables: Animal (ID, ... ), Mammal (ID (FK to Animal.ID), ...), Cow (ID* (FK to Mammal.ID), ...)

Looking at such a structure, one can conclude two things: 1) these three tables have 1:1 pk-pk relations and aren't in an inheritance hierarchy (e.g. used for optional 1:1 related data for example) 2) these three tables are in an inheritance hierarchy.

To conclude 2) a machine/program has to make an assumption what the entities mean, which it can't do: Customer 1:1 AdditionalCustomerData also have a 1:1 pk-pk relationship but aren't in an inheritance hierarchy, so it requires the user (you) to help it reverse engineer the hierarchies.

The tables you gave as an example, if they are the projection result of entities in an inheritance hierarchy, they have to have 1 core aspect: the PK of the root of the hierarchy must be the PK of the table representing this root type and the direct subtypes must refer to it. Looking at your tables, I can only conclude: that's not the case. flying vehicle isn't a subtype of vehicle type. In fact, the PK of flying vehicle isn't vehicletypeid.

Changing it to a single-table stored hierarchy with TargetPerEntityHierarchy isn't changing it one bit. The only thing that changes is an artificial extra field which describes of which entity definition (!) type the data is an instance of. This thus means that every value in such a table means a different type. Has nothing to do with code, tables etc., but with entity definitions.

Your table setup is therefore wrong: get rid of the vehicletype table for specifying the type, that's not going to work. Please understand that the route to alter tables is: modifying entity definitions -> convert them to tables. It's ok to have an entity definition without extra attributes/fields. In your case, get rid of vehicletype as you're not storing vehicletypes, but vehicles. So you have a Vehicle entity with PK ID. It has subtypes, like Car, Airplane and Hot Air Balloon. As you want everything in 1 table, you define 1 table, Vehicle. You define the PK on that table. Then you define all fields in all entities in that table, making the fields which are only defined in subtypes nullable. Then you add a discriminator column. Then you open llblgen pro and map an entity to Vehicle. This is your root entity.

For every type you define a new discriminator value and a new entity. So you end up with Vehicle, Car, Airplane and HotAirBalloon. it doesn't matter if only vehicle has extra fields, llblgen pro can deal with that. What it does is that it will generate 4 entity classes for you, 3 of them will perhaps not add extra fields but they DO define different meaning! They do define that the data inside the instances of the classes is of a different type.

That's how you should approach this. So please stop thinking in tables and code, and first think in entities, then create tables from them.

I know this can be hard without knowledge from NIAM for example, as working with databases in general results in working with tables and it ends up the same as with working with code: tables and classes are the central point of the universe. But they don't fall out of the sky. There's a reason why AnimalEntity maps to Tbl_Animal and not to Tbl_Mammal: it's because both are projections of the same entity definition and therefore give the SAME meaning to the entity instance inside them. Otherwise the mapping would simply be wrong.

All this theory and my slightly bad example aside, I think the error I've been making is using the PK of a table as a discriminator field instead of using an 'artificial extra field which describes of which entity definition (!) type the data is an instance of.'

If I do do this then of course its trivial to load multiple rows in a table into the same type container which is what I was after all along. I think my error was partially a result of my simplistic reading of the manual where it says,

Discriminator fields shouldn't be foreign key fields.

An 'artificial extra field which describes of which entity definition (!) type the data is an instance of.' is a bit like a foreign key isn't it? Anyway, how about adding this description of the discriminator field to the manual?

By the way, I think it may have been Terry Halpin that I saw on The .Net Show about five years ago.

It was episode #25. Pity its not in the archives any longer.

I remember thinking that it would be marvelous to test my database design skills by telling their ORM tool what I wanted in English and then seeing the database schema that was generated from the description. But back then I think the tool only came with a super expensive version of Visual Studio. I'm kind of hoping that your 'vision' for LLBLGen 3 includes an ORM designer (like NORMA) which will generate the DB and the classes all in one go.

Ian wrote:

All this theory and my slightly bad example aside, I think the error I've been making is using the PK of a table as a discriminator field instead of using an 'artificial extra field which describes of which entity definition (!) type the data is an instance of.'

If I do do this then of course its trivial to load multiple rows in a table into the same type container which is what I was after all along. I think my error was partially a result of my simplistic reading of the manual where it says,

Discriminator fields shouldn't be foreign key fields.

An 'artificial extra field which describes of which entity definition (!) type the data is an instance of.' is a bit like a foreign key isn't it?

What does 'a bit like a foreign key' mean ? A foreign key field is really the PK field of the PK side of a relationship. So if entity A and entity B have a relationship, A 1:n B, and A.ID is the PK of A, then B gets a foreign key field pointing to A.ID.

That has nothing to do with the type of B, but with the relationship with A. That's a different thing. You might want to see A as the type description of B, but that's not correct, as you can change the FK field but you can't change types of instances of B on the fly.

Anyway, how about adding this description of the discriminator field to the manual?

At the moment, LLBLGen Pro expects a model to be present and that the model is a result of a projection of an entity model onto tables, so it can be reverse engineered to that entity model again (what llblgen pro v2.6 does). The caveat here is that the table model thus must be designed in such a way that it represents the entity model and thus that discriminator fields are already present in the tables which contain a whole hierarchy.

So documenting this inside the entity inheritance docs in v2.6 is perhaps useful to check if people did model the tables correctly but it doesn't really help people who don't model the tables but inherit a model from a dba for example. So in the v2.6 docs, I don't think it will help much to document it deeper. What should be added is a section like the sections in Halpin/Nijssen's book how to convert an entity model to tables, but that's a bit much.

Ian wrote:

By the way, I think it may have been Terry Halpin that I saw on The .Net Show about five years ago.

It was episode #25. Pity its not in the archives any longer.

I do remember that show! Those shows were great btw.

I remember thinking that it would be marvelous to test my database design skills by telling their ORM tool what I wanted in English and then seeing the database schema that was generated from the description. But back then I think the tool only came with a super expensive version of Visual Studio. I'm kind of hoping that your 'vision' for LLBLGen 3 includes an ORM designer (like NORMA) which will generate the DB and the classes all in one go.

Yes the ORM/NIAM capable component was in visio and was shipped with vs.net enterprise. It was nice to be able to design pretty pictures, but it was overly clunky to be really useful.

NIAM/ORM is based on stating facts and based on that facts you can build your entity model. So if you define that customer has a relationship with order, you can state facts that show that the relationship is 1:n. Before I started V3, I looked at NORMA and what I should do with v3 to make it offer a feature that users can define an entity model properly. I found it too complex to work with (NORMA), for users who don't have ORM/NIAM knowledge (the vast majority, as ORM/NIAM is not widely known/ used). So I looked at it differently: I liked the concept of defining sentences, like customer has orders, though I don't like the fact stating, as I never liked that really in the NIAM/ORM component in visio: it was too cumbersome and maintaining these facts sucks. V3 also doesn't have to produce check constraints, so a lot of the elements in NIAM are not used.

V3 indeed allows model-first design, be it from scratch or by modifying an existing model. DB Schema changes are exportable and verifyable. It helps you define inheritance hierarchies properly so you work with entity inheritance hierarchies and as you are guided with either using target per entity or target per entity hierarchy you are focussing on specifying the info for either one of the hierarchy types and you thus don't make mistakes.

V3 will also contain on top of the normal interfaces and tools to create entities via menus a component which will allow you to specify entities easily with something I based on how NIAM does it. I won't mention details about it but it's designed to be useful during interviewing with a domain expert, so the domain expert states facts, you write them down, the model arises. But more on that later.

One thing that I think would be especially cool would be the ability to define simple business logic in the model which would result in a SQL version _and _a C# version of the logic being generated.

Then you'd have the option of running the business logic at the db _or _in the CLR but you'd still only need to maintain the logic in one place.

People often seem to say - keep your business logic out of the database . But the db is often perfectly capable of executing logic and sometimes its just so much easier to run a query at the database than have it spilling out into one's app.

Am I making any sense here?

What kind of business logic are you referring to exactly? Because you are in control where what happens. BL in the database could mean inserts/updates based on filters, we already cover updates based on filters, though not inserts, however, working with entities implies that you work with entity instances (== data) and therefore the BL also has to work with and act on entity instances, thus it doesn't matter where that logic is stored.

Well I'll give you the example that's lead me to ask this question...

I'm selling chalets at a holiday camp for a festival. First the user makes a payment towards a chalet which results in an account being created and the payment being assigned to the account. Then the user creates several 'Guest' instances which are also assigned to the account. (Of course, these are the people that are actually going to stay in the chalet.)

Now, when it comes time to send out an email notification to an account, the logic is -

Send it to the first guest (called the group leader) or if there isn't one, send it to the last billing address for the most recent payment assigned to the account.

(There can be multiple payments made on the account.)

When I want to bulk mail lots of accounts, its much easier when selecting multiple accounts to look up the preferred contact at the database using CASE and some sub-queries, but if I'm working with a single account for a web page, then I think its easier to do a couple of prefetches on the account's orders and guests and do the look up in C#.

In the single account scenario for example, if I were to add an extra 'preferred contact' property to the account entity which was populated at the db when the account was first fetched, then if the user were to edit their group leader info, this extra property would then be out of sync with the current guest data.

So I want to state the 'preferred contact logic only once but be able to have it run at the db or in C#. I think this is analogous to having validation rules projected to Javascript which runs at the client. You don't want to define the validation with client technologies but you _do _want it to run there to improve the user experience.

Though I think it's about: I have this logic to apply to a set of entities or a set of data, and therefore I have to take these steps: 1) produce the set 2) apply the logic 3) consume the result of step 2)

Step 1 is a query or set of queries, to obtain the data from the storage the data is located in. You have to run the query or queries, even if the logic is inside the db.

Where Step 2 is located is a debate which is going on for many many years (the pro/con stored proc debate actually covers it). The problem is that if you store it inside the DB, you can't re-use it for in-memory usage and vice versa. So you've to choose. And what you do depends on step 3. If the 3 steps produce new data, it might be that it's more efficient to do this inside a stored procedure, when the data set produced by 2 or consumed by 2 is very very big for example: keeping the data inside the db is then more efficient.

In general, it's easier to write all logic in .NET code, as the rest of the code is there too, so the steps in general are: 1) write a query or set of queries to produce a graph of entities or typedlist/dynamic list/projection list 2) traverse fetched data and apply logic, produce a changed set 3) save the changed set or otherwise consume the results

Your example about the bulk email, your step 1 set of queries have to produce a list which you can consume. It might be that you fetch perhaps a little bit more data but as you're not saving entities, you could produce a more clever dynamic list or typedlist for example just for the production of emails.

Should this be in the DB? that's up to you. Though I don't see how writing C# code could be converted into SQL imperative logic so it's converted to a proc or imperative SQL so it's ran inside the DB, with the flip of a switch.

The problem is that if you store it inside the DB, you can't re-use it for in-memory usage and vice versa. So you've to choose.

At the moment yes.

Though I don't see how writing C# code could be converted into SQL imperative logic so it's converted to a proc or imperative SQL so it's ran inside the DB, with the flip of a switch.

Yes that is a bit science fiction-y. But I wouldn't be surprised if that's where things end up. Oslo and your own tool will both allow one to project both a C# and a SQL version of a model. How long until behaviour is added to the model?

Behavior is better expressed in a language designed for specifying behavior, i.e. C# or VB.NET. SQL isn't meant to be a language to define behavior in, it's an interpreted language after all and set oriented.

Oslo also won't change this, as Oslo is a souped up config file specification engine, as far as I can see. IMHO, it's easier to simply define the logic in the steps I mentioned above.