The Situation In our database, we sometimes store different types of entities in the same table. They are distinguished by a Type field. Some of the fields in the table don't apply to certain types, so we just fill those fields with dummy values like "NOT APPLICABLE".

In our old system that I'm porting to .NET and LLBLGen, the Object/Relational Mapping layer we wrote would map different classes to the same table.

Here is an example to illustrate what we did.

Let's say we have a table called Animal:

Table: Animal
Type    (valid values: Mammal, Bird, Fish)
Height
Weight
NumberOfLegs
WingSpan

We may have several classes that map to that table:

class Mammal
class Bird
class Fish

Question 1: How can I have LLBLGen retrieve objects based on the value of a field? For example, how can I make a MammalEntity so LLBLGen only retrieves records of Type='Mammal' from the Animal table?

In our old system, the class for retrieving objects of type Mammal would generate code like this: SELECT Height, Weight, NumberOfLegs FROM Animal WHERE Type = 'Mammal'

For birds, we would use: SELECT Height, Weight, NumberOfLegs, WingSpan FROM Animal WHERE Type = 'Bird'

For fish, we would use SELECT Height, Weight FROM Animal WHERE Type = 'Fish'

Notice that I also only grab the fields that apply to the type of entity I'm retrieving.

Question 2: When saving an entity, how can I have LLBLGen insert dummy values for fields that aren't applicable to the entity? When saving an object to the database, we would fill the fields that didn't apply to the object with dummy values like zero or 'NOT APPLICABLE'.

For example: If saving a Fish object we would generate code like this: INSERT INTO Animal( Type, Height, Weight, NumberOfLegs, Wingspan) VALUES('Fish', 5, 10, 0, 'NOT APPLICABLE')

Database Redesign Not an Option Now some of you may suggest redesigning the database, but that is not an option for us at this point in time. So that is why I'm just asking how to recreate what we did in our old system with LLBLGen.

Thanks!

clint wrote:

Question 1: How can I have LLBLGen retrieve objects based on the value of a field? For example, how can I make a MammalEntity so LLBLGen only retrieves records of Type='Mammal' from the Animal table?

In our old system, the class for retrieving objects of type Mammal would generate code like this: SELECT Height, Weight, NumberOfLegs FROM Animal WHERE Type = 'Mammal'

For birds, we would use: SELECT Height, Weight, NumberOfLegs, WingSpan FROM Animal WHERE Type = 'Bird'

For fish, we would use SELECT Height, Weight FROM Animal WHERE Type = 'Fish'

Notice that I also only grab the fields that apply to the type of entity I'm retrieving.

This is a typical TargetPerEntityHierarchy scenario where the Animal entity is abstract and you create subEntities based on the Type discriminator field. That way you would have a class per type. You could read more about Entity inheritance.

clint wrote:

Question 2: When saving an entity, how can I have LLBLGen insert dummy values for fields that aren't applicable to the entity? When saving an object to the database, we would fill the fields that didn't apply to the object with dummy values like zero or 'NOT APPLICABLE'.

For example: If saving a Fish object we would generate code like this: INSERT INTO Animal( Type, Height, Weight, NumberOfLegs, Wingspan) VALUES('Fish', 5, 10, 0, 'NOT APPLICABLE')

You can override the OnInitialize method at a partial class of the involved entity and set your default values there. Example: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15323&StartAtMessage=0&#85350

Thanks daelmo.

It looks like the TargetPerEntityHierarchy will work for some of my tables. However, for others, the "discriminator" field is a boolean (bit) field so it won't work unless I change the database, which means I'll have to change the existing programs that are using the database, which we'd rather not do. That's something we'll have to think about.

As for filling in the fields that are usen't used with dummy or "non applicable" values, I had originally thought of overriding EntityBase2.OnBeforeEntitySave().

clint wrote:

It looks like the TargetPerEntityHierarchy will work for some of my tables. However, for others, the "discriminator" field is a boolean (bit) field so it won't work unless I change the database, which means I'll have to change the existing programs that are using the database, which we'd rather not do. That's something we'll have to think about.

Yep, I mean for the scenario you posted above TargetPerEntityHierarchy is ideal. For other tables it may not be suitable. If you want two different entities mapped on the same table without inheritance you also could add the table twice to the entity definition with different names.

clint wrote:

As for filling in the fields that are usen't used with dummy or "non applicable" values, I had originally thought of overriding EntityBase2.OnBeforeEntitySave().

That would work as well.

daelmo wrote:

If you want two different entities mapped on the same table without inheritance you also could add the table twice to the entity definition with different names.

OK, I'll try that idea next.

Thanks!

clint wrote:

daelmo wrote:

If you want two different entities mapped on the same table without inheritance you also could add the table twice to the entity definition with different names.

OK, I'll try that idea next.

Thanks!

Actually, I just remembered that I had already tried making two entities off the same table. But I wanted one entity to automatically grab all the records that had one value for a discriminating field and the other entity to automatically grab records that had another value for that field. I didn't know how to do that and that's when I started this thread.

As David said earlier this is a perfect candidate for targetPerEntityHierarchy Inheritance.

In case of a boolean discriminator, you can try to use a TypeConverter, so your model entities have a different Type than bool, an int or a string for instance, then you can try using this field as a discriminator field for the Inheritance Hierarchy.

Walaa wrote:

As David said earlier this is a perfect candidate for targetPerEntityHierarchy Inheritance.

In case of a boolean discriminator, you can try to use a TypeConverter, so your model entities have a different Type than bool, an int or a string for instance, then you can try using this field as a discriminator field for the Inheritance Hierarchy.

Thanks for the tip!