composite id as part of an other composide id

BIZ TMS Aurelius
1

I have the following classes:
` [Entity]
[Table('apipatprotosubsts')]
[Id('Fpatprotostep', TIdGenerator.None)]
[Id('Fisubid', TIdGenerator.None)]
Tapipatprotosubst = class
private
[Association([TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
[JoinColumn('pid', [TColumnProp.Required], 'pid')]
[JoinColumn('iproid', [TColumnProp.Required], 'iproid')]
[JoinColumn('istepid', [TColumnProp.Required], 'istepid')]
Fpatprotostep: Tapipatprotostep;
[Column('isubid', [TColumnProp.Required])]
Fisubid: Integer;
[Column('protoid', [TColumnProp.Required])]
Fprotoid: Integer;
[Column('substid', [TColumnProp.Required])]
Fsubstid: Integer;
[Column('act', [TColumnProp.Required])]
Fact: Integer;
public
atc,descr,disp: string;
property patprotostep: Tapipatprotostep read fpatprotostep write fpatprotostep;
property protoid: Integer read Fprotoid write Fprotoid;
property isubid: Integer read Fisubid write Fisubid;
property substid: Integer read Fsubstid write Fsubstid;
property act: Integer read Fact write Fact;
end;

[Entity]
[Table('apipatprotosteps')]
[Id('Fpatproto', TIdGenerator.None)]
[Id('Fistepid', TIdGenerator.None)]
Tapipatprotostep = class
private
[Association([TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
[JoinColumn('pid', [TColumnProp.Required], 'pid')]
[JoinColumn('iproid', [TColumnProp.Required], 'iproid')]
fpatproto: Tapipatproto;
[Column('protoid', [TColumnProp.Required])]
Fprotoid: Integer;
[Column('istepid', [TColumnProp.Required])]
Fistepid: Int64;
[Column('step', [TColumnProp.Required])]
Fstep: Integer;
[Column('stepid', [TColumnProp.Required])]
Fstepid: Integer;
[Column('insdat', [TColumnProp.Required])]
Finsdat: TDateTime;
[Column('lastudat', [TColumnProp.Required])]
Flastudat: TDateTime;
[Column('note', [TColumnProp.Required])]
Fnote: string;
[Column('atc5', [TColumnProp.Required], 10)]
Fatc5: string;
[Column('expdat', [TColumnProp.Required])]
Fexpdat: TDateTime;
[Column('canrep', [TColumnProp.Required])]
Fcanrep: Integer;
[Column('active', [TColumnProp.Required])]
Factive: Integer;
[Column('expdays', [TColumnProp.Required])]
Fexpdays: Integer;
[Column('act', [TColumnProp.Required])]
Fact: Integer;
[ManyValuedAssociation([], CascadeTypeAllRemoveOrphan,'Fpatprotostep')]
Fprotosubsts:TObjectList;
public
constructor create;
destructor destroy; override;
property patproto: Tapipatproto read Fpatproto write Fpatproto;
property protoid: Integer read Fprotoid write Fprotoid;
property istepid: Int64 read Fistepid write Fistepid;
property step: Integer read Fstep write Fstep;
property stepid: Integer read Fstepid write Fstepid;
property insdat: TDateTime read Finsdat write Finsdat;
property lastudat: TDateTime read Flastudat write Flastudat;
property note: string read Fnote write Fnote;
property atc5: string read Fatc5 write Fatc5;
property expdat: TDateTime read Fexpdat write Fexpdat;
property canrep: Integer read Fcanrep write Fcanrep;
property active: Integer read Factive write Factive;
property expdays: Integer read Fexpdays write Fexpdays;
property act: Integer read Fact write Fact;
property protosubsts:TObjectList read Fprotosubsts;
end;

[Entity]
[Table('apipatprotos')]
[Id('Fpid', TIdGenerator.None)]
[Id('Fiproid', TIdGenerator.None)]
Tapipatproto = class
private
[Column('pid', [TColumnProp.Required])]
Fpid: Integer;
[Column('iproid', [TColumnProp.Required])]
Fiproid: Int64;
[Column('protoid', [TColumnProp.Required])]
Fprotoid: Integer;
[Column('insdat', [TColumnProp.Required])]
Finsdat: TDateTime;
[Column('lastudat', [TColumnProp.Required])]
Flastudat: TDateTime;
[Column('intol', [TColumnProp.Required])]
Fintol: Integer;
[Column('act', [TColumnProp.Required])]
Fact: Integer;
[ManyValuedAssociation([], CascadeTypeAllRemoveOrphan,'Fpatproto')]
Fprotosteps:TObjectList;
public
constructor create;
destructor destroy; override;
property pid: Integer read Fpid write Fpid;
property iproid: Int64 read Fiproid write Fiproid;
property protoid: Integer read Fprotoid write Fprotoid;
property insdat: TDateTime read Finsdat write Finsdat;
property lastudat: TDateTime read Flastudat write Flastudat;
property intol: Integer read Fintol write Fintol;
property act: Integer read Fact write Fact;
property protosteps:TObjectList read Fprotosteps;
end;
`
It has not much data and I want to load them all:

