InvalidCastException for a long time

Question

I have the following method:

public static T ExecuteScalar<T>( string query, SqlConnection connection, params SqlParameter[] parameters) where T : new() { // Create SqlCommand SqlCommand command = CreateCommand(query, connection, parameters); // Execute command using ExecuteScalar object result = command.ExecuteScalar(); // Return value as expected type if (result == null || result is DBNull) return default(T); return (T)result; } 

I want to have a MIN_ACTIVE_ROWVERSION database as ulong . The strange thing: the first method call below generates an error, but the second method call works fine.

A call to method 1 generates an error:

 ulong minActiveRowversion = SqlUtils.ExecuteScalar<ulong>( "SELECT CAST(MIN_ACTIVE_ROWVERSION() AS BIGINT)" , _connectionString); 

Mistake:

 System.InvalidCastException: Specified cast is not valid. 

Calling method 2 works fine:

 ulong minActiveRowversion = (ulong)SqlUtils.ExecuteScalar<long>( "SELECT CAST(MIN_ACTIVE_ROWVERSION() AS BIGINT)" , _connectionString); 

I do not understand how this is possible, because the result of the command.ExecuteScalar() method is the following:

 object result | 1955612 result.GetType() | {Name = "Int64" FullName = "System.Int64"} 

• Can someone tell me why the first script is impossible and the second script works?
• Can someone tell me how . I can solve it, so I can use scenario 1.

Answer 1

Why

You can only delete the value type in its original type. In your cast case, you first need to switch to long from object , and then to ulong .

See this question for more details:

Why can't I unzip int as decimal?

He also links a blog post from Eric Lippert.

how

One way, as you know, is to discard the original type before making it to T - unless, of course, the original type is T

As mentioned in the comments, another way is to use conversion procedures ( Convert.ToUInt64 ) and not explicit casting.

This can be achieved using Func<object, T> :

 public static T ExecuteScalar<T>( Func<object, T> conversionFunctor, string query, SqlConnection connection, params SqlParameter[] parameters) where T : new() { // Create SqlCommand SqlCommand command = CreateCommand(query, connection, parameters); // Execute command using ExecuteScalar object result = command.ExecuteScalar(); // Return value as expected type if (result == null || result is DBNull) return default(T); return conversionFunctor(result); } 

Making a call:

 ulong minActiveRowversion = SqlUtils.ExecuteScalar<ulong>( Convert.ToUInt64, "SELECT CAST(MIN_ACTIVE_ROWVERSION() AS BIGINT)" , _connectionString); 

Answer 2

Adam responds correctly identifies the problem; here is the solution: you can use LINQ to unpack any type if it can be added to T with built-in or custom conversion.

 static T UnboxUnchecked<T>(object obj) { var pe = Expression.Parameter(typeof(object)); return Expression.Lambda<Func<object,T>>( Expression.Convert( Expression.Convert(pe, obj.GetType()) , typeof (T) ) , pe ).Compile()(obj); } 

This method creates a LINQ expression that first disables the object to its actual type, and then applies the transform. Replace the last line of your method

 return (T)result; 

from

 return UnboxUnchecked<T>(result); 

for it to work.

Here is a link to an article that explains how to make this kind of conversion more efficient by caching compiled lambdas.