none
Operator '>' cannot be applied to Operands System.Data.Linq.Binary

    Question

  • I would like to run the following query to get all new records added to a table:

    var newRecords = from c in context.Customers
    where c.TimestampInsert > lastAnchor &&
    c.TimestampInsert <= newAnchor select c;
    
    

    The problem is TimestampInsert, lastAnchor and newAnchor are of type System.Data.Linq.Binary....and I get the following error in the Linq Expression:

    "Operator '>' cannot be applied to operands of type System.Data.Linq.Binary"
    "Operator '<=' cannot be applied to operands of type System.Data.Linq.Binary"

    Is this query possible in Linq?  Any suggestions?

    Monday, April 05, 2010 3:02 PM

Answers

  • Thanks, that's close...but i need to run the query on the server.  FYI: I just found the following article (very similar to your method) that seems to work.  Thank you very much for all your help!

    http://diegofrata.wordpress.com/2009/11/18/linq-to-sql-expression-trees-and-binary-comparing/

    Here's the work around:

    // trick linq to allow comparison on binary/timestamp datatype
    public static class BinaryComparer
    {
         public static int Compare(Binary b1, Binary b2)
          {
               throw new NotImplementedException();
          }
    }
    
    // now the query
    var newRecs = from c in context.Customers
    where BinaryComparer.Compare(c.TimestampInsert, lastAnchor) > 0 && 
    BinaryComparer.Compare(c.TimestampInsert, lastAnchor) <= 0 select c;
    • Marked as answer by bap3 Friday, April 16, 2010 2:24 AM
    Thursday, April 08, 2010 12:38 PM

