CODEWITHSUNDEEP
c#.netsql-serverlinq-to-sql
sharptooth
sharptooth

Reputation: 170549

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

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?

Upvotes: 1

Views: 5900

Answers (2)

Henk Holterman
Henk Holterman

Reputation: 273711

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.

Upvotes: 5

RNH
RNH

Reputation: 93

Try using 

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

Upvotes: 4

Related Questions