Error in LINQ to SQL: the specified listing is invalid

Question

Hi, I am trying to learn LINQ, and in LINQ to SQL I have the following exception: This is sample code from Linq In Action by Manning. What's 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 is being served, the Customers table in Northwind does not have CustomerID as int (I think its NVARCHAR). If you wrote the Contact class manually, instead of generating LINQ to SQL, make sure the types in 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 I tend to think that the CustomerID field is not INT, but NVARCHAR (or NCHAR, for that gone)

Answer 2

Sacred cow ... for me it was a killer.

Actually, it was a SQL Collation problem for me, because I had a field in LINQ-to-SQL called Dataarea , but in my SQL view this field was called Dataarea . The problem was in upper building A in the Area.

Normally, SQL is not case sensitive for field names, but if your SQL collation is Danish Norwegian, then this works, and I get that "the specified cast is invalid."

A strange mistake.

Answer 3

I had the same problem when I tried to use Linq to Sql . Since my stored procedures used temporary tables (the Linq to Sql tool does not create classes for temporary tables). So I had to create my own custom class. The same exception occurred.

StackTrace: in System.Data.SqlClient.SqlBuffer.get_Int32 () in Read_Contact (ObjectMaterializer 1 ) at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader 2.MoveNext ()

This error occurred due to the fact that I tried to use a variable of type small int for an int C # variable. In case anyone else goes through this, refer to the official Microsoft documentation here for DB type mappings for C # types.

Note. If you have an unacceptable problem with a specific failure, make sure your data types are correct.