Hi,

I have a ProjectTable. In this projecttable I have a column project type and a column ParentProjectID

I have a Project and SubProject defined in LLBLGen, using the ProjectType as a discriminator value. ParentProjectID is now a property only on SubProject.

So far so good ...

Now I have used the manager templates 2.0. I get a nice function ProjectManager.FetchWithSubProjects(int id).

In the database I have 3 projects. ProjectID 2 of the first, ProjectType 1, the other two have ProjectID 8 and 9, Both have ProjectType 2 (which is discriminator value for SubProject) and a ProjectParentID of 2 (which is related to project id of the same table).

But when I run the code, it generates the following SQL Code:

Generated Sql query: Query: SELECT [FindDb].[find].[Project].[ProjectID] AS [F0], [FindDb].[find].[Project].[Title] AS [F1], [FindDb].[find].[Project].[Description] AS [F2], [FindDb].[find].[Project].[Progress] AS [F3], [FindDb].[find].[Project].[CompletionDate] AS [F4], [FindDb].[find].[Project].[Owner] AS [F5], [FindDb].[find].[Project].[ProjectTypeID] AS [F6], [FindDb].[find].[Project].[StrategicCoreAreaID] AS [F7], [FindDb].[find].[Project].[SubProjectNumber] AS [F8], [FindDb].[find].[Project].[ParentProjectID] AS [F9] FROM [FindDb].[find].[Project] WHERE ( ( ( [FindDb].[find].[Project].[ParentProjectID] = @ParentProjectId1)) AND ( [FindDb].[find].[Project].[ProjectID] = @Id2)) Parameter: @ParentProjectId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2. Parameter: @Id2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.

And this is wrong ... since it says ProjectID = 2 AND ParentProjectID = 2 ...

Have I done something wrong in the designer or is there something wrong in the manager templates, or is it something else? I don't see any JOIN statements here ... do the manager templates not know how to work with Target Per Entity?

Best regards,

• G.I.

Very likely a bug in the manager templates. I'll see what it generates (we didn't write the manager templates ourselves nor have we tested them thouroughly, they're a community effort) and see if that 's correct.

(edit) reproduced. Hmm...

By the way, it is TargetPerEntityHierarchy I am using, NOT TargetPerEntity ...

It doesn't seem to me that it's a bug in de templates, since I also get that same SQL when I run this code:

adapter.FetchEntityCollection(pe.SubProjects, pe.GetRelationInfoSubProjects());

So somewhere else is something wrong ... the JOIN statements don't get generated.

I have a picture from the designer of the project tables attached.

Does this seem right to you? Everything is in 1 table. I am still trying to figure out what works best ... this or using multiple tables for project types ...

I have the same entity setup as you described and also see the same behavior. Very odd. It's a prefetch path fetch which gives this error. Looking into it.

I get a DIFFERENT query:

SELECT 
    [InheritanceTwo].[dbo].[Project].[Id] AS [F0], 
    [InheritanceTwo].[dbo].[Project].[Title] AS [F1], 
    [InheritanceTwo].[dbo].[Project].[ProjectType] AS [F2], 
    [InheritanceTwo].[dbo].[Project].[ParentProjectId] AS [F3] 
FROM    [InheritanceTwo].[dbo].[Project]  
WHERE 
( (( [InheritanceTwo].[dbo].[Project].[ParentProjectId] = @ParentProjectId1)) 
AND ( [InheritanceTwo].[dbo].[Project].[ProjectType] = @ProjectType2))

parameters: Parameter: @ParentProjectId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2. Parameter: @ProjectType2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.

Code:

ProjectEntity p = ProjectManager.FetchWithSubProjectCollection(2);

Table SQL:

CREATE TABLE [Project] (
    [Id] [int] NOT NULL ,
    [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ProjectType] [int] NOT NULL ,
    [ParentProjectId] [int] NULL ,
    CONSTRAINT [PK_Project] PRIMARY KEY  CLUSTERED 
    (
        [Id]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_Project_Project] FOREIGN KEY 
    (
        [ParentProjectId]
    ) REFERENCES [Project] (
        [Id]
    )
) ON [PRIMARY]
GO

Created 2 entities: Project (disc: 1) and SubProject. (disc 2) Created 3 instances:

Id        Title                                           ProjectType ParentProjectId 
----------- -------------------------------------------------- ----------- --------------- 
2          ParentProject                                      1        NULL
8          SubProject1                                      2          2
9          SubProject2                                      2          2

(edit) this code also works fine:

ProjectEntity p = new ProjectEntity(2);
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    Debug.Assert(adapter.FetchEntity(p));

    adapter.FetchEntityCollection(p.SubProjectCollection, p.GetRelationInfoSubProjectCollection());

}
Debug.Assert(p.SubProjectCollection.Count == 2);

(latest build of v2.0 runtime libs)

I sent a mail with my llblgen project file to support ... maybe I did setup something wrong in there?

Received it. Will have a look

The error is that you specified in the designer 'Id' as the discriminator field in the Project hierarchy. That's the cause of the error. So, destroy the Project hierarchy and rebuild it. (You can't flip the discriminator column easily on an existing hierarchy, since that has consequences for the whole hierarchy and is very complex to check all scenario's where it can go wrong.)

hmmm ... did i do such a stupid thing? haha ... I'll check tomorrow at work ( I am at home now). Thanks for the support ... it was a frustrating issue ... works great by the way with the logging and tracer .... remembered the "old times" where I used SQL Profiler haha!

EDIT: Ok, little difficult that I had to throw the subtypes away and create them again ... it wasn't just possible to remove the subtype link and create that one new again with the correct discriminator column ... but at least now it's working perfectly again! Thank you very much for the fast and good support!