Duplicate columns using associations

[Gabriele_Dunkel]

Hi,

I need help understanding the concept of associations. Assume I have the following entity class:

  [Entity]
  [Table('sys_menu_group')]
  [UniqueKey('smg_name')]
  [Id('Fsmg_id', TIdGenerator.Uuid36)]
  Tsys_menu_group = class
  private
    [Column('smg_id', [TColumnProp.Required], 36)]
    Fsmg_id: string;
    
    [Column('smg_basis_group_id', [], 36)]
    Fsmg_basis_group_id: Nullable<string>;

    ...
    
    [Association([TAssociationProp.Lazy], [])]
    [JoinColumn('smg_basis_group_id', [], 'smg_id')]
    Fbasis_group: Proxy<Tsys_menu_group>;

    function Getbasis_group: Tsys_menu_group;
    procedure Setbasis_group(const Value: Tsys_menu_group);
  public
    constructor Create;
    destructor Destroy; override;

    property smg_id: string read Fsmg_id write Fsmg_id;
    property smg_basis_group_id: Nullable<string> read Fsmg_basis_group_id write Fsmg_basis_group_id;

    ...

    property basis_group: Tsys_menu_group read Getbasis_group write Setbasis_group;
  end;

function Tsys_menu_group.Getbasis_group: Tsys_menu_group;
begin
  Result := Fbasis_group.Value;
end;

procedure Tsys_menu_group.Setbasis_group(const Value: Tsys_menu_group);
begin
  Fbasis_group.Value := Value;
end;

I am creating a new object like this:

MenuGroup := Tsys_menu_group.Create;
MenuGroup.smg_basis_group_id := ...
...

I want to access and use the basis_group property right after creating, so I additional assign the corresponding object to the basis_group property. I think that this should automatically be done by the object manager when accessing the field?

Later on when saving the data using SaveOrUpdate() I get an exception with the message "Column 'smg_basis_group_id' specified twice.".

I tried some variations of field annotations but nothing worked. Do I have to remove the field Fsmg_basis_group_id? But how can I use it without using (and loading) the complete basis_group?

Thanks and regards

[wlandgraf (Wagner Landgraf)]

@Gabriele_Dunkel

Do I have to remove the field Fsmg_basis_group_id

Yes. Associations should only be defined using properties of type object.

@Gabriele_Dunkel

But how can I use it without using (and loading) the complete basis_group?

You don't. You should always use the associated object. Here are some topics I gathered for you for better understanding of the mechanism:

Setting Parent ID (foreign key value) directly

Correct, the parent is already in the manager and that's the best way to do, as it won't execute any SQL but retrieve from cache. You can also create the Parent object in advance and add it to the manager before making the Find calls: Parent := TParent.Create; Parent.Id := ParentID; ObjMgr.Update(Parent); But note that you have to be sure that the Parent object will never be flushed, otherwise the record in the database will be updated and the missing properties will be set to null.

TMS Software Blog: Crash Course TMS Aurelius - Associations (Foreign Keys)

[Gabriele_Dunkel]

Hi,

if I omit the Fsomething_id fields and instead declare the associated "Fsomething: Proxy<>" fields, how can I distinguish cases where Fsomething_id is null and Fsomething (with Fsomething_id not null) cannot be found?

Regards

[wlandgraf (Wagner Landgraf)]

If Fsomething is null, that means the underlying foreign key field in the database is null.
There should be no possibility for an invalid value ("not found"), because by default Aurelius creates a foreign key constraint in the field.