Dealing with Nothing In IComparer

[IanRyder]

Hi All,

I Feel like I am missing something simple and I would appreciate your advice.

I am creating a simple Sort class to be used on a Generic List that has been populated from the execution of a Stored Procedure from an SQL Database via the Entity Framework. Easy Enough.

However, when I come to sort the List which is bound via a BindingSource to a DataGridView I feel like I have gone “way over the top” from a code point of view to deal with Null Values (Nothing in VS) returned from the database. As an example, which works fine, here is what I have done so far for two fields, one field being “SortCode” with NO Null values and the second field being VatNo which DOES have Null values:-

Friend Class SortVATandAccNoColumns
  Implements IComparer(Of sp_EuroBilling_NewCustomerAccNoAndVatDetails_Result)
 
  Private _strFieldName As String
  Private _sortDirection As SortOrder
  Public Sub New(ByVal strFieldName As String, ByVal sortDirection As SortOrder)
    _strFieldName = strFieldName
    _sortDirection = sortDirection
  End Sub
 
  Public Function Compare(x As sp_EuroBilling_NewCustomerAccNoAndVatDetails_Result, y As sp_EuroBilling_NewCustomerAccNoAndVatDetails_Result) As Integer Implements IComparer(Of sp_EuroBilling_NewCustomerAccNoAndVatDetails_Result).Compare
    Select Case _strFieldName
      Case "SortCode"
        If _sortDirection = SortOrder.Ascending Then
          Return x.SortCode.CompareTo(y.SortCode)
        Else
          Return y.SortCode.CompareTo(x.SortCode)
        End If
      Case "VatNo"
        If _sortDirection = SortOrder.Ascending Then
          If x.VatNo Is Nothing AndAlso y.VatNo Is Nothing Then
            Return 0
          ElseIf Not x.VatNo Is Nothing AndAlso y.VatNo Is Nothing Then
            Return 1
          ElseIf x.VatNo Is Nothing AndAlso Not y.VatNo Is Nothing Then
            Return -1
          Else
            Return x.VatNo.CompareTo(y.VatNo)
          End If
        Else
          If x.VatNo Is Nothing AndAlso y.VatNo Is Nothing Then
            Return 0
          ElseIf Not x.VatNo Is Nothing AndAlso y.VatNo Is Nothing Then
            Return -1
          ElseIf x.VatNo Is Nothing AndAlso Not y.VatNo Is Nothing Then
            Return 1
          Else
            Return y.VatNo.CompareTo(x.VatNo)
          End If
        End If
      Case Else
        Return -1
    End Select
  End Function
End Class

When I started writing this class I just had a gut feeling that I should have been able to use Generics or Nullable variables to deal with these issues but for one reason or another I cannot get them incorporated into the Sort class and I just get the feeling that I have missed something obvious here. Not seeing the “Wood for the Trees” so to speak.

So, the simple questions are “Am I over complicating this checking for Nothing here in the VatNo Case?” or “is there a simpler way to code this which just seems to be eluding me at the moment?”

Many thanks to all who provide any suggestions.

Kind regards,

Ian

NB, there are many more fields to be added to the class which will have Null values and VatNo is just one of them.

[Shaggy Hiker]

One thing to note is that you don't really care about the sort direction. Consider what you were doing in this code:

If _sortDirection = SortOrder.Ascending Then
          Return x.SortCode.CompareTo(y.SortCode)
        Else
          Return y.SortCode.CompareTo(x.SortCode)
        End If

What's the difference between the two lines in real terms? If X = Y then all options will return 0. When X > Y then the return will be 1 in the first case and -1 in the second, but the sorting routine will order them based on ascending or descending. You haven't really done anything except guarantee that they will ALWAYS sort in ascending order. The comparison is expecting that you return some measure of whether the first measure is greater or less than the second, but you return whether the second measure is greater or less than the first when the sort order is descending.

EDIT: I kind of missed the actual question. How can you sort Nothing in any way? Nothing values can sort always before or always after, but that's about it. So, you could simply say that if X is Nothing always return -1, regardless of Y, and if X is not Nothing while Y IS Nothing, then always return 1. That reduces the options by a fair amount.

[AdamPanic2013]

Null <> Nothing

If the Null is from the DB then use DBNull, or IsDBNull.
No where does it state the return value have to be (-1,0,1), only the value 0 has to mean both are same.

< 0 Lower / Before
= 0 Equal
> 0 Higher / After

[szlamany]

Actually if both x and y are nothing you return zero. If just x is nothing return -1 otherwise return 1. This puts the nothings first.

[techgnome]

There is a difference between Null and Nothing, and they are not interchangeable... Nothing means it doesn't exist... not that the value doesn't exist, but the field itself doesn't exist. But with Null, the field DOES exist, it just does not have a specific value.

-tg

[szlamany]

@tg - I believe be said he was taking the DB nulls and representing them as NOTHING on the VB client side - or I missed what he said. It's one or the other

[techgnome]

I considered that except for this line:

a code point of view to deal with Null Values (Nothing in VS) returned from the database.

He seemed to equate the two... and then looking at the code reveals this:

If x.VatNo Is Nothing AndAlso y.VatNo Is Nothing Then

