"Specified cast is not valid" when a null value is returned as Nullable

Question

In my SQL Database I have a following view:

CREATE VIEW MyView AS
(SELECT ChangeType FROM MyTable) UNION ALL
(SELECT NULL AS ChangeType FROM MyTable)

where ChangeType has type TINYINT. Then in my C# code I have a following class:

[Table(Name = "MyView")]
public class MyView
{
    [Column]
    public Nullable<int> ChangeType;
}

which is used as follows:

var table = dataContext.GetTable<MyView>();
var elements = table.ToArray();

and when this code runs I get the following exception:

[InvalidCastException: Specified cast is not valid.]
System.Data.SqlClient.SqlBuffer.get_Int32() +6393860
Read_MyView(ObjectMaterializer`1 ) +3404
System.Data.Linq.SqlClient.ObjectReader`2.MoveNext() +42
System.Linq.Buffer`1..ctor(IEnumerable`1 source) +488
System.Linq.Enumerable.ToArray(IEnumerable`1 source) +104
MyCodeHere

If I change the second part of UNION as follows:

(SELECT CONVERT(INT,NULL) AS ChangeType FROM MyTable)

the same code runs just fine.

Why does that "cast is not valid" there? Why won't the TINYINT just be read in place of int?

See this article: TinyInt maps to Byte in the CLR – lboshuizen Oct 08 '12 at 11:58 — lboshuizen, Oct 08 '12 at 11:58
See this article: TinyInt maps to Byte in the CLR – lboshuizen Oct 08 '12 at 11:59 — lboshuizen, Oct 08 '12 at 11:59

H H · Accepted Answer · 2012-10-09T12:07:05.957

5

Why won't the TINYINT just be read in place of int?

Because TINYINT maps to either short or sbyte.

Edit: Looked it up and it's an unsigned byte, so use byte.

[Column]
public Nullable<byte> ChangeType;

Your stack-trace shows that the the Linq2Sql mapper calls .get_Int32(). Assuming this is similar to SqlDataReader.GetInt32() then that means it will attempt to read a 32 bit value, no conversions are applied.

edited Oct 09 '12 at 12:07

answered Oct 08 '12 at 11:49

H H

263,252
30
330
514

Anyway, tinyint fits into int just okay, why the exception? – sharptooth Oct 08 '12 at 11:56
Yes, you can (implicitly) convert a `byte` valueto an `int`. But you cannot call `GetInt32()` on a Byte-sized column. Stricter rules. – H H Oct 08 '12 at 11:59
Great, could you please include the last bit into the answer? – sharptooth Oct 09 '12 at 12:00

score 4 · Answer 2 · answered Oct 08 '12 at 11:50

4

Try using

[Column]
    public Nullable<Int32> ChangeType; //for int type
    public Nullable<Int16> ChangeType; //for smallint type

answered Oct 08 '12 at 11:50

RNH

93
1
8

"Specified cast is not valid" when a null value is returned as Nullable

2 Answers2