Hi All,

I have an inheritance problem as a result of a (bad!) database schema that I have not control over and was hoping someone may have a suggestion.

I have an Employee and a Manager table where Manager extends Employee. Unfortunately the Manager table has an independent PK with the relationship being a nullable, non-unique foreign key from the Manager to the Employee table. I know, bad, but not of my doing and out of my control to change.

The result of this relationship is that rather than the generated Manager entity being a subtype of Employee the Employee entity has a collection of Managers associated with it.

On the surface this was not the end of the world as I am using LLBLGen entities to load the state of my Domain model (which has the correct inheritance relationship), so I could hide away this issue behind a loader method. The problem that I have is that this solution issue 2 SQL requests to load my Domain model’s Manager class as one call fetches the Employee and the second all the Managers (of which there will always only be one).

I understand that if the schema were correct and the relationship of the entities generated properly this could be done in a single SQL hit but is there any way to achieve this given my situation?

Thanks in advance, tups

I have an Employee and a Manager table where Manager extends Employee. Unfortunately the Manager table has an independent PK with the relationship being a nullable, non-unique foreign key from the Manager to the Employee table. I know, bad, but not of my doing and out of my control to change.

Is there any chance you put a Unique Constraint on the Manager's field that link to Employee? That way you could setup inheritance at LLBLGen Designer.

And the second all the Managers (of which there will always only be one).

Please post the .Net code that you use to such fetch. Also post the Generated SQL for that fetches.

And last but not least, LLBLGen build and runtime library version? (http://llblgen.com/TinyForum/Messages.aspx?ThreadID=7718)

Is there any chance you put a Unique Constraint on the Manager's field that link to Employee? That way you could setup inheritance at LLBLGen Designer.

Thanks for the suggestion.

Once I have applied this constraint is there something other than click on the 'Construct target-per-entity Hierarchies' that I need to do to set-up the inheritence as clicking this made no difference? At the moment the change has just adjusted the relationship from 1:m to 1:1 which still leave me with the double SQL hit.

Please post the .Net code that you use to such fetch. Also post the Generated SQL for that fetches

            IPredicateExpression idFilter = new PredicateExpression(EmployeeFields.EmployeeId == 1);

            IPrefetchPath prefetch = new PrefetchPath((int)EntityType.EmployeeEntity);
            prefetch.Add(EmployeeEntity.PrefetchPathManager);

            EmployeeCollection entities = new EmployeeCollection();
            entities.GetMulti(idFilter, prefetch);

SQL Hit 1:

exec sp_executesql N'SELECT [Onside_StructureOnly].[dbo].[Employee].[EmployeeID] AS [EmployeeId], [Onside_StructureOnly].[dbo].[Employee].[LabelName], [Onside_StructureOnly].[dbo].[Employee].[Title], [Onside_StructureOnly].[dbo].[Employee].[Firstname], [Onside_StructureOnly].[dbo].[Employee].[LastName], 
[Onside_StructureOnly].[dbo].[Employee].[KnownAs], [Onside_StructureOnly].[dbo].[Employee].[OtherName], [Onside_StructureOnly].[dbo].[Employee].[PreviousSurname], 
[Onside_StructureOnly].[dbo].[Employee].[Salutation], [Onside_StructureOnly].[dbo].[Employee].[EmployeeFullName], [Onside_StructureOnly].[dbo].[Employee].[Suffix], 
FROM [Onside_StructureOnly].[dbo].[Employee]  WHERE ( ( [Onside_StructureOnly].[dbo].[Employee].[EmployeeID] = @EmployeeId1))',N'@EmployeeId1 int',@EmployeeId1=1

SQL Hit 2:

exec sp_executesql N'SELECT [Onside_StructureOnly].[dbo].[Manager].[ManagerID] AS [ManagerId], [Onside_StructureOnly].[dbo].[Manager].[EmployeeID] AS [EmployeeId], [Onside_StructureOnly].[dbo].[Manager].[DateCreated], [Onside_StructureOnly].[dbo].[Manager].[CreatorID] AS [CreatorId], [Onside_StructureOnly].[dbo].[Manager].[DateUpdated], [Onside_StructureOnly].[dbo].[Manager].[UpdatorID] AS [UpdatorId], [Onside_StructureOnly].[dbo].[Manager].[Notes] FROM [Onside_StructureOnly].[dbo].[Manager]  WHERE ( ( ( ( [Onside_StructureOnly].[dbo].[Manager].[EmployeeID] = @EmployeeId1))))',N'@EmployeeId1 int',@EmployeeId1=1

And last but not least, LLBLGen build and runtime library version?

LLBLGen Build: 2.6 Final Runtime library version: 2.6.8.911

1- First of all, yo are using old runtime libs, please use the latest.

2- I've seen it before in some companies where one employee who plays different roles within the organization, he then might have more than one manageer at a time. Also it might be that an Emplyee gets a different manager per project. I'm saying this just to make sure these were not the cases that drove this design.

3-You can fix the schema by faking it. Create a dummy schema (a copy of the original one), remove the PK from the Manager table. And lte the EmployeeID FK be the PK of the manager table. Create the LLBLGen Pro project based on this schema, then re-generate the code and use it to target the original schema.

This way you can define the inheritance hierarchy in the designer, and the code will follow, and the original schema won't have anything to prevent this from working.