help with complex where statement (c# project)

montoyam

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);
            }

montoyam

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)

montoyam

so, i just realized my logic with what 'where's were needed is incorrect. let me figure it out and repost

montoyam

so, i don't need condition 1 at all. Just 2 and 3

                    .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
                    )

                     where 
                        (                                //(asOfCookie between EffectiveDate and Isnull(ExpireDate,getdate())
                            EffectiveDate <= AsOfCookie
                            and
                            (
                                ExpireDate >= AsOfCookie
                                or
                                ExpireDate is null
                            )
                        )

                        or JobTiteRates.JobTitleID is null

montoyam

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.

                    .Where(q =>
                               q
                               .Where("JobTitleRates.JobTitleID", null)
                               .OrWhere(
                                   r=>
                                   {
                                       r.Where("JobTitleRates.EffectiveDate", AsOfCookie, "<=");
                                       r.Where(s => {
                                                    s.Where("JobTitleRates.ExpireDate", AsOfCookie, ">=");      //requirement 2b
                                                    s.OrWhere("JobTitleRates.ExpireDate", null);
                                                }
                                           );
                                   }
                                )
                    )

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.

montoyam

aha....a coworker showed me how to "hack" the LeftJoin statement!!!

.LeftJoin("JobTitleRates", "JobTitles.JobTitleID", "="
                        , "JobTitleRates.JobTitleID and '" + AsOfCookie + "' Between JobTitleRates.EffectiveDate and isnull(JobTitleRates.ExpireDate,getdate())")

colin

Glad all sorted!

Colin