LLBLGen Pro 5.7.3, using Database First:

I believe this is a both a reverse engineer and designer issue, not related to the target framework.

Scenario: (MSSQL server) With SQL tables configured as below

CREATE TABLE dbo.A (
  Pk1 INT not null primary key
);
CREATE TABLE dbo.C (
 Pk1 INT not null,
 Pk2 INT not null,
 [Data] nvarchar(100) null,
 CONSTRAINT [PK_C] PRIMARY KEY (Pk1,Pk2),
 CONSTRAINT [FK_C_A] FOREIGN KEY (Pk1) REFERENCES dbo.A (Pk1)
);

CREATE TABLE dbo.B (
  Pk1 INT not null,
  Pk2 INT not null,
 Link INT null,
 CONSTRAINT [PK_B] PRIMARY KEY (Pk1,Pk2),
 CONSTRAINT [FK_B_A] FOREIGN KEY (Pk1) REFERENCES dbo.A (Pk1),
 CONSTRAINT [FK_B_Link] FOREIGN KEY (Pk1,Link) REFERENCES dbo.C (Pk1,Pk2)
);

There are two issues around this.

1. When reverse engineering Tables to Entities, the model created sets field B.Link as not optional.

2. If edit B.Link in designer, and set the "Optional" flag (so correct underlying object model is created), then renaming the Navigator associate with B.Link causes the "Optional" flag for B.Link to be cleared.

Inspecting the Model Relational data (created by database first process) confirms that B.Link field has correctly has the "Is Nullable" value set to True.

If the "Optional" flag for B.Link is not manually corrected before generating code, this bug results in broken code where entities of type B cannot be loaded if any of the rows contain NULL entries for Link column.

I can't reproduce the first issue. I created a new project based on Northwind database, and the Product Entity has IsOptional set to true for the SupplierId and the CategoryId fields. The same for the second issue, renaming the Navigators doesn't rest the IsOptional flag.

Walaa wrote:

I can't reproduce the first issue. I created a new project based on Northwind database, and the Product Entity has IsOptional set to true for the SupplierId and the CategoryId fields. The same for the second issue, renaming the Navigators doesn't rest the IsOptional flag.

If you create a test database - then run the above SQL script against that database, it will demonstrate the problem. It's also a much simpler test case compared to Northwind to focus on the problem. I tested using the script above before posting this.

I suspect that compound foreign key is a key element in the issue where one part is not nullable, and the other part is. In my example, the not nullable part is also part of PK for that table.

Attached is the LLBLGen project file created when doing the test. To demo item 1, simply delete the model data, then re-create re reverse engineering tables to entities. At the end of reverse a warning conveniently highlights the issue:

DZ0009: The mapping of sub-element 'Link' of entity 'B' for the target ... has following issue The IsNullable flag on the target field 'dbo'B' is set to 'True' while the IsOptional flag flag of the mapped field 'Link (FK)' is set to 'False'

Checking the Optional flag for B.Link removes the warning. Point is the reverse engineering got it wrong in the first place, then gets it wrong again when editing the Navigator using B.Link.

We'll look into it

Reasoning about what's going on, I'll be honest, I can only conclude: for me the construction doesn't make much sense: if the PK side is optional (so the FK fields can be nullable), it's weird to make these part of the PK, but alas, it's a given there are databases out there which are, let's say, unconventional.

That said, we should support this scenario and currently don't. E.g. we also support compound FKs with FK fields which are used in multiple FKs (which this model appears to have too btw), it doesn't make sense theoretically but it happens....

Reverse engineering B alone things are ok, but reversing C after that indeed resets the field, it's done in the FK field assignment. It's a bug in our code (or rather: a limitation as the rule is too strict) : it checks if all FK fields are nullable and only then it markes the PK side as optional. As it doesn't mark it as optional, assigning the FK fields enforces all FK fields to be non-optional (as otherwise the PK side can be optional).

We'll look into fixing this restriction today.

The fix is now available: v5.6.6 hotfix and v5.7.3 hotfix builds.