Hi,

I am using Version 5.6.1 of the designer and SQL Server version 13.0.4001

I am having an issue where I am introducing a new entity to an existing database and the designer is not recognising one of the properties is a foreign key.

The relevant tables are :

CREATE TABLE [dbo].[UserProfile]
(
    [Id] INT NOT NULL IDENTITY(1000, 1),
    AzureId UNIQUEIDENTIFIER NOT NULL,  
    RoleId INT,
    JobTitleId INT, 
    EmployeeNumber nvarchar(6),
    [MobileNumber] NVARCHAR(20) NULL, 
    FirstName NVARCHAR(30) NULL,
    LastName NVARCHAR(40) NULL,
    [Email] NVARCHAR(100) NULL, 
    [ModifiedBy] NVARCHAR(200) NULL,
    CONSTRAINT [UQ_UserProfile_AzureId] UNIQUE (AzureId),   
    CONSTRAINT [FK_UserProfile_To_Role] FOREIGN KEY (RoleId) REFERENCES [Role](Id),
    CONSTRAINT [FK_UserProfile_To_JobTitle] FOREIGN KEY (JobTitleId) REFERENCES [JobTitle](Id), 
    CONSTRAINT [PK_UserProfile] PRIMARY KEY ([Id]),
)

CREATE TABLE [dbo].[UserProfileBusinessUnit]
(
    [Id] INT NOT NULL IDENTITY(1000, 1),
    [UserProfileId] INT NOT NULL,
    [BusinessUnitId] INT NOT NULL,
    [ModifiedBy] NVARCHAR(200) NULL,    
    CONSTRAINT [PK_UserProfileBusinessUnit] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_UserProfileBusinessUnit_To_UserProfile] FOREIGN KEY (UserProfileId) REFERENCES [UserProfile](Id),
    CONSTRAINT [FK_UserProfileBusinessUnit_To_BusinessUnit] FOREIGN KEY ([BusinessUnitId]) REFERENCES[BusinessUnit]([Id]),
    CONSTRAINT [UQ_UserProfileId_BusinessUnitId] UNIQUE ([UserProfileId], [BusinessUnitId]) 
)

CREATE TABLE [dbo].[BusinessUnit]
(
    [Id] INT NOT NULL  IDENTITY(1000, 1), 
    [Name] NVARCHAR(80) NOT NULL,
    [Kco] INT NOT NULL,
    [Address] NVARCHAR(80) NULL,
    [Address2] NVARCHAR(80) NULL,
    [Address3] NVARCHAR(80) NULL,
    [Address4] NVARCHAR(80) NULL,
    [PostCode] NVARCHAR(9) NULL,
    [Telephone] NVARCHAR(30) NULL,
    [Fax] NVARCHAR(30) NULL,
    [DateModified] DATETIME NOT NULL,
    [IsDropped] BIT NOT NULL CONSTRAINT [DF_BusinessUnit_IsDropped] DEFAULT 0,  
    CONSTRAINT [UQ_BusinessUnit_Kco] UNIQUE ([Kco]), 
    CONSTRAINT [PK_BusinessUnit] PRIMARY KEY ([Id]) 
)

CREATE TABLE [dbo].[BusinessUnitExtended]
(
    [Id] INT NOT NULL ,
    [RegionId] INT NULL,
    [Email] NVARCHAR(80) NULL,

    [ModifiedBy] NVARCHAR(200) NULL,
    [IsPlotPublishingEnabled] BIT CONSTRAINT [DF_BusinessUnitExtended_IsPlotPublishingEnabled] DEFAULT 0 NOT NULL,
    [PublishEnabledDate] DATETIME NULL, 
    [PublishEnabledByUserId] INT NULL,   
    CONSTRAINT [FK_BusinessUnitExtended_To_Region] FOREIGN KEY ([RegionId]) REFERENCES Region(Id),
    CONSTRAINT [FK_BusinessUnitExtended_To_BusinessUnit] FOREIGN KEY ([Id]) REFERENCES BusinessUnit(Id), 
    CONSTRAINT [PK_BusinessUnitExtended] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_BusinessUnit_UserProfile] FOREIGN KEY ([PublishEnabledByUserId]) REFERENCES [dbo].[UserProfile] ([Id])
)

