SQL Conversion - ISNULL?

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

Answer 1

User1120430333 posted

https://dailydotnettips.com/linq-and-nullable-values-or-sql-isnull-with-linq/

https://docs.microsoft.com/en-us/dotnet/csharp/linq/handle-null-values-in-query-expressions

Answer 2

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

Answer 3

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)

Answer 4

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