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; }
}

Error

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:

What data type is contact.City? Are you sure it is a string? — bluevector
– bluevector, Commented Jun 15, 2012 at 12:32
@jonnyGold in db contact.City is nvarchar, its Northwind db's Customers table — Mahesha999
– Mahesha999, Commented Jun 15, 2012 at 12:47
@TimSchmelter an image is worth hundred words ;p but code added ;p — Mahesha999
– Mahesha999, Commented Jun 15, 2012 at 12:51
Now while adding more of code SF says: Your post does not have much context to explain the code sections; please explain your scenario more clearly. now whats this???? — Mahesha999
– Mahesha999, Commented Jun 15, 2012 at 13:01
View Detail! View Detail! Tell us explicitly what the inner exception is. — Totero
– Totero, Commented Jun 15, 2012 at 13:44

John Gathogo · Accepted Answer · 2012-06-15 14:24:17Z

5

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)

answered Jun 15, 2012 at 14:24

John Gathogo

4,6753 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rishi Kc · Accepted Answer · 2018-03-25 13:38:13Z

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.

JanBorup · Accepted Answer · 2014-03-14 10:27:32Z

0

Holy Cow... this was a killer for me.

Actually this 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 Upper Cased A in Area was the problem.

Normally the SQL is NOT case sensitive for field-names, but if your SQL collation is DanishNorwegian, then this does work, and i get the "specified cast is not valid".

Strange error.

answered Mar 14, 2014 at 10:27

JanBorup

5,6451 gold badge31 silver badges17 bronze badges

Comments

user10364393 · Accepted Answer · 2020-05-04 07:49:16Z

0

Actually, after importing table to model, I changed one of the variable's datatype in DB but didn't make that change in model. It's better to delete your table from models and regenerate it.

answered May 4, 2020 at 7:49

user10364393

Collectives™ on Stack Overflow

Error in LINQ to SQL: specified cast is not valid

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related