Two functions in PredicateExpression

Posts   
 
    
tprohas
User
Posts: 257
Joined: 23-Mar-2004
# Posted on: 10-Jun-2008 20:39:59   

LLBLGen 2.5 Runtime: v2.0.50727 ASP.NET 2.0

Hello all,

I'm trying to create the following SQL as a predicate expression to use for fetching a entity called VisitRequestEntity. The following is the SQL which I'm trying to reproduce.


SELECT
    vr.RequestorFirstName,
    vr.RequestorLastName,
    vr.FacilityName,
    sv.EventName,
    vr.StartTime,
    DAY(vr.StartTime)
FROM
    dbo.VisitRequest vr
LEFT JOIN
    dbo.ScheduledVisit sv ON vr.ScheduledVisitID = sv.ScheduledVisitID
WHERE
    --// Filter out visit requests which have evaluations.
    vr.VisitRequestID NOT IN (SELECT VisitRequestID FROM dbo.EvaluationAnswer)
-- AND
--  vr.StartTime >= GETDATE()
AND
    DAY(vr.StartTime) = dbo.GetCalendarDayXDaysFromNow(GETDATE(), 9)
ORDER BY
    vr.StartTime

The line DAY(vr.StartTime) = dbo.GetCalendarDayXDaysFromNow(GETDATE(), 9) is really what I need to create as a predicate. Can anyone give me some direction as to how this can be done?

Thanks

tprohas
User
Posts: 257
Joined: 23-Mar-2004
# Posted on: 10-Jun-2008 23:26:15   

OK, I think I've solved this. Here is the code that I used. This really works great! Good job Frans!


int daysFromNow = 9;
            IExpression daysFromNowFuction = new DbFunctionCall("CDGVisitProgram", "dbo", "GetCalendarDayXDaysFromNow", new object[] { DateTime.Now, daysFromNow });
            IExpression startTimeDayFunction = new DbFunctionCall("DAY", new object[] { VisitRequestFields.StartTime });
            EntityField2 startDateField = VisitRequestFields.StartTime.SetExpression(startTimeDayFunction);
            predicateBucket.PredicateExpression.Add(startDateField == daysFromNowFuction);