Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> Entity property should be a foreign key but designer says otherwise
 

Pages: 1
Bugs & Issues
Entity property should be a foreign key but designer says otherwise
Page:1/1 

  Print all messages in this thread  
Poster Message
DominicF
User



Location:

Joined on:
02-Jul-2019 13:07:55
Posted:
7 posts
# Posted on: 25-Oct-2019 13:41:32.  
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 :
Code:

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.
Code:
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?
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37859 posts
# Posted on: 28-Oct-2019 10:13:37.  
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.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
DominicF
User



Location:

Joined on:
02-Jul-2019 13:07:55
Posted:
7 posts
# Posted on: 29-Oct-2019 16:20:46.  
Hi,

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

Code:
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.


  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14584 posts
# Posted on: 29-Oct-2019 21:04:51.  
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.



  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37859 posts
# Posted on: 30-Oct-2019 09:47:35.  
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.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
DominicF
User



Location:

Joined on:
02-Jul-2019 13:07:55
Posted:
7 posts
# Posted on: 19-Dec-2019 16:29:19.  
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

Code:
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


  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14584 posts
# Posted on: 19-Dec-2019 20:01:54.  
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.
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.