protos:=manager.find<Tapipatproto>.Where(Linq['pid']=pid).list;

I am getting from line 978 of Aurelius.Mapping.Metadata the following:
raised exception class EidValueConvertFail with message 'Could not check/convert ID for null values'
Class name: Tapipatprotosubst
column name: pid
Attribute name: Fpatprotostep
Database value: ,,1579329,4'
It seems that the values of composite id Fpatprotostep are not used.
How can I overcome this? Thank you in advance

2

Your pid class field is declared as Integer, but the database value contains a different type. Your report says Database value: ,,1579329,4' so I'm not sure what type is this because your message is not properly formatted, probably a string.

Please format your code accordingly, it speeds up the support answer as it's very hard to read your message and the code in it without proper formatting.

3

The table definitions:

CREATE TABLE `apipatprotos` (
	`pid` INT(10) UNSIGNED NOT NULL,
	`iproid` BIGINT(20) UNSIGNED NOT NULL,
	`protoid` SMALLINT(5) UNSIGNED NOT NULL,
	`insdat` DATETIME NOT NULL,
	`lastudat` DATETIME NOT NULL,
	`intol` TINYINT(4) NOT NULL DEFAULT '0',
	`act` TINYINT(4) NOT NULL,
	PRIMARY KEY (`pid`, `iproid`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
CHECKSUM=1;
CREATE TABLE `apipatprotosteps` (
	`pid` INT(10) UNSIGNED NOT NULL,
	`iproid` BIGINT(20) UNSIGNED NOT NULL,
	`protoid` TINYINT(3) UNSIGNED NOT NULL,
	`istepid` BIGINT(20) UNSIGNED NOT NULL,
	`step` TINYINT(3) UNSIGNED NOT NULL,
	`stepid` INT(10) UNSIGNED NOT NULL,
	`insdat` DATETIME NOT NULL,
	`lastudat` DATETIME NOT NULL,
	`note` MEDIUMTEXT NOT NULL COLLATE 'utf8_general_ci',
	`atc5` VARCHAR(10) NOT NULL COLLATE 'utf8_general_ci',
	`expdat` DATETIME NOT NULL,
	`canrep` TINYINT(3) UNSIGNED NOT NULL,
	`active` TINYINT(3) UNSIGNED NOT NULL,
	`expdays` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '180',
	`act` TINYINT(4) NOT NULL,
	PRIMARY KEY (`pid`, `iproid`, `istepid`) USING BTREE,
	CONSTRAINT `FK_apipatprotosteps_apipatprotos` FOREIGN KEY (`pid`, `iproid`) REFERENCES `pchr`.`apipatprotos` (`pid`, `iproid`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
CHECKSUM=1;

CREATE TABLE `apipatprotosubsts` (
	`pid` INT(10) UNSIGNED NOT NULL,
	`iproid` BIGINT(20) UNSIGNED NOT NULL,
	`protoid` TINYINT(3) UNSIGNED NOT NULL,
	`istepid` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
	`isubid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`substid` INT(10) UNSIGNED NOT NULL,
	`act` TINYINT(3) UNSIGNED NOT NULL,
	PRIMARY KEY (`pid`, `iproid`, `istepid`, `isubid`) USING BTREE,
	INDEX `Index 2` (`isubid`) USING BTREE,
	CONSTRAINT `FK_apipatprotosubsts_apipatprotosteps` FOREIGN KEY (`pid`, `iproid`, `istepid`) REFERENCES `pchr`.`apipatprotosteps` (`pid`, `iproid`, `istepid`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
CHECKSUM=1;
4

Ok, but that's not what your error message indicates.

5

εικόνα

6

As you can see from the error message, your database value is coming as ,,1579329,4'. That's not an integer at all.

8

I rewrite the following as I made a mistake in the table name (Tapipatprotostep instead of Tapipatprotosubst):

Tapipatprotostep.Fistepid (1579329) and Tapipatprotosubst.Fisubid(4) are the last 2 IDs in the composite key of the Tapipatprotosubst's record. But the composite key Tapipatprotostep.Fpatproto is not "transfered" to class Tapipatprotosubst to load the first record. An we have 2 null values for database keys pis, iproid.

The first record from Tapipatprotosubst has the composite key 26,1477578,1579329,4
They are 4 integers: PRIMARY KEY (pid, iproid, istepid, isubid) USING BTREE

I also notice that generics in TobjectLists are lost .... through this forum
So,
the entity Tapipatproto has an Fprotosteps:TObjectList<Tapipatprotostep>; and a composite key:
[Id('Fpid', TIdGenerator.None)] ->Integer
[Id('Fiproid', TIdGenerator.None)] ->Int64

Each entity Tapipatprotostep has an Fprotosubsts:TObjectList<Tapipatprotosubst>; and a composite key:
[Id('Fpatproto', TIdGenerator.None)] -> Tapipatproto
[Id('Fistepid', TIdGenerator.None)] -> Int64

Finally the entity Tapipatprotosubst has a composite key :
[Id('Fpatprotostep', TIdGenerator.None)] -> Tapipatprotostep
[Id('Fisubid', TIdGenerator.None)] -> Integer

When Aurelus tries to load the first record in table apipatprotosubsts the Fpatprotostep in the composite key does not have the info for the pid/iproid probably because no records from table
apipatprotosteps have been load yet... or due to other reason?
Is it a bug? Can I recreate in an other way the entities for these tables? How to overcome it? thank you in advance @wlandgraf

9

That's the reason then. The first two commas are separating the null values read from the database.
I'm not sure I fully understand your last message.

If you have a composite key, all values must not be null.

If the composite key is made of associations, then the associated, referenced column must exist in the target table. If the composite key is an association which has an key/id which also contains an association, then the field value must also exist in the referenced table, and so on.

10

Data modeler gives me the same source for the Entities, except the use of TObjectList (it gives Tlists).
Is it a bug of Data modeler or a bug of Aurelius then?

11

Thanks a lot for the new version of Aurelius. Problem exists also with the new version.

12

It doesn't seem to have anything wrong with Data Modeler or Aurelius: your database has null values where it should have non-null values. An id should not have null values.

13

As you can see from table definitions, NULL values are not allowed for the keys elements
I check the tables data, NO null values

14

Can you please provide a small project reproducing the issue, so we can debug and explain/fix what's wrong?

15

aurelius2.zip (314.3 KB)
Use Randomdata.sql in a mysql database

16

Thank you for the project. It really helps and speed up.
The problem is that you are using your many-valued associations (lists) without a proxy. Always use lazy-loaded many-valued associations, making them eager brings no benefit and cause issues like that.

When everything is eager, the whole object tree is retrieved at once, and Aurelius has a limit on the depth of such tree to prevent circular references and low performance. Thus, some levels of the tree come as nil/null. Using lazy-loading prevents such issue.

17

I did not read this limitation in the manual. "Always" is not there. Also I read the following in composite keys in the manual:

As illustrated in the previous example, you can have association attributes as part of a composite identifier. However, there is one limitation: you can't have lazy-loaded associations as part of the Id. All associations that are part of an Id are loaded in eager mode. In the previous example, although FPatient association was declared with TAssociationProp.Lazy, using a proxy, this settings will be ignored and the TPerson object will be fully loaded when a TAppointment object is loaded from the database.

Can you please explain it a bit more?
The first level is Tapipatproto that has a list of Tapipatprotostep (second level) and each Tapipatprotostep has a list of Tapipatprotosubst (third level).

  • The Tapipatprotostep list of objects inside the Tapipatproto will be loaded in eager mode anyway?
  • On the third level (list of Tapipatprotosubst inside each Tapipatprotostep) it must be Lazy-loaded in order not to happen this problem?

So, Aurelius with composite keys can have one eager and one lazy-loaded level in an entity?

18

It's not a limitation, but a recommendation.
And I'm talking about associations, by many-valued associations (lists). Make them lazy and it will work.

19

I made Lazy-loading only the third level (Tapipatprotosubst Tlist inside Tapipatprotostep) and it worked.

Now I have small problem:
I want the TobjectManager not to own the entities in same cases, I want the Tobject manager to load the entities, then free the Tobject manager and free manually them probably in an other/main thread.

How to request all lazy loading lists to be loaded by a TobjectManager that does not own the entities and then destroy the TobjectManager? Can I use the Tproxy.Load and when?

Can I disconnect the TFireDacConnectionAdapter without destroy the TobjectManager?

20

Unless you have a good, specific reason, add lazy-loading to lists in all levels.

You can set Manager.OwnsObjects := False if you don't want th manager to destroy its own objects. I wouldn't recommend it, though, unless you really want to manage complex object structures.

21

How can I deal with a connection loss to the database having a TobjectManager with Lazy Loading?
I know the autoreconnect option of the Firedac. If database is not available for some minutes?
Anyway, thanks a lot