My application requires effective dated records. I need to apply a filter that finds the current record that is in effect. I was wondering if someone could suggest how to represent the following scenario in code:

EmployeeAddress Class

EmployeeAddressId EmployeeId Street1 Street2 ... EffectiveDate

An employee can have several records. Each record will have an effective date. Effective dates can occur in the past and future. I need to find the record that is currently in effect.

Example EffectiveDates: 1/1/2004 6/31/2004 2/1/2005 7/10/2005

Let's say today's date is 2/14/2005, the record that is in effect is the one with a date of 2/1/2005.

Thanks, Matt

your date filter is this: select * from employeeaddress where effectivedate = (select top 1 effectivedate from employeeAddress where employeeid = @id and effectivedate <= @date order by effectivedate desc) and employeeid=@id

In code this is (for selfservicing, you have to add two times 'null' to the FieldCompareSetPredicate constructor for adapter, see the reference manual on FieldCompareSetPredicate constructors.

// year, month, day are today's values, I also could have just used DateTime.Now, but that would have 
// the time in it of the time of call, which could miss rows inserted for today with a time later than the
// time of call.
// In-set filter. 
IPredicateExpression setFilter = new PredicateExpression(
    PredicateFactory.CompareValue(EmployeeAddressFieldIndex.EmployeeID, ComparisonOperator.Equal, employeeID),
    PredicateExpressionOperator.And,
    PredicateFactory.CompareValue(EmployeeAddressFieldIndex.EffectiveDate, 
        ComparisonOperator.LessEqual, new DateTime(year, month, day, 23, 59, 59)));

// compare set filter.
IPredicateExpression filter = new PredicateExpression(new FieldCompareSetPredicate(
    EntityFieldFactory.Create(EmployeeAddressFieldIndex.EffectiveDate),
    EntityFieldFactory.Create(EmployeeAddressFieldIndex.EffectiveDate),
    SetOperator.Equal,
    setFilter,
    null, 
    string.Empty,
    1,
    new SortExpression(
        SortClauseFactory.Create(EmployeeAddressFieldIndex.EffectiveDate, SortOperator.Descending)),
    false));

You can now use filter to filter on the effective address. Of course, you then also have to filter on the specific employee also

I'm having trouble compiling the last part. It looks like FieldCompareSetPredicate is expecting IEntityField objects yet the EntityFieldFactory returns IEntityField2 objects.

Perhaps I'm missing something here.

Thanks, Matt

mattl wrote:

I'm having trouble compiling the last part. It looks like FieldCompareSetPredicate is expecting IEntityField objects yet the EntityFieldFactory returns IEntityField2 objects.

Perhaps I'm missing something here.

Thanks, Matt

I used the selfservicing constructor, the adapter constructor requires more parameters. Please consult the reference manual for the adapter constructor. You have to pass in null for the IFieldPersistenceInfo parameters for both fields. When you do that, the code will compile as the compiler knows it has to use the adapter constructorl