Specified cast is not valid Linq Query

Question

Hi i would like to query a table based on its primary key.

i have tried both

var deviceDetails = (from d in db.Devices
                     where d.DeviceId == pointDetails.DeviceId
                     select d).ToList();  

EDIT d.DeviceId

var deviceDetails = db.Devices.Single(d => d.DeviceId == pointDetails.DeviceId)

I know these return different types but this is not the issue right now.

This statement throws an invalidCastException and i dont know why. PointDetails.DeviceId is definitely a valid int. The exception is thrown even if i replace this with a hard coded int.

here is the relevant parts of the stack trace.

 at System.Data.SqlClient.SqlBuffer.get_Int32()
 at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
 at Read_Device(ObjectMaterializer`1 )
 at        System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
 at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
 at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

Any Help is apreciated as i'm out of ideas.

Class Definition and Schema Here is the Class definition of Device

[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.Devices")]
public partial class Device : INotifyPropertyChanging, INotifyPropertyChanged
{

    private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);

    private int _DeviceId;
    private int _DeviceTypeId;  
    private string _DeviceName; 
    private string _DeviceMACAddress;   
    private string _DeviceIPAddress;    
    private string _DeviceSubnetMask;
    private string _DeviceGatewayAddress;
    private int _ZoneId;
    private int _TelevisionTypeId;      
    private int _DeviceStatusId;        
    private byte _DeviceIsModified;     
    private int _DeviceSetupBaudRate;       
    private int _DeviceConfigId;        
    private byte _DeviceSetupIsInputInternalPower;      
    private int _DeviceBedSensorInput;      
    private int _DeviceEnsuiteSensorInput;      
    private int _DeviceRoomSensorInput;     
    private string _DeviceSetupString1;     
    private string _DeviceSetupString2;     
    private string _DeviceSetupString3;     
    private string _DeviceSetupString4;     
    private byte _DeviceSetupIsWiegand;     
    private int _DeviceSetupOptionId;       
    private byte _DeviceSetupIsLightMomentary;      
    private string _DeviceTestDateTime;     
    private string _DeviceTestResults;    

Here is the SQL Design

Edit Identified the column causing trouble

I selected one colmun at at time to find the one causing the cast exception and it is DeviceStatusId. What are the constraints on a tinyInt type in SQL? Any suggestions to make this cast correctly

Answer 1

I don't think the error is necessarily in your where predicate, but in your select (at least indirectly). It's likely that a column in the database (not necessarily the DeviceId column) is of a different type than a property in the compiled C# code. It could be as simple as a column being nullable (and containing a null value somewhere) where the code's property is not nullable.

Note where the exception is happening. This line suggests that it's when the results are enumerated, not when the where clause is evaluated (the call to "ToList"):

System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

And this line suggests that it's when an instance is being constructed (or rather, when an "object" is being "materialized"):

Read_Device(ObjectMaterializer`1 )

As does this one (the call to the constructor):

System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)

Basically, there's at least one column that doesn't match the code and at least one record which takes advantage of that discrepancy. When that happens, the constructor for the object being built throws an exception, because it can't make sense of the data it's receiving.