Hi!

I get a bit lost when I try to do something a little more complex using filters instead of SQL.

Can anyone give me a little pointer on how to filter an entity collection and getting the same results as the following SQL query?

SELECT 
    *
FROM
    FundProduct
WHERE
    FundID = 2
AND
    StartDate <= GetDate()
AND
    GetDate() <= DateAdd(Month, TermMonths, StartDate)

Gratefully,

!Rob

Rob wrote:

Hi!

I get a bit lost when I try to do something a little more complex using filters instead of SQL.

Can anyone give me a little pointer on how to filter an entity collection and getting the same results as the following SQL query?

SELECT 
    *
FROM
    FundProduct
WHERE
    FundID = 2
AND
    StartDate <= GetDate()
AND
    GetDate() <= DateAdd(Month, TermMonths, StartDate)

You need the DateAdd function, which is currently not supported. The GetDate() you can work around that by passing in the DateTime.Now value, but the DateAdd() is a problem, as you can't use another type of expression.

Two workarounds: 1) add a computed column to the table, which calculates EndDate using DateAdd(Month, TermMonths, StartDate). Not that great. 2) Implement IExpression in a class similar to the Expression class in the ORM Support classes (see runtime library sourcecode for details), which accepts also 2 operands (2 fields) but instead produces DateAdd(Month, operand1, operand2). You can then use that expression class to produce the value <= expression of the last line.