Query with Nullable Values using LINQ

Question

I am trying to figure out how to make a query in LINQ where some values are nullable.

Next is my table:

ID int
Key1 int
Key2 int?
Key3 int?
Value string

uniq = Key1+Key2+Key3+Value

Now I need to check if an existing record is there already base on a unique constraint.

I tried the following:

Object tmp = model.table.FirstOrDefault(row => row.Key1 == newItem.Key1 &&
             (row.Key2.HasValue && newItem.Key2.HasValue && row.Key2.Value == newItem.Key2.Value) &&
             (row.Key3.HasValue && newItem.Key3.HasValue && row.Key3.Value == newItem.Key3.Value) &&
             row.Value == newItem.Value);

and:

Object tmp = model.table.FirstOrDefault(row => row.Key1 == newItem.Key1 &&
             row.Key2 == newItem.Key2 &&
             row.Key3 == newItem.Key3 &&
             row.Value == newItem.Value);

But both didn't get me correct results when one of the keys is null!

Is there a way to write a correct LINQ query?

if both row.Key2 and newItem.Key2 are null, should results be present in tmp? — Ilya Ivanov
– Ilya Ivanov, Commented Jan 16, 2013 at 11:16
Yes! I just realize that first query is wrong. If a row(1,222,null,null,3)... and I'm querying with same data should be returned — SairuS
– SairuS, Commented Jan 16, 2013 at 11:21

Paul Turner · Accepted Answer · 2013-01-16 15:43:01Z

2

object tmp= model.table.FirstOrDefault(t => 
    t.Key1 == newItem.Key1 
    && ((!t.Key2.HasValue & !newItem.Key2.HasValue) 
        | t.Key2.Value == newItem.Key2.Value)                             
    && ((!t.Key3.HasValue & !newItem.Key3.HasValue) 
        | t.Key3.Value == newItem.Key3.Value) && t.Value == newItem.Value);

edited Jan 16, 2013 at 15:43

Paul Turner

39.9k17 gold badges110 silver badges170 bronze badges

answered Jan 16, 2013 at 11:54

Lütfullah Kus

3021 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Lütfullah Kus Over a year ago

you dont need functions for compare null values.

SairuS Over a year ago

if you add Value computation... this is what I need!

SairuS Over a year ago

can you explain what is the meaning of | and & in our case?

Teejay Over a year ago

Since HasValue and comparison are boolean operands, it's non-sense to use bitwise operators (&,|). You should really use standard boolean operators (see here: stackoverflow.com/questions/24542/…).

Teejay Over a year ago

Anyway, as I wrote at the end of my answer (EDIT 2) you can simply use == without the need of checking if your variable hasValue, at least this is valid for C#.

Teejay · Accepted Answer · 2016-08-23 07:30:12Z

1

Some time ago, I wrote a small function to handle these kind of situations:

Private Function EqualOrBothNull(ByVal int1 As Int32?, ByVal int2 As Int32?) As Boolean
    Select Case True
        Case (int1 Is Nothing AndAlso int2 Is Nothing)
            Return True
        Case (int1 Is Nothing AndAlso int2 IsNot Nothing) OrElse (int1 IsNot Nothing AndAlso int2 Is Nothing)
            Return False
        Case (int1 IsNot Nothing AndAlso int2 IsNot Nothing)
            Return int1 = int2
    End Select
End Function

It's in VB.NET, but it should be simple to convert it in C#:

private bool EqualOrBothNull(Nullable<Int32> int1, Nullable<Int32> int2) {
    switch (true) {
        case (int1 == null && int2 == null):
            return true;
        case (int1 == null && int2 != null) || (int1 != null && int2 == null):
            return false;
        case (int1 != null && int2 != null):
            return int1 == int2;
    }
}

Then, you can simply write:

Object tmp = model.table.FirstOrDefault(row =>
                      EqualOrBothNull(row.Key1, newItem.Key1) &&
                      EqualOrBothNull(row.Key2, newItem.Key2) &&
                      EqualOrBothNull(row.Key3, newItem.Key3) &&
                      EqualOrBothNull(row.Value, newItem.Value));

To reply to the several comments to my answer:

At least in VB.NET, a comparison of two Nullable(Of T) always evaluates to a Boolean? with value of Nothing if at least one of them has NO value:

Enter image description here

And NO, a = b is not only an assignment in VB.NET.

The operator = is context-base auto-switched by the compiler between assignment and comparison.

Moreover, in Quickwatch mode, it's always parsed as a comparison.

C# behaves differently from VB:

Just checked, in C# the simple == operand acts like you would expect (like Ilya Ivanov and Konrad Morawski turned out in comments), so there is no need to use HasValue in this question's scenario.

edited Aug 23, 2016 at 7:30

answered Jan 16, 2013 at 11:47

Teejay

7,55111 gold badges52 silver badges80 bronze badges

12 Comments

Ilya Ivanov Over a year ago

Why not just to use == operator on int? operands. If one operand is null and other is not - it will return false. If both null - return true. If both not null - it will compare values.

Teejay Over a year ago

If both null it doesn't return true!

Ilya Ivanov Over a year ago

Console.WriteLine (null == null); //true. Am I missing something?

Teejay Over a year ago

True, but two Int32? both null behaves NOT the same, because they are references, not primitive types.

Konrad Morawski Over a year ago

Have you even checked it?? Console.WriteLine((int?)null == (int?)null); // true... Nullable<Int32> a = null; Nullable<Int32> b = null; Console.WriteLine(a == b); gives the same effect

|

Collectives™ on Stack Overflow

Query with Nullable Values using LINQ

2 Answers 2

5 Comments

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related