Error in LINQ to SQL: specified cast is not valid

Question

Hi I am trying to learn LINQ, and in LINQ to SQL I have got following exception: This is a sample code from Linq In Action by Manning publications. Whats wrong?

        DataContext db = new DataContext("E:\\Mahesh\\TempFolder\\DB\\NORTHWND.MDF");

        var contacts =
            from contact in db.GetTable<Contact>()
            where contact.City == "Paris"
            select contact;

        foreach (Contact aContact in contacts)
            Console.WriteLine("Bonjour " + aContact.Name);
        Console.Read();
    }
}

[Table(Name = "Customers")]
class Contact
{
    [Column(IsPrimaryKey = true)]
    public int CustomerID { get; set; }
    [Column(Name = "ContactName")]
    public string Name { get; set; }
    [Column]
    public string City { get; set; }
}

Exception details:

System.InvalidCastException was unhandled
HResult=-2147467262
Message=Specified cast is not valid.
Source=System.Data
StackTrace:
   at System.Data.SqlClient.SqlBuffer.get_Int32()
   at Read_Contact(ObjectMaterializer`1 )
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
   at LinqDemo.Program.Main(String[] args) in c:\Users\MAHESH\Desktop\TechNode\C#\MyTechDos\LinqDemo\LinqDemo\Program.cs:line 51
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()
InnerException: 

Answer 1

If my memory serves, the Customers table in Northwind does not have CustomerID as int (I think its NVARCHAR). If you wrote the Contact class manually, instead of have LINQ to SQL generate it, make sure that the types on your class match the types in the database table

EDIT: From this link http://msdn.microsoft.com/en-us/library/bb399575(v=vs.90).aspx am inclined to think that the CustomerID field is not INT but NVARCHAR (or NCHAR for that matter)

Answer 2

I had the same issue when i tried to use Linq to Sql. Since my stored procedures used temp tables (Linq to Sql tool doesn't create classes for temp tables).So i had to create my own custom class. The same exception occured.

StackTrace: at System.Data.SqlClient.SqlBuffer.get_Int32() at Read_Contact(ObjectMaterializer1 ) at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader2.MoveNext()

This error occurred because i was trying to cast my small int DB type variable to int C# variable. In case anyone else goes through the same please refer the official Microsoft Documentation here for the DB type to C# type mappings.

Note: If you have a Specified Cast not valid issue then make sure your data types are correct.