All replies

  • What datatype is "TimestampInsert" in the database? Is it timestamp/rowversion? If so, it will increment for all changes; updates as well as inserts. Even in T-SQL there are a number of restrictions on how timestamp/rowversion can be used; for example you can not pass them as parameters to t-sql functions. In short, this is now how timestamp/rowversion is intended to be used.

    As an alternative, you may want to add an identity column (int not null identity) which you can use to determine in what order rows were inserted.


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Tuesday, April 06, 2010 1:47 AM
  • It's a binary(8).  The Linq2SQL designer sets the datatype to binary.  TSQL allows me to compare timestamps with binary data.  Just curious why Linq won't allow that.  Below is a valid TSQL query:

    CREATE PROCEDURE dbo.GetNewCustomersSinceLastSync
    (
    @LastAnchor binary(8),
    @NewAnchor binary(8)
    )
    
    Select * from Customers c
    WHERE c.TimstampInsert > @LastAnchor AND
    c.TimestampInsert <= @NewAnchor

    I would like to do the same query in linq like:

    var customers = from c in context.Customers
    Where c.TimestampInsert > lastAnchor &&
    c.TimestampInsert <= newAnchor 
    select c;

     

    In the example above both c.TimestampInsert, lastAnchor and newAnchor are Binary type.  Linq doesn't like the comparision resulting in the error in the subject of this thread.

    Tuesday, April 06, 2010 3:19 PM
  • It's a binary(8).  The Linq2SQL designer sets the datatype to binary.  TSQL allows me to compare timestamps with binary data.  Just curious why Linq won't allow that.  Below is a valid TSQL query:

    CREATE PROCEDURE dbo.GetNewCustomersSinceLastSync
    
    (
    
    @LastAnchor binary(8),
    
    @NewAnchor binary(8)
    
    )
    
    
    
    Select * from Customers c
    
    WHERE c.TimstampInsert > @LastAnchor AND
    
    c.TimestampInsert <= @NewAnchor
    
    

    I would like to do the same query in linq like:

    var customers = from c in context.Customers
    
    Where c.TimestampInsert > lastAnchor &&
    
    c.TimestampInsert <= newAnchor 
    
    select c;
    
    

     

    In the example above both c.TimestampInsert, lastAnchor and newAnchor are Binary type.  Linq doesn't like the comparision resulting in the error in the subject of this thread.


    Your best bet if you want to keep it as binary(8) is to wrap it in a table valued function instead of a stored procedure. That way you can compose queries around it.

    E.g.:

    CREATE function dbo.GetNewCustomersSinceLastSync (
      @LastAnchor binary(8),
      @NewAnchor binary(8)
    ) returns table as
    return (
      select *
      from dbo.Customers as c
      WHERE c.TimestampInsert > @LastAnchor AND
      c.TimestampInsert <= @NewAnchor
    );

    ...will allow you to compose linq queries around the timestamp filtered data, e.g.:

    var foo =
      from c in dc.GetNewCustomersSinceLastSync(lastAnchor, newAnchor)
      join o in dc.Orders on c.CustomerID equals o.CustomerID
      where c.PostalCode == "33027"
      group c by new { c.CustomerID, c.Name } into cg
      select new { cg.Key.CustomerID, cg.Key.Name, OrderCount = cg.Count() };
    

    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Wednesday, April 07, 2010 4:08 AM
  • Thanks KristoferA,

    But that's no quite what i meant.  I provided the TSQL example to show that you can compare binary datatypes/parameters in a SQL qeury.  I don't want to call a stored procedure from Linq, i want to use Linq in C# to perform the same query as in the TSQL example.  When i try that from my C# application, i the error in the subject line of this post.  Since SQL supports this type of query, i don't understand why Linq won't do the same.  Again, thanks a lot for the suggestions...

    Wednesday, April 07, 2010 4:39 AM
  • Not everything that is possible in T-SQL is possible in Linq-to-SQL, [and not everything possible in Linq queries can be translated into T-SQL by L2S].

    If you want built in support for the >, >=, <, <= operators for binary columns in L2S, your best bet is to file a connect suggestion (over at https://connect.microsoft.com/data ) and hope that as many people as possible will upvote it....


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Wednesday, April 07, 2010 5:31 AM
  • Hi,

    You should add this Method to convert a Binary to a string then compare them in your query.

     

    //Add this Method
            public static string TimestampToString(this System.Data.Linq.Binary binary)
            {
                byte[] binarybytes = binary.ToArray();
    
                string result = "";
                foreach (byte b in binarybytes)
                {
                    result += b.ToString() + "|";
    
                }
                result = result.Substring(0, result.Length - 1);
    
                return result;
    
            }
    
    
    var newRecordsQuery = (from c in context.Customers
                                 select c).ToArray();
    
    var newRecords = from c in newRecordsQuery
                                  where String.Compare( c.TimestampInsert.TimestampToString(), lastAnchor.TimestampToString()) > 0 &&
                                 String.Compare( c.TimestampInsert.TimestampToString(), lastAnchor.TimestampToString()) <= 0
                                 select c);

     

    Regards,

    Wednesday, April 07, 2010 6:40 PM

  • var newRecordsQuery = (from c in context.Customers

                                 select c).ToArray();



    var newRecords = from c in newRecordsQuery

                                  where String.Compare( c.TimestampInsert.TimestampToString(), lastAnchor.TimestampToString()) > 0 &&

                                 String.Compare( c.TimestampInsert.TimestampToString(), lastAnchor.TimestampToString()) <= 0

                                 select c);

     

    Regards,


    Just a FYI - the first statement will pull over _all_ customers in the database to the client, and the second one will do the comparison as a Linq-to-objects query client side. Not a problem if there is just a handful of customers, but if the customers table is large then it could be a bit expensive...
    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Thursday, April 08, 2010 1:34 AM
  • Thanks, that's close...but i need to run the query on the server.  FYI: I just found the following article (very similar to your method) that seems to work.  Thank you very much for all your help!

    http://diegofrata.wordpress.com/2009/11/18/linq-to-sql-expression-trees-and-binary-comparing/

    Here's the work around:

    // trick linq to allow comparison on binary/timestamp datatype
    public static class BinaryComparer
    {
         public static int Compare(Binary b1, Binary b2)
          {
               throw new NotImplementedException();
          }
    }
    
    // now the query
    var newRecs = from c in context.Customers
    where BinaryComparer.Compare(c.TimestampInsert, lastAnchor) > 0 && 
    BinaryComparer.Compare(c.TimestampInsert, lastAnchor) <= 0 select c;
    • Marked as answer by bap3 Friday, April 16, 2010 2:24 AM
    Thursday, April 08, 2010 12:38 PM
  • Yes,

     

            public static string TimestampToString(this System.Data.Linq.Binary binary)
            {
                byte[] binarybytes = binary.ToArray();
    
                string result = "";
                foreach (byte b in binarybytes)
                {
                    result += b.ToString() + "|";
    
                }
                result = result.Substring(0, result.Length - 1);
    
                return result;
    
            }
    
            public static int CompareBinary(this System.Data.Linq.Binary binary, System.Data.Linq.Binary binary2)
            {
                return string.Compare(binary.TimestampToString(), binary2.TimestampToString());
            }
    
    
      var newRecords = from c in context.Customers
                                 where c.TimestampInsert.CompareBinary(lastAnchor) > 0 
                                 where c.TimestampInsert.CompareBinary(lastAnchor) <= 0
                                 select c);

     

    • Proposed as answer by Link.fr Thursday, April 15, 2010 10:24 PM
    Thursday, April 08, 2010 6:34 PM
  • Hi,

    I am writing to check the status of this thread.   Could you pleas tell us how is the problem now?    

    Have a nice weekend, all!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Supportin Forum

    If you have any feedback on our support, please contactmsdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, April 16, 2010 1:25 AM
  • Thanks for checking in Lingzhi Sun.  I googled and found a solution that seems to work.  It's appears to be kind of a hack though.  Please see my post on 8/8/2010.  I'd love to here Microsoft's thought on the hack.  Thanks!
    Friday, April 16, 2010 2:27 AM