Linq & timestamps

ajj3085 posted on Friday, March 21, 2008

Hi,

I am using timestamps to check for row changes in my business objects.  Some operations are done by calling a stored procedure, and I use output parameters to get the new timestamp value back.  Linq isn't even runnign the sql though, instead it's checking parameter types and for some reason it complains.

Here's the proc signature:

CREATE PROCEDURE [dbo].[apConvertDocument]
        @FromDocumentId int,
        @FromDocumentVersion timestamp,
        @CloseFromDocument bit,
        @NewDocumentType char(1),
        @NewDocumentId int output,
        @NewDocumentVersion timestamp output
AS
-- do stuff

Here's the code I'm using to call the sp:
            [Function( Name = "dbo.apCorrectInvoice", IsComposable = false )]
            public int apCorrectInvoice(
                   [Parameter( Name = "DocumentId", DbType = "Int" )]
                   int? documentId,
                   [Parameter( Name = "DocumentVersion", DbType = "Timestamp" )]
                   byte[] documentVersion,
                   [Parameter( Name = "CreditMemoId", DbType = "Int" )]
                   ref int? creditMemoId,
                   [Parameter( Name = "CreditMemoVersion", DbType = "Timestamp" )]
                   ref byte[] creditMemoVersion,
                   [Parameter( Name = "NewInvoiceId", DbType = "Int" )]
                    ref int? newInvoiceId,
                    [Parameter( Name = "NewInvoiceVersion", DbType = "Timestamp" )]
                    ref byte[] newInvoiceVersion
           ) {
                    Binary tmp = new Binary( new byte[ 8 ] );
                    Binary tmp2 = new Binary( new byte[ 8 ] );

                    IExecuteResult result =
                            ExecuteMethodCall(
                                    this,
                                    (MethodInfo)MethodInfo.GetCurrentMethod(),
                                    documentId,
                                    new Binary( documentVersion ),
                                    creditMemoId,
                                    tmp,
                                    newInvoiceId,
                                    tmp2
                            );
                    creditMemoId = (int?)result.GetParameterValue( 2 );
                    creditMemoVersion = ( (Binary)result.GetParameterValue( 3 ) ).ToArray();
                    newInvoiceId = (int?)result.GetParameterValue( 4 );
                    newInvoiceVersion = ( (Binary)result.GetParameterValue( 5 ) ).ToArray();

                    return (int)result.ReturnValue;
            }

No matter what I do, I get the following exception:
System.ArgumentException: Argument types do not match
   at System.Linq.Expressions.Expression.Constant(Object value, Type
type)
   at System.Data.Linq.DataContext.GetMethodCall(Object instance,
MethodInfo methodInfo, Object[] parameters)
   at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance,
MethodInfo methodInfo, Object[] parameters)

Any ideas?

dpetrancuri replied on Friday, March 21, 2008

Off the top of my head, I believe BIT needs to map to BOOL, not INT. I could be wrong.

Regards,

D

ajj3085 replied on Friday, March 21, 2008

The return from a proc is an int though..

I did find the issue.  If DbType = "timestamp", the parameter decorated by the ParameterAttribute MUST be a System.Data.Linq.Binary.    I was hoping to hide that type from my business layer since I'm storing timestamps as byte[], but I guess you can't. 

You COULD, but you need to change DbType = "varbinary(8)", but that's a problem if the timestamp data type ever is expanded to 16 bytes.

Henrik replied on Friday, March 21, 2008

Andy

I have created a couple of methods that converts the byte array into an int64 and back again (see below)

I've never liked to have the timestamp carried in my classes as a byte array since I can't easily  compare it visually to another timestamp while debugging. By converting it to an int64 it's easy to identify a newer timestamp from an older. Note that the methods below reverts the timestamp byte array before converting it to an int64. This results in a smaller number that is easier to read.

 ''' <summary>
 ''' Returns a SqlTimestamp parameter value as an Int64
 ''' </summary>
 ''' <param name="param">The SqlParameter holding the timestamp as a byte array</param>
 ''' <param name="reversed">Specifies whether or not to reverse the bytes in the array before converting to Int64.</param>
 ''' <returns>Int64</returns>
 ''' <remarks>
 ''' If reversed is set to True, the function will reverse the timestamp byte array before
 ''' converting it to an Int64. This will often result in a much lower Int64 number, since a
 ''' timestamp sets the highest bytes first when it is created. Not reversing it will result
 ''' int a very high number, that is not so human readable as if it is reversed.
 ''' </remarks>
 Public Shared Function GetTimestampAsInt64(ByVal param As SqlClient.SqlParameter, ByVal reversed As Boolean) As Int64
  Dim b() As Byte = CType(param.Value, Byte())
  If reversed Then
   Array.Reverse(b)
  End If
  Return BitConverter.ToInt64(b, 0)
 End Function

 ''' <summary>
 ''' Converts an Int64 to a byte array.
 ''' </summary>
 ''' <param name="value">The value to convert</param>
 ''' <param name="isReversed">Specifies whether or not the value holds a reversed timestamp byte array.</param>
 ''' <returns></returns>
 ''' <remarks></remarks>
 Public Shared Function ConvertToTimestamp(ByVal value As Int64, ByVal isReversed As Boolean) As Byte()
  Dim b() As Byte

  b = BitConverter.GetBytes(value)
  If isReversed Then
   Array.Reverse(b)
  End If

  Return b
 End Function

Cheers

/Henrik