Many Valued Associations on Unique Key

Wagner,


I a FiscalYear table which has an ID (Integer) primary key column and a unique key with a composite key (StartDate, EndDate).  I also have a related table called FiscalYearClosure which references (StartDate, EndDate).  I created the relationship in this manner to enforce that FiscalYearClosure.Date is between FiscalYear.StartDate and FiscalYear.EndDate in the database.

I have not been able correctly set a ManyValueAssociation in the TFiscalYear class.  Are ForeignJoin argument required to reference the column(s) of a primary key only or can they reference a unique key also?

Here is how I have coded the relationship

  [Entity]
  [Table('T_FISCAL_YEAR')]
  [ID('ID', TIdGenerator.IdentityOrSequence)]
  [UniqueKey('START_DATE, END_DATE')]
  [UniqueKey('FIRST_DAY_OF_SCHOOL, LAST_DAY_OF_SCHOOL')]
  TFiscalYear = class(TCustomFiscalYear)
  private
    [ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAll - [TCascadeType.Remove])]
    [ForeignJoinColumn('FISCAL_YEAR_START_DATE', [TColumnProp.Required], 'START_DATE')]
    [ForeignJoinColumn('FISCAL_YEAR_END_DATE',   [TColumnProp.Required], 'END_DATE')]
    FClosureList: Proxy<TList<TFiscalYearClosure>>;
 end;

  [Entity]
  [Table('T_FISCAL_YEAR_CLOSURE')]
  [ID('ID', TIdGenerator.IdentityOrSequence)]
  TFiscalYearClosure = class(TCustomSchoolClosure)
  private
    [Association([TAssociationProp.Lazy], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('FISCAL_YEAR_START_DATE', [TColumnProp.Required], 'START_DATE')]
    [JoinColumn('FISCAL_YEAR_END_DATE', [TColumnProp.Required], 'END_DATE')]
    FFiscalYear: Proxy<TFiscalYear>;
 end;

You can have relationships to fields that are not part of primary key, like you did. That's fine.

The issue with your mapping is that you the way you did it Aurelius can't tell TFiscalYear.FClosureList relates to FFiscalYearClouse.FFiscalYear, it will think they are two different associations and will create the fields twice. Map it this way to tell Aurelius FClosureList relates to FFiscalYear:




[Entity]
  [Table('T_FISCAL_YEAR')]
  [ID('ID', TIdGenerator.IdentityOrSequence)]
  [UniqueKey('START_DATE, END_DATE')]
  [UniqueKey('FIRST_DAY_OF_SCHOOL, LAST_DAY_OF_SCHOOL')]
  TFiscalYear = class(TCustomFiscalYear)
  private

    [ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAll - [TCascadeType.Remove], 'FFiscalYear')]
    FClosureList: Proxy<TList<TFiscalYearClosure>>;
 end;



  [Entity]
  [Table('T_FISCAL_YEAR_CLOSURE')]
  [ID('ID', TIdGenerator.IdentityOrSequence)]
  TFiscalYearClosure = class(TCustomSchoolClosure)
  private
    [Association([TAssociationProp.Lazy], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('FISCAL_YEAR_START_DATE', [TColumnProp.Required], 'START_DATE')]
    [JoinColumn('FISCAL_YEAR_END_DATE', [TColumnProp.Required], 'END_DATE')]
    FFiscalYear: Proxy<TFiscalYear>;
 end;

This worked!  Thanks!

A further question on this.  Will mapping for ManyValuedAssociations work when I am using inheritance and the mapping field is in the parent class?  For example


  [Entity]
  [Table('T_FISCAL_YEAR')]
  [ID('FID', TIdGenerator.IdentityOrSequence)]
  [UniqueKey('START_DATE, END_DATE')]
  [UniqueKey('FIRST_DAY_OF_SCHOOL, LAST_DAY_OF_SCHOOL')]
  TFiscalYear = class(TModel)
  private
    [ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAllButRemove, 'FFiscalYear')]
    FEnrollContractList: Proxy<TList<TEnrollContract>>;
  end;

  [Entity]
  [Table('T_DOCUMENT')]
  [ID('ID', TIdGenerator.IdentityOrSequence)]
  [UniqueKey('START_DATE, STUDENT_ID, GRADE_PROGRAM_ID')]
  [Inheritance(TInheritanceStrategy.JoinedTables)]
  TDocument = class(TModel)
  private
    [Association([TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('FIRST_DAY_OF_SCHOOL', [TColumnProp.Required, TColumnProp.NoUpdate], 'FIRST_DAY_OF_SCHOOL')]
    [JoinColumn('LAST_DAY_OF_SCHOOL',  [TColumnProp.Required, TColumnProp.NoUpdate], 'LAST_DAY_OF_SCHOOL')]
    FFiscalYear: Proxy<TFiscalYear>;
  end;

  TEnrollContract = class(TDocument)
// several fields declared here
  end;

So far, I am getting an error message which says "A.LAST_DAY_OF_SCHOOL" cannot be found.

Your ManyValuedAssociation is related to TEnrollContract, but that class doesn't have a field "FFiscalYear". Such field is declared in TDocument. Even though they inherit from each other, when it comes to mapping, the class must be exact, because each db field might belong to a different table. 


Either declare your FEnrollContractList as a TList<TDocument> or declare FFiscalYear in TEnrollContract class.

Wagner,


Thanks for your reply.  Due to the design requirements of the application, neither of these are suitable.  I have a different way to accomplish what I need.