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

Reckoning

Looks like there's a DeviceId of null in your database, but your model doesn't know it's nullable. – Jac

I can confirm there is no null DeviceId's as this is the primary key of the table – Reckoning

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. – Flanch

@Flanch Both throw the same exception. If i use db.Devices.Where(d => d.devices == pointDetails.DeviceId); the Exception is still thrown. – Reckoning

@Paperwaste: What are the compile-time types of d.DeviceId and d.devices? What are the SQL types of their corresponding database columns? – Flanch

@Flanch apologies there was a typo in the example code i made edits. DeviceId is the primary key of type int in the database. At compile time it is int(System.Int32) – Reckoning

Can you place your cursor on d.DeviceId and set a breakpoint there and step through until you hit the error? – Westney

@Paperwaste: Ah, that makes more sense. Looking at the stack trace a bit more, I strongly suspect the error isn't in the where predicate but in the select clause when it's trying to construct an instance of the object. Perhaps Mike was right and there is a null for a column that C# doesn't think is nullable, but on a different column? Or maybe some other column's type has changed? My guess is that one or more records can't be instantiated as objects because the types aren't lining up, nothing to do with DeviceId. – Flanch

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. – Alethaalethea

@Westney I hit the error on the lines of code i specified. It does not matter which method of query i use. It throws a cast error in the depths System.Data which i am unable to step through. – Reckoning

@StephenByrne your right on the money. it works if i select d.DeviceId. – Reckoning

Ok, so that proves that it's another column. Can you update your post with the SQL schema and the class definition, I bet it's something simply mismapped. – Alethaalethea

@StephenBryrne i have provided the schemas and class definitions – Reckoning

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.

Flanch

Your onto the money, now i just have to find the column giving me grief. – Reckoning

I identified the column as DeviceStatusId. Do you have any idea how i should fix it? – Reckoning

@Paperwaste: I imagine the best approach would be to re-generate the database context. This looks like Linq to Sql, is that correct? You should be able to either update the DbContext file or just delete it and re-create it from the database. That that point any type errors in the code itself would (should) become compile-time errors, which are a lot easier to identity and correct. – Flanch

private int _DeviceStatusId; ->this is specified as "tinyint" in your database schema. Could that be the problem, that the cast from byte to int is failing on this property? – Alethaalethea

I regenerated the table in the dbml file and the query works properly now. Thanks so much for your help StephenByrne and David. Lifesavers i also learnt a fair bit today. – Reckoning

For refrence @StephenByrne the regenerated DeviceStatusId is now a byte. So you were correct. – Reckoning

Answer 2

it some time happen when you update table in DataBase but doesn't update it in DBML file

Matri