help with complex where statement (c# project)

help with complex where statement (c# project)

montoyammontoyam Posts: 489Questions: 116Answers: 5

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

Answers

  • montoyammontoyam Posts: 489Questions: 116Answers: 5

    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)

  • montoyammontoyam Posts: 489Questions: 116Answers: 5

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

  • montoyammontoyam Posts: 489Questions: 116Answers: 5

    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
    
  • montoyammontoyam Posts: 489Questions: 116Answers: 5
    edited April 21

    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.

  • montoyammontoyam Posts: 489Questions: 116Answers: 5

    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())")
    
  • colincolin Posts: 12,470Questions: 0Answers: 2,112

    Glad all sorted!

    Colin

Sign In or Register to comment.