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

SQL Design Schema

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

Looks like there's a DeviceId of null in your database, but your model doesn't know it's nullable. — Mike Christensen
I can confirm there is no null DeviceId's as this is the primary key of the table — Paperwaste
Which of these throws the exception? The where clauses are very different. Presumably, at least semantically, d.devices isn't an integer but rather a collection of some sort. I'm surprised that one even compiles if that's the case. — David
@David Both throw the same exception. If i use db.Devices.Where(d => d.devices == pointDetails.DeviceId); the Exception is still thrown. — Paperwaste
I'm guessing the problem is not with the primary key at all but with another column that you're selecting. Try changing select d to just select d.DeviceId and I bet it will work - not that the result is any use to you! - but then you'll know for sure. — Stephen Byrne

David David · Accepted Answer · 2014-03-03T23:48:22

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.

Specified cast is not valid Linq Query

1 Answers