How can we perform an "between xxx and yyy" find operation?

[WIERZBICKI_Stephane (WIERZBICKI Stéphane)]

Hi,

I need to retrieve entities that has been setup between to date.
I haven't found any "Between" syntax with Linq expression?

What is the best way for doing it?

Manager.Find<TProgramme>.Where(Linq.Eq('code' , 'MYPROG')
       and Linq.GreaterOrEqual('InceptionDate',Inception_Date)
       and Linq.LessOrEqual('ReviewDate', Inception_Date)).List;

or

Manager.Find<TProgramme>
      .Add(Linq['Code'] = 'MYPROG')
      .Add(Linq['InceptionDate'] >= Inception_Date)
      .Add(Linq['ReviewDate'] <= Inception_Date)
      .List;

Doesn't seems to get my records ( Inception_Date is a TDateTime)

I'm able to get results when runningthis SQL

SELECT  * FROM Programme p WHERE
  '2020-10-01' BETWEEN p.Programme_Inception_Date AND p.Programme_Review_Date AND p.Programme_Code = 'MYPROG'

Here is my TProgramme Entity

 [Entity]
  [Table('Programme')]
  [Description('')]
  [Id('FCode', TIdGenerator.None)]
  [Id('FUnderwriting_Year', TIdGenerator.None)]
  TProgramme = class
  private
    [Column('Programme_Code', [TColumnProp.Required], 10)]
    [Description('')]
    FCode: string;
    
    [Column('Underwriting_Year', [TColumnProp.Required])]
    [Description('')]
    FUnderwriting_Year: Integer;
    
    [Column('Programme', [], 200)]
    [Description('')]
    FProgramme: Nullable<string>;
    
    [Column('Programme_Inception_Date', [])]
    [Description('')]
    FInceptionDate: Nullable<TDateTime>;
    
    [Column('Programme_Review_Date', [])]
    [Description('')]
    FReviewDate: Nullable<TDateTime>;
    
    [Association([TAssociationProp.Lazy], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('Intermediary_Code', [], 'Intermediary_Code')]
    [Description('')]
    FIntermediary_Entity: Proxy<TIntermediary>;
    
    [Association([TAssociationProp.Lazy], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('Programme_Type_Code', [], 'Programme_Type_Code')]
    [Description('')]
    FProgramme_Type_Entity: Proxy<TProgramme_Type>;
    function GetIntermediary_Entity: TIntermediary;
    procedure SetIntermediary_Entity(const Value: TIntermediary);
    function GetProgramme_Type_Entity: TProgramme_Type;
    procedure SetProgramme_Type_Entity(const Value: TProgramme_Type);
  public
    property Code: string read FCode write FCode;
    property Underwriting_Year: Integer read FUnderwriting_Year write FUnderwriting_Year;
    property Programme: Nullable<string> read FProgramme write FProgramme;
    property InceptionDate: Nullable<TDateTime> read FInceptionDate write FInceptionDate;
    property ReviewDate: Nullable<TDateTime> read FReviewDate write FReviewDate;
    property Intermediary_Entity: TIntermediary read GetIntermediary_Entity write SetIntermediary_Entity;
    property Programme_Type_Entity: TProgramme_Type read GetProgramme_Type_Entity write SetProgramme_Type_Entity;
  end;

WFIW Manager.Find.List will retreive everything ! There must be an issue with my where condition , am I doing something wrong?

Thanks

[Farias_Anderson (Anderson Farias)]

Hello there,

Your Find expression is not reflecting the SQL that works. You should "invert it":

  .Add(Linq['InceptionDate'] <= Inception_Date)
  .Add(Linq['ReviewDate'] >= Inception_Date)

or

  .Add(Linq.LessOrEqual('InceptionDate',Inception_Date))
  .Add(Linq.GreaterOrEqual('ReviewDate', Inception_Date))

HTH

[WIERZBICKI_Stephane (WIERZBICKI Stéphane)]

Thanks, I'll test this tomorrow. For what I understand, in SQL I'm comparing a date value with field names where as with linq I'm comparing property with a date value).