I have an entity hierarchy that looks like this:

Person -> Correcter -> Preparer -> Proofreader

The child tables each have a field 'IsActive'. I want to call UpdateMulti() on one of these tables and set IsActive = false - however there is an ORMQueryExecutionException and when I examine QueryExecuted I see the following:

Query: UPDATE [MyProject].[dbo].[Person] SET [IsActive]=@IsActive FROM ( [MyProject].[dbo].[Person]  INNER JOIN [MyProject].[dbo].[Proofreader]  ON  [MyProject].[dbo].[Person].[ID]=[MyProject].[dbo].[Proofreader].[PersonID])
Parameter: @IsActive : Boolean. Length: 0. Precision: 1. Scale: 0. Direction: Input. Value: False.

I'm not sure why it's looking in 'Person' for 'IsActive' - it's not defined there. Maybe I set up the hierarchy wrong? It is my first try with a hierarchy... A hint would be greatly appreciated - thanks

How did you setup your hierarchy. I believe that what you have is a target-per-entity hierarchies. Right-Click on the Entities node in the designer and choose to "Construct Target-Per-Entity Hierarchies". See if this will generate the query that you require.

bclubb wrote:

Right-Click on the Entities node in the designer and choose to "Construct Target-Per-Entity Hierarchies".

Yes, that was how I did it. If it helps, here is the graph of entities from the Designer:

and here are the same tables in Enterprise Manager: (ignore the 'Code#' fields in Person - they are temporary)

Anyway, I tried starting over by deleting those 4 tables, adding them back, and re-creating the hierarchy - but with the same result. When I call UpdateMulti() on ProofreaderCollection, it tries to find an 'IsActive' field on Person.

I also wondered if there might be a problem with the PK and FK's having different names for some reason, so I renamed the FK's from 'PersonID' to 'ID' and regenerated everything but still the same result.

I'm out of ideas

Quick question. Why are you using inheritance in the database for this example? Usually this would be done because the different entities would represent different data or columns. I'll keep looking for a solution, but I just wanted to check what you are trying to do to see if it may have an impact on the solution. Thanks for the screenshots, those always help so much.

Could you please post your code which updates the entity?

For the graph: I'd move IsActive to the supertype, as it's a field contained in all subtypes.

Otis wrote:

Could you please post your code which updates the entity?

For the graph: I'd move IsActive to the supertype, as it's a field contained in all subtypes.

The idea is that there are 3 kinds of 'Person'. A 'Person' can be a member of one or more of the 3 types, and can be "active" or "not active" in each role. Does that make sense?

try
{
    ProofreaderEntity entity = new ProofreaderEntity();
    entity.IsActive = false;
    ProofreaderCollection collection = new ProofreaderCollection();
    collection.UpdateMulti(entity, null);
}
catch (ORMQueryExecutionException e1)
{
    Trace.Warn("QueryExecuted", e1.QueryExecuted);
}

I could go with my original solution which was one table with a field for each role, but I wanted to try to wrap my head around inheritance - maybe it's the wrong approach

I think inheritence is a good idea in you case, but you went for the wrong type of inheritence.

Maybe you shoud have a look at the section "Concepts - Entity inheritance and relational models" in the manual.

You'll see that LLBLGen allows to implement two type of inheritence.

The one you tried, which is called TargetPerEntity, implies that your child entities hold specific data. It involves using a common table with the shared columns and specific tables for each one of the child entities, with only the specific columns for each entity and a primary key made of the foreign key to the master table.

In you case, you don't want to discriminate the entities by the type of data they hold, but simply make it an arbitrary hierarchy. Using a dedicated column in a single shared table seems to be the recommendated way, and LLBLGen allows you to still make use of hard coded different classes. That second mechanism is called TargetPerEntityHierarchy. You can define it in the designer by opening the contextual menu on the master entity and click "Create Sub type for that Entity"

It will the ask for the column to discriminate the child entities, and generate the code you wish.

So you might go with a single table Person with a column PersonType which you use to discriminate the child entities. Once you're done with the designer, you should be able to use "ProofreaderEntity" and the other ones just as you did with your previous code.

Jessynoo wrote:

I think inheritence is a good idea in you case, but you went for the wrong type of inheritence.

Thanks for the ideas! I'll look into it shortly - swamped now.

Jessynoo wrote:

So you might go with a single table Person with a column PersonType which you use to discriminate the child entities.

So if a person has more than one role, they will appear in the Person table more than once? That seems counter-intuitive. There is only one of each person!

Jessynoo wrote:

The one you tried, which is called TargetPerEntity, implies that your child entities hold specific data.

The way I understand it, my child entities DO hold specific data. They just happen to have the same name (IsActive).

Well, I need to proceed with the project... so I will probably just add fields to my Person table like 'IsProofreader', 'IsCorrecter' and such. I can attempt inheritance another day...

I made the same mistake about inheritance when I first started using it. You aren't defining multiple types that a person could be. Inheritance is really used to keep information that only a specific type of person would have.

So lets say you have a company that has a database about vehicles. So their base table is vehicle. It contains vehicleId, engine type, steering wheel type. Now they need to store information about similar types of vehicles. So they have the table for cars. It contains VehicleId, which is used to maintain information about engine and steeringwheel. Then it also has, trunk type. This is information that is specific only to cars. Now they could have many other tables, Truck for instance which could keep information on how big the bed or storage area is, but that doesn't make sense to store for other types of vehicles. This helps the database not need to store null values and makes your code easier to deal with at design time using LLBLGen.

Rhywun wrote:

Jessynoo wrote:

So you might go with a single table Person with a column PersonType which you use to discriminate the child entities.

So if a person has more than one role, they will appear in the Person table more than once? That seems counter-intuitive. There is only one of each person!

Jessynoo wrote:

The one you tried, which is called TargetPerEntity, implies that your child entities hold specific data.

The way I understand it, my child entities DO hold specific data. They just happen to have the same name (IsActive).

Well, I need to proceed with the project... so I will probably just add fields to my Person table like 'IsProofreader', 'IsCorrecter' and such. I can attempt inheritance another day...

Unless there is specific data (IsActive doesn't count) to be stored for each role then there is no need for inheritance at all.

Have a Person table, a Role table and a PersonRole table. For each role a person has, they get an entry in the PersonRole table. If its not there then they don't have that role. More roles can be added later if required. You can also store non-specific data such as StartDate in the PersonRole table. Your Person entity would have a Roles collection property and you can add IsProofreader and IsCorrecter properties etc to your PersonEntity class.

Cheers Simon

simmotech wrote:

Have a Person table, a Role table and a PersonRole table.

Yeah, that makes sense