CODEWITHSUNDEEP
c#.netlinq
SairuS
SairuS

Reputation: 149

Query with Nullable Values using LINQ

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?

Upvotes: 1

Views: 2247

Answers (2)

Teejay
Teejay

Reputation: 7471

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.

Upvotes: 1

L&#252;tfullah Kus
L&#252;tfullah Kus

Reputation: 302

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

Upvotes: 2

Related Questions