none
LINQ query varbinary column comparison

    Question

  • Given a "RangeObjects" TABLE with a "StartSortKey" varbinary column, and a model class:

    class RangeObject
    {
      // ... other properties ...
    
      public byte[] StartSortKey { get; set; }
    }
    

    it's not clear to me how to construct a query with LINQ equivalent to the following SQL:

    select ...
    from RangeObjects
    where StartSortKey >= @SortKey
    

    byte[] instances are not comparable, so the following will not work:

    from obj in context.RangeObjects
    where obj.StartSortKey >= sortKey
    select ...
    

    Thursday, August 5, 2010 6:42 PM

All replies

  • Hello Jacob,

    Can I ask why you need to compare the binary field? As we know, sql sql varbinary type was mapping to System.Data.Linq.Binary in CLR. Unfortunately, operator like '>=' can not apply to Binary type.

    As a workaround, you could compare the length of the Binary fields. For example,

    from obj in context.RangeObjects
    where obj.RangeObjects.Length >= sortKey.Length
    select ...
    

    Please let me know if it does not work.
    Best  regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Tuesday, August 10, 2010 2:19 PM
  • We have a complex data type that supports range-like functionality.

    To efficiently query for objects that intersect a range, we generate binary sort keys for each object.

    For example, say we had a number range object that had start and end points.

    To query for ranges which intersect the range 3–5, we can query:

     

    select ... from NumberRanges
    where StartSortKey < @PastEnd
    and PastEndSortKey > @Start
    

     

    Where @PastEnd is 6 and @Start is 3.

    The reason the key must be binary (instead of a simple integer sort key), is that these complex data types need to support arbitrarily deep (but well defined) levels of hierarchy. We can generate a value that will lexicographically compare appropriately for any level of hierarchy. It's also a very efficient storage format.

    The proposed workaround unfortunately doesn't address this need.

    Tuesday, August 10, 2010 7:18 PM
  • Hi,

    You may consider converting the data types using sql CAST or CONVERT. Since binary data field could not compared directly in Linq to Sql, you could implicit convert it to a comparable type, for example, integer or float and then use it in a stored procedure or UDF. For more information about the type conversion in SQL: http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Wednesday, August 11, 2010 7:22 AM
  • Conversion is not an appropriate solution. Firstly there's the small problem of potentially overflowing the other comparable types (when converting). Secondly and much more importantly, numeric types are not lexicographically compared (like varbinary columns are).
    Thursday, August 12, 2010 3:59 PM
  • I posted a request for this issue at the new UserVoice site.
    Monday, September 13, 2010 9:00 PM