Previously a user would belong to exactly one BusinessUnit but now can belong to many so I am adding a linking table UserProfileBusinessUnit to allow this. BusinessUnitExtended inherits from BusinessUnit of type TargetPerEntityHierarchy.

When syncing the relational model data, the catalog explorer correctly shows UserProfileBusinessUnit has two foreign key constraints.

FK_UserProfileBusinessUnit_To_UserProfile   FK_UserProfileBusinessUnit_To_BusinessUnit

However the entity created from this has no relationship to BusinessUnit but one is created to UserProfile.

I am doing something wrong?

When creating a DB with the tables you specified (and removing the FKs to tables not given and shuffling the order a bit), I see there are 2 FKs in the catalog explorer and creating all entities from this creates the proper relationships too.

My test is obviously flawed as yours migrates an existing model and mine just creates what's there.

Do you have the 3 tables that form the initial situation before the import? (or just 2: businessunit and userprofile). I can then use the tables given above as the new situation and see if I can reproduce it.

For testing, removing UserProfileBusinessUnit as an entity and recreating it by reverse engineering should likely fix it on your end, but for completeness if there's a bug we of course have to fix it.

Hi,

These are the tables before a UserProfile could have more than one BusinessUnit

CREATE TABLE [dbo].[BusinessUnit]
(
    [Id] INT NOT NULL  IDENTITY(1000, 1), 
    [Name] NVARCHAR(80) NOT NULL,
    [Kco] INT NOT NULL,
    [Address] NVARCHAR(80) NULL,
    [Address2] NVARCHAR(80) NULL,
    [Address3] NVARCHAR(80) NULL,
    [Address4] NVARCHAR(80) NULL,
    [PostCode] NVARCHAR(9) NULL,
    [Telephone] NVARCHAR(30) NULL,
    [Fax] NVARCHAR(30) NULL,
    [DateModified] DATETIME NOT NULL,
    [IsDropped] BIT NOT NULL CONSTRAINT [DF_BusinessUnit_IsDropped] DEFAULT 0,  
    CONSTRAINT [UQ_BusinessUnit_Kco] UNIQUE ([Kco]), 
    CONSTRAINT [PK_BusinessUnit] PRIMARY KEY ([Id]) 
)


CREATE TABLE [dbo].[BusinessUnitExtended]
(
    [Id] INT NOT NULL ,
    [RegionId] INT NULL,
    [Email] NVARCHAR(80) NULL,
    [ModifiedBy] NVARCHAR(200) NULL,
    [IsPlotPublishingEnabled] BIT CONSTRAINT [DF_BusinessUnitExtended_IsPlotPublishingEnabled] DEFAULT 0 NOT NULL,
    [PublishEnabledDate] DATETIME NULL, 
    [PublishEnabledByUserId] INT NULL,   
    CONSTRAINT [FK_BusinessUnitExtended_To_Region] FOREIGN KEY ([RegionId]) REFERENCES Region(Id),
    CONSTRAINT [FK_BusinessUnitExtended_To_BusinessUnit] FOREIGN KEY ([Id]) REFERENCES BusinessUnit(Id), 
    CONSTRAINT [PK_BusinessUnitExtended] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_BusinessUnit_UserProfile] FOREIGN KEY ([PublishEnabledByUserId]) REFERENCES [dbo].[UserProfile] ([Id])
)