which really only checks if the fields are nothing (of course they won't be nothing because the field will exist) ... but it's really the value the OP should be comparing.

-tg

[IanRyder]

Hi All,

Many thanks for all the comments so far. Not had time to digest any as yet, buried with other stuff at the moment, but I will do in due course and come back with any comments.

Cheers,

Ian

[szlamany]

Actually if both x and y are nothing you return zero. If just x is nothing return -1 otherwise return 1. This puts the nothings first.

Just to clarify...

This should really read: "If just x is nothing return -1 otherwise if y is nothing return 1. "

If both x and y are not nothing you do a STRING.COMPARE to get the -1, 0 or 1 that the comparer wants.

btw - here's a comparer I wrote 5 years ago...

    Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer _
                        Implements System.Collections.IComparer.Compare
        Dim returnVal As Integer = -1
        returnVal = [String].Compare(CType(x,  _
                        ListViewItem).SubItems(col).Text, _
                        CType(y, ListViewItem).SubItems(col).Text)
        ' Determine whether the sort order is descending.
        If order = SortOrder.Descending Then
            ' Invert the value returned by String.Compare.
            returnVal *= -1
        End If

        Return returnVal
    End Function

It does not do you NOTHING processing - just an example

[IanRyder]

Hi All,

By Shaggy Hiker
You haven't really done anything except guarantee that they will ALWAYS sort in ascending order.

Not true SH. I pass a sort direction to the Comparer and then X compared to Y always sorts Ascending and Y compared to X always sorts Descending.

By Shaggy Hiker
I kind of missed the actual question. How can you sort Nothing in any way? Nothing values can sort always before or always after, but that's about it. So, you could simply say that if X is Nothing always return -1, regardless of Y, and if X is not Nothing while Y IS Nothing, then always return 1. That reduces the options by a fair amount.

By Szlamany
This should really read: "If just x is nothing return -1 otherwise if y is nothing return 1. "

Thanks SH & Szlamany, I knew I was missing some easy points.

By AdamPanic2013
No where does it state the return value have to be (-1,0,1), only the value 0 has to mean both are same.

This is the requirement of a Sort Method on a List when using a Comparer.

By Szlamany
Actually if both x and y are nothing you return zero. If just x is nothing return -1 otherwise return 1. This puts the nothings first.

Yes agreed, my extra bit in the code is to additionally sort the contents of the fields.

By AdamPanic2013
If the Null is from the DB then use DBNull, or IsDBNull.

By techgnome
There is a difference between Null and Nothing, and they are not interchangeable... Nothing means it doesn't exist... not that the value doesn't exist, but the field itself doesn't exist. But with Null, the field DOES exist, it just does not have a specific value.

which really only checks if the fields are nothing (of course they won't be nothing because the field will exist) ... but it's really the value the OP should be comparing.

I wish it were that simple Guys. To delve into the whole Null vs Nothing debate I ultimately think that this is to do with how Entity Framework 6 deals with Null values returned from a Database through the execution of a stored procedure.

Using Datatables populated through some SQL execution routine I agree that you would check for null values in the Datatables with the appropriate methods but in the case of EF6 and a Stored Procedure it definitely sets the field to Nothing when null values are encountered which has the “knock on” effect of causing an “object reference not set to an instance of an object” when a null value (Nothing) is encountered and hence the resulting If statements in the Sort routine.

By Szlamany
btw - here's a comparer I wrote 5 years ago...

Nice trick on the Descending portion of your code. Never thought to do just an Ascending Sort test and then multiple the result by -1.

Thanks for all the knowledge everyone.

Cheers,

Ian

[Shaggy Hiker]

All this time I assumed that it was the sort routine that made use of ascending or descending comparisons. In that understanding, you compare the first item to the second item and get a result. The sort routine decides whether to put item A ahead of or after item B based on that comparison, and the only responsibility of the comparison is to return a value that indicated whether A was greater, less than, or equal to, B. It seems absurd to me that the sort routine would require the comparison routine to return A > B in one case and A < B in the other. The comparison should just compare the two, the sort routine should do the sorting based on that comparison. After a bit of searching, it does appear that such is not the case. I guess I've always only sorted in one direction. Still seems wrong, though. It does present an interesting possibliity, of course. Sorting ascending and sorting descending, when it comes to classes, could be performed on different fields such that the two sort orders bore no resemblance to each other at all.

[szlamany]

If you are sorting on multiple columns it gets even more complicated as you enter the world of ASCENDING for some columns and DESCENDING for other.

Back in my pre-PC days you would SUBTRACT each CHARACTER of sort data from CHR(255) to get the descending sort. Or from 99999999 if sorting a DATE for instance.

99999999 - 20140101 = 79859898
99999999 - 20140102 = 79859897

Now you have a DESCENDING "sort key".

[techgnome]

I wish it were that simple Guys. To delve into the whole Null vs Nothing debate I ultimately think that this is to do with how Entity Framework 6 deals with Null values returned from a Database through the execution of a stored procedure.

Using Datatables populated through some SQL execution routine I agree that you would check for null values in the Datatables with the appropriate methods but in the case of EF6 and a Stored Procedure it definitely sets the field to Nothing when null values are encountered which has the “knock on” effect of causing an “object reference not set to an instance of an object” when a null value (Nothing) is encountered and hence the resulting If statements in the Sort routine.

Eeeeew! I haven't had the chance to use EF yet.... now I'm not sure... although I see some wisdom in it. But jsut because I don't agree with it doesn't mean I can't use it...

-tg