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, Jan 16 '13 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, Jan 16 '13 at 11:21

score 2 · Accepted Answer · edited Jan 16 '13 at 15:43

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 '13 at 15:43

Paul Turner

38,949
15
102
166

answered Jan 16 '13 at 11:54

Lütfullah Kus

302
1
12

you dont need functions for compare null values. – Lütfullah Kus Jan 16 '13 at 12:01
if you add Value computation... this is what I need! – SairuS Jan 16 '13 at 15:34
can you explain what is the meaning of | and & in our case? – SairuS Jan 16 '13 at 15:41
Since `HasValue` and comparison are boolean operands, it's non-sense to use bitwise operators (`&`,`|`). You should really use standard boolean operators (see here: http://stackoverflow.com/questions/24542/using-bitwise-operators-for-booleans-in-c). – Teejay Jan 16 '13 at 17:13
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 Jan 16 '13 at 17:14

Teejay · Answer 2 · 2016-08-23T07:30:12.170

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 '16 at 07:30

answered Jan 16 '13 at 11:47

Teejay

7,210
10
45
76

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. – Ilya Ivanov Jan 16 '13 at 11:49
If both null it doesn't return true! – Teejay Jan 16 '13 at 11:51
1

`Console.WriteLine (null == null);` //true. Am I missing something? – Ilya Ivanov Jan 16 '13 at 11:52
True, but two Int32? both null behaves NOT the same, because they are references, not primitive types. – Teejay Jan 16 '13 at 11:54
1

Have you even checked it?? `Console.WriteLine((int?)null == (int?)null);` // true... `Nullable a = null; Nullable b = null; Console.WriteLine(a == b);` gives the same effect – Konrad Morawski Jan 16 '13 at 11:55
to add to Konrad comment: `int.Equals(null,null)` also true. I can play this game all day long `int? a = null;` `Console.WriteLine (a == null);` //true – Ilya Ivanov Jan 16 '13 at 11:57
At least in VB: `Dim a As Int32? = Nothing` and `Dim b As Int32? = Nothing` then `a=b` returns a Nullable(of Boolean) with value Nothing. Same thing if one has value and the other doesn't. Try it yourself. – Teejay Jan 16 '13 at 12:00
Not `bool?`, but `Int32?`. Otherwise you're right, though - only one problem with that, and it's not VB: `a = b` is **not** the same as `a == b` :) `a = b` is **assignment**, not **comparison**. `a = b` returns the value which you have just assigned. Eg. `(a = "abc") == "abc"`. It's pretty basic stuff in .NET. – Konrad Morawski Jan 16 '13 at 12:26
No! VB isn't like C#. In VB `a = b` is **assignment** and **comparison**. The compiler auto-select the correct behavior for the current context. Es. In `If (a = b)` the = is **comparison**. In `Dim a = b` th = is **assignment**. – Teejay Jan 16 '13 at 13:19
Want a proof of that? If I write `Dim a As Int32? = Nothing` and `Dim b As Int32? = 5` the `a=b` still evaluates to a **Bool?** with value Nothing. If it was like you said, it would evaluate to 5. – Teejay Jan 16 '13 at 13:21
@Teejay you're right, I'm sorry. I baselessly assumed that VB must be like C# in this aspect. I'll look it up beforehand next time. – Konrad Morawski Jan 17 '13 at 10:08
@Teejay Having learned about the difference, I decisively like C# better - as you remarked, in C# there's no need for the function you came up with at all. Anyway I upvoted your answer, retracting the initial downvote. – Konrad Morawski Jan 17 '13 at 10:16

Query with Nullable Values using LINQ

2 Answers2