CREATE TABLE [dbo].[UserProfile]
(
    [Id] INT NOT NULL IDENTITY(1000, 1),
    AzureId UNIQUEIDENTIFIER NOT NULL,  
    RoleId INT,
    JobTitleId INT,
    BusinessUnitId INT,
    EmployeeNumber nvarchar(6),
    [MobileNumber] NVARCHAR(20) NULL, 
    FirstName NVARCHAR(30) NULL,
    LastName NVARCHAR(40) NULL,
    [Email] NVARCHAR(100) NULL,
    [ModifiedBy] NVARCHAR(200) NULL,    
    CONSTRAINT [UQ_UserProfile_AzureId] UNIQUE (AzureId),   
    CONSTRAINT [FK_UserProfile_To_Role] FOREIGN KEY (RoleId) REFERENCES [Role](Id),
    CONSTRAINT [FK_UserProfile_To_BusinessUnit] FOREIGN KEY (BusinessUnitId) REFERENCES [BusinessUnit](Id),
    CONSTRAINT [FK_UserProfile_To_JobTitle] FOREIGN KEY (JobTitleId) REFERENCES [JobTitle](Id), 
    CONSTRAINT [PK_UserProfile] PRIMARY KEY ([Id]),
)

Recreating the UserProfileBusinessUnit entity does not fix my issue.

I can't reproduce it.

Steps: 1- I've created a database with the initial 2 tables UserProfile & BusinessUnit. I removed fields referencing other tables.

2- Created an LLBLGen Pro project (v.5.5), LLBLGenPro Framework, using DC First. The 2 tables were ported correctly and mapped to entities with the correct relationship, and the same goes to the generated code.

3- Went back to the database and dropped the 2 tables, and recreated them with the third linking table.

4- Went to the the LLBLGen Pro project, and synched the Relation Model Data with the database, and everything was ported correctly, and reverse engineered to entities as expected with the correct relations.

5- Generated the code, and both relations in the linked table were generated as well.

I think what's going wrong is that you still have to create the entity UnitProfileBusinessUnit (its' a setting in project settings which dictates whether new elements are automatically created from new tables, Project Settings -> Database First Development -> Add new elements after relational model data sync.

As the FKs you refer to are on the UserPRofileBusinessUnit entity, not UserProfile. Right-click 'Entities' in project explorer or the schema in catalog explorer and select Reverse engineer tables to entity definitions. UserProfileBusinessUnit is then automatically selected.

Hi,

I have had another occurrence of the above issue. I was reverse engineering to create the entity definition as you described on 30-Oct. But a relationship is missing on the entity after it has been created. I have found the cause and by posting this update I hope I can help other users.

In this new scenario I have the following new table

CREATE TABLE [dbo].[PendingRequirement]
(
    [Id] INT NOT NULL IDENTITY(1000, 1), 
    [PlotId] INT NOT NULL,  
    [ProductId] INT NOT NULL,
    [DecisionPoint] NVARCHAR(20) NOT NULL,
    [Status] NVARCHAR(10) NOT NULL,
    [PendingDate] DATETIME NOT NULL,        
    CONSTRAINT [PK_PendingRequirements] PRIMARY KEY ([Id]), 
    CONSTRAINT [FK_PendingRequirements_ToProduct] FOREIGN KEY ([ProductId]) REFERENCES [dbo].[Product] ([Id]),
    CONSTRAINT [FK_PendingRequirements_ToPlot] FOREIGN KEY ([PlotId]) REFERENCES [Plot]([Id]),
    CONSTRAINT [UQ_PendingRequirements_Plot_DecisionPoint] UNIQUE ([PlotId],[DecisionPoint])
)

In the above, one PendingRequirement is related to exactly one Plot and one Product. When I reverse engineer the new PendingRequirementEntity has a relation to Product but none for Plot.

It turns out that the PlotEntity definition targets a view and not a table. This detail can be easily missed.

Regards Dominic

Thank you for the feedback. I believe it's better for people to have view names different than table names, maybe by using a prefix or a more descriptive naming, so to avoid any naming confusion.