help with complex where statement (c# project)
help with complex where statement (c# project)
montoyam
Posts: 568Questions: 136Answers: 5
in DataTables
I can't seem to wrap my head around how to pull off what I am needing to do:
.Where("JobTitleRates.JobTitleID", request.Form["JobTitleIDFilter"]) //requirement 1
.Where(q =>
q
.Where("JobTitleRates.EffectiveDate", AsOfCookie, "<=") //requirement 2a
.Where(r => {
r.Where("JobTitleRates.ExpireDate", AsOfCookie, ">="); //requirement 2b
r.OrWhere("JobTitleRates.ExpireDate", null); //requirement 2c
})
//TODO: add OrWhere("JobTiteRates.JobTitleID", null) //requirement 3
)
Note that this is a c# project, not PHP.
the final output needs to be:
where
requirement 1
and
(
(
requirement 2a
and
(
requirement 2b
or
requirement 2c
)
)
or requirement 3
)
that Lambda syntax throws me off all the time 
Now, the reason I am needing this complex where is because I am doing a left join on a table. If there was a way to put criteria 2a, 2b, and 2c in the Left join then I wouldn't need all that in the where, but that can't be done, correct?
using (var db = new Database(settings.DbType, settings.DbConnection))
{
var response = new Editor(db, "JobTitles", "JobTitleID")
.Model<JobTitlesModel>("JobTitles")
.LeftJoin("JobTitleRates", "JobTitles.JobTitleID", "=", "JobTitleRates.JobTitleID")
.Field(new Field("JobTitleRates.HourlyRate"))
.Where("JobTitleRates.JobTitleID", request.Form["JobTitleIDFilter"]) //requirement 1
.Where(q =>
q
.Where("JobTitleRates.EffectiveDate", AsOfCookie, "<=") //requirement 2a
.Where(r => {
r.Where("JobTitleRates.ExpireDate", AsOfCookie, ">="); //requirement 2b
r.OrWhere("JobTitleRates.ExpireDate", null); //requirement 2c
})
//TODO: add OrWhere("JobTiteRates.JobTitleID", null) //requirement 3
)
.Process(request)
.Data();
return Json(response);
}
This discussion has been closed.
Answers
and to clarify further:
requirement 2a,b, and c. are basically
where AsOfCookie between EffectiveDate and isnull(ExpirationDate,getdate())
but, in my understanding, you can't build a where like that, you need to stick to the .Where format of .Where(Field, evaluation, operator)
so, i just realized my logic with what 'where's were needed is incorrect. let me figure it out and repost
so, i don't need condition 1 at all. Just 2 and 3
well, i figured out the correct syntax for the where statement, but now I am seeing it is not returning what I anticipated. I am saying to show the current Rates entry (using the Rates.Effective/Expire dates) or, if no rates exist, then show the Job Title record anyway. However, what is happening is if a Rates record does exist, but the Effective/Expire dates are not current, then no record shows up, not even the JobTitle record.
What I need is a true LeftJoin, where the main table is always returned, and if there is a matching record in the leftJoin table, that data is displayed as well
Of course I can do this as a saved SQL View, but I was hoping to do it all in code.
aha....a coworker showed me how to "hack" the LeftJoin statement!!!
Glad all sorted!
Colin