locked
SQL Conversion - ISNULL? RRS feed

  • Question

  • User1029764681 posted

    I have the following in a SQL WHERE statement.  Is there a way to do this in LINQ?

    AND ISNULL(F.[BlockOut],F.[SchedBlockOut]) >= @myStartDate

    Thursday, November 29, 2018 7:19 PM

Answers

  • User1520731567 posted

    Hi fmrock164,

    fmrock164

    AND ISNULL(F.[BlockOut],F.[SchedBlockOut]) >= @myStartDate

    You could use ? and : to filter data,

    Check if BlockOut is null:

    if it is null,return the fist operand .

    Otherwise, return the second operand.

    like:

     DateTime myStartDate= ...;
    .Where(_=>(_.BlockOut==null?_.SchedBlockOut:_.BlockOut)>= myStartDate);

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 30, 2018 6:28 AM

All replies

  • User1520731567 posted

    Hi fmrock164,

    fmrock164

    AND ISNULL(F.[BlockOut],F.[SchedBlockOut]) >= @myStartDate

    You could use ? and : to filter data,

    Check if BlockOut is null:

    if it is null,return the fist operand .

    Otherwise, return the second operand.

    like:

     DateTime myStartDate= ...;
    .Where(_=>(_.BlockOut==null?_.SchedBlockOut:_.BlockOut)>= myStartDate);

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 30, 2018 6:28 AM
  • User1029764681 posted

    Worked Perfect.

                    .Where(x => (x.BlockOut == null ? x.SchedBlockOut : x.BlockOut) >= dtStartDate)
                    .Where(x => (x.BlockOut == null ? x.SchedBlockOut : x.BlockOut) < dtEndDate)

    I originally set it up like this.  Any drawbacks to this?

    .Where(x => x.BlockOut >= dtStartDate || x.SchedBlockOut >= dtStartDate)
                .Where(x => x.BlockOut < dtEndDate || x.SchedBlockOut < dtEndDate)

    Friday, November 30, 2018 3:52 PM
  • User1520731567 posted

    Hi fmrock164,

    The logic of these two parts is different, and the results may be different.

    The first one is based on the BlockOut field,all activities are performed under the premise of judging whether BlockOut is null.

    However,the second one is not so strict.

    Best Regards.

    Yuki Tao

    Monday, December 3, 2018 5:41 AM