LINQ Issue - "Specified cast is not valid"

[=?Utf-8?B?UGF1bCBQcmV3ZXR0?=]

I'm attempting to use LINQ to insert a record into a child table and I'm
receiving a "Specified cast is not valid" error that has something to do w/
the keys involved. The stack trace is:

=============== =======
Message: Specified cast is not valid.

Type: System.InvalidC astException
Source: System.Data.Lin q
TargetSite: Boolean TryCreateKeyFro mValues(System. Object[], V ByRef)
HelpLink: null
Stack: at
System.Data.Lin q.IdentityManag er.StandardIden tityManager.Sin gleKeyManager`2 .TryCreateKeyFr omValues(Object[] values, V& v)
at
System.Data.Lin q.IdentityManag er.StandardIden tityManager.Ide ntityCache`2.Fi nd(Object[] keyValues)
at System.Data.Lin q.IdentityManag er.StandardIden tityManager.Fin d(MetaType
type, Object[] keyValues)
at System.Data.Lin q.CommonDataSer vices.GetCached Object(MetaType type,
Object[] keyValues)
at System.Data.Lin q.ChangeProcess or.GetOtherItem (MetaAssociatio n assoc,
Object instance)
at System.Data.Lin q.ChangeProcess or.BuildEdgeMap s()
at System.Data.Lin q.ChangeProcess or.SubmitChange s(ConflictMode failureMode)
at System.Data.Lin q.DataContext.S ubmitChanges(Co nflictMode failureMode)
at System.Data.Lin q.DataContext.S ubmitChanges()

(.....)

=============== =======

This error is being thrown on the following code:
============
ResponseDataCon text db = new ResponseDataCon text(m_Connecti onString);
CodebookVersion codebookVersion = db.CodebookVers ions.Single(cv =>
cv.VersionTag == m_CodebookVersi onTag);
ResponseCode rc = new ResponseCode()
{
SurveyQuestionN ame = "Q11",
Code = 3,
Description = "Yet another code"
};
codebookVersion .ResponseCodes. Add(rc);
db.SubmitChange s(); //exception gets thrown here
============

The tables in question have a FK relationship between the two of them.
The parent table's column is called 'id', is the PK, and is of type: INT
NOT NULL IDENTITY
The child table's column is called 'responseCodeTa bleId' and is of type: INT
NOT NULL

codebookVersion (parent class) maps to table tblResponseCode Table
responseCode (childClass) maps to table tblResponseCode

If I execute SQL directly, it works. e.g.
INSERT INTO tblResponseCode
(responseCodeTa bleId, surveyQuestionN ame, code, description)
VALUES (13683, 'Q11', 3, 'Yet another code')
Updates to the same class work properly. e.g.
codebookVersion .ResponseCodes[0].Description = "BlahBlahBl ah";
db.SubmitChange s(); //no exception - change is committed to db

I've examined the variable, rc, after the .Add() operation and it does,
indeed, receive the proper responseCodeTab leId, just as I would expect since
I'm adding it to that collection.

tblResponseCode Table's full definition:
COLUMN_NAME TYPE_NAME
id int identity
responseCodeTab leId int
surveyQuestionN ame nvarchar
code smallint
description nvarchar
dtCreate smalldatetime

dtCreate has a default value of GetDate().
The only other bit of useful information that I can think of is that no SQL
is ever tried against the database, so LINQ is blowing up before it ever
tries (hence the error not being a SqlException). I've profiled and verified
that no attempt is made to execute any statements on the database.
Can anyone shed any light on this situation for me? What incredibly obvious
thing am I missing here?

--
-Paul Prewett

[Steven Cheng]

Hi Paul,

From the issue description, I understand you're encounering some exception
when try inserting a main table record via a foreignkey associated table,
correct?

As you said that the direct T-SQL inserting approach work, have you tried
using the DataContext.Log to output the LINQ translated T-SQL code to see
whehter it matches the working SQL code?

#DataContext.Lo g - Logging LINQ To SQL Output to Console or Debugger Output
Window
http://www.davidhayden.com/blog/dave...ntextLogLoggin
gLINQToSQLOutpu tConsoleDebugge rOuputWindow.as px

Also, if possible, you can provide a simlified version of the two tables
and code(the DDL or table schema). I'd like to perform some tests on my
side.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
ms****@microsof t.com.

=============== =============== =============== =====
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/...tance&ln=en-us.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

>From: =?Utf-8?B?UGF1bCBQcmV 3ZXR0?= <li***@communit y.nospam>
Subject: LINQ Issue - "Specified cast is not valid"
Date: Sat, 25 Oct 2008 17:41:01 -0700

>
I'm attempting to use LINQ to insert a record into a child table and I'm
receiving a "Specified cast is not valid" error that has something to do

w/

>the keys involved. The stack trace is:

============== ========
Message: Specified cast is not valid.

Type: System.InvalidC astException
Source: System.Data.Lin q
TargetSite: Boolean TryCreateKeyFro mValues(System. Object[], V ByRef)
HelpLink: null
Stack: at

System.Data.Lin q.IdentityManag er.StandardIden tityManager.Sin gleKeyManager`2 .
TryCreateKeyFro mValues(Object[] values, V& v)

at

System.Data.Lin q.IdentityManag er.StandardIden tityManager.Ide ntityCache`2.Fi n
d(Object[] keyValues)

at

System.Data.Lin q.IdentityManag er.StandardIden tityManager.Fin d(MetaType

>type, Object[] keyValues)
at System.Data.Lin q.CommonDataSer vices.GetCached Object(MetaType type,
Object[] keyValues)
at System.Data.Lin q.ChangeProcess or.GetOtherItem (MetaAssociatio n assoc,
Object instance)
at System.Data.Lin q.ChangeProcess or.BuildEdgeMap s()
at System.Data.Lin q.ChangeProcess or.SubmitChange s(ConflictMode

failureMode)

at System.Data.Lin q.DataContext.S ubmitChanges(Co nflictMode failureMode)
at System.Data.Lin q.DataContext.S ubmitChanges()

(.....)

============== ========

This error is being thrown on the following code:
============
ResponseDataCon text db = new

ResponseDataCon text(m_Connecti onString);

CodebookVersion codebookVersion = db.CodebookVers ions.Single(cv

=>

>cv.VersionTa g == m_CodebookVersi onTag);
ResponseCode rc = new ResponseCode()
{
SurveyQuestionN ame = "Q11",
Code = 3,
Description = "Yet another code"
};
codebookVersion .ResponseCodes. Add(rc);
db.SubmitChange s(); //exception gets thrown here
============

The tables in question have a FK relationship between the two of them.
The parent table's column is called 'id', is the PK, and is of type: INT
NOT NULL IDENTITY
The child table's column is called 'responseCodeTa bleId' and is of type:

INT

>NOT NULL

codebookVersio n (parent class) maps to table tblResponseCode Table
responseCode (childClass) maps to table tblResponseCode

If I execute SQL directly, it works. e.g.
INSERT INTO tblResponseCode
(responseCodeT ableId, surveyQuestionN ame, code, description)
VALUES (13683, 'Q11', 3, 'Yet another code')
Updates to the same class work properly. e.g.
codebookVersio n.ResponseCodes[0].Description = "BlahBlahBl ah";
db.SubmitChang es(); //no exception - change is committed to db

I've examined the variable, rc, after the .Add() operation and it does,
indeed, receive the proper responseCodeTab leId, just as I would expect

since

>I'm adding it to that collection.

tblResponseCod eTable's full definition:
COLUMN_NAME TYPE_NAME
id int identity
responseCodeTa bleId int
surveyQuestion Name nvarchar
code smallint
description nvarchar
dtCreate smalldatetime

dtC

[=?Utf-8?B?UGF1bCBQcmV3ZXR0?=]

Hi Steven -

Thanks for taking a look at this.

The first table schema is listed in my original post.

tblResponseTabl e definition (which maps to CodebookVersion )
COLUMN_NAME TYPE_NAME
id int identity
versionTag nvarchar
responseVersion Tag nvarchar
dtCreate smalldatetime

dtCreate again has a default value of GETDATE()

As for the generated T-SQL, as I see the issue, there is never any generated
T-SQL. The error is coming from LINQ before it generates the T-SQL. Would
using the DataContext Log still be instructive?
--
-Paul Prewett
""Steven Cheng"" wrote:

Hi Paul,

From the issue description, I understand you're encounering some exception
when try inserting a main table record via a foreignkey associated table,
correct?

As you said that the direct T-SQL inserting approach work, have you tried
using the DataContext.Log to output the LINQ translated T-SQL code to see
whehter it matches the working SQL code?

#DataContext.Lo g - Logging LINQ To SQL Output to Console or Debugger Output
Window
http://www.davidhayden.com/blog/dave...ntextLogLoggin
gLINQToSQLOutpu tConsoleDebugge rOuputWindow.as px

Also, if possible, you can provide a simlified version of the two tables
and code(the DDL or table schema). I'd like to perform some tests on my
side.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
ms****@microsof t.com.

=============== =============== =============== =====
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/...tance&ln=en-us.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

From: =?Utf-8?B?UGF1bCBQcmV 3ZXR0?= <li***@communit y.nospam>
Subject: LINQ Issue - "Specified cast is not valid"
Date: Sat, 25 Oct 2008 17:41:01 -0700

I'm attempting to use LINQ to insert a record into a child table and I'm
receiving a "Specified cast is not valid" error that has something to do

w/

the keys involved. The stack trace is:

=============== =======
Message: Specified cast is not valid.

Type: System.InvalidC astException
Source: System.Data.Lin q
TargetSite: Boolean TryCreateKeyFro mValues(System. Object[], V ByRef)
HelpLink: null
Stack: at

System.Data.Lin q.IdentityManag er.StandardIden tityManager.Sin gleKeyManager`2 .
TryCreateKeyFro mValues(Object[] values, V& v)

at

System.Data.Lin q.IdentityManag er.StandardIden tityManager.Ide ntityCache`2.Fi n
d(Object[] keyValues)

at

System.Data.Lin q.IdentityManag er.StandardIden tityManager.Fin d(MetaType

type, Object[] keyValues)
at System.Data.Lin q.CommonDataSer vices.GetCached Object(MetaType type,
Object[] keyValues)
at System.Data.Lin q.ChangeProcess or.GetOtherItem (MetaAssociatio n assoc,
Object instance)
at System.Data.Lin q.ChangeProcess or.BuildEdgeMap s()
at System.Data.Lin q.ChangeProcess or.SubmitChange s(ConflictMode

failureMode)

at System.Data.Lin q.DataContext.S ubmitChanges(Co nflictMode failureMode)
at System.Data.Lin q.DataContext.S ubmitChanges()

(.....)

=============== =======

This error is being thrown on the following code:
============
ResponseDataCon text db = new

ResponseDataCon text(m_Connecti onString);

CodebookVersion codebookVersion = db.CodebookVers ions.Single(cv

=>

cv.VersionTag == m_CodebookVersi onTag);
ResponseCode rc = new ResponseCode()
{
SurveyQuestionN ame = "Q11",
Code = 3,
Description = "Yet another code"
};
codebookVersion .ResponseCodes. Add(rc);
db.SubmitChange s(); //exception gets thrown here
============

The tables in question have a FK relationship between the two of them.
The parent table's column is called 'id', is the PK, and is of type: INT
NOT NULL IDENTITY
The child table's column is called 'responseCodeTa bleId' and is of type:

INT

NOT NULL

codebookVersion (parent class) maps to table tblResponseCode Table
responseCode (childClass) maps to table tblResponseCode

If I execute SQL directly, it works. e.g.
INSERT INTO tblResponseCode
(responseCodeTa bleId, surveyQuestionN ame, code, description)
VALUES (13683, 'Q11', 3, 'Yet another code')
Updates to the same class work properly. e.g.
codebookVersion .ResponseCodes[0].Description = "BlahBlahBl ah";
db.SubmitChange s(); //no exception - change is committed to db

I've examined the variable, rc, after the .Add() operation and it does,
indeed, receive the proper responseCodeTab leId, just as I would expect

since

I'm adding it to that collection.

tblResponseCode Table's full definition:
COLUMN_NAME TYPE_NAME
id int identity
responseCodeTab leId int
surveyQuestionN ame nvarchar
code smallint
description nvarchar
dtCreate smalldatetime

dtC

[Steven Cheng]

Hi Paul,

Thanks for your reply.

Yes, if the error occurs before the LINQ has generated the T-SQL, the LOG
does be of less assistance.

I have just created two tables per your schema and performed some tests. It
seems my local program can sucessfully run the code snippet below:

=============== =====
using (ResponseDataCo ntextDataContex t ctx = new
ResponseDataCon textDataContext ())
{

CodebookVersion codebookVersion =
ctx.CodebookVer sions.Single(
cv =>
cv.id == 2);

MessageBox.Show (codebookVersio n.ToString());

ResponseCode rc = new ResponseCode()
{
surveyQuestionN ame= "Q11",
code = 3,
description = "Yet another code"
};
codebookVersion .ResponseCodes. Add(rc);
ctx.SubmitChang es();
=============== =

# for the two tables, I've manually added the foreign key constraint betwee
them. Not sure whether thsi matters.

And here is the LINQ log trace I've printed on my side:

=============== ==
SELECT [t0].[id], [t0].[versionTag], [t0].[responseVersion Tag],
[t0].[dtCreate]
FROM [dbo].[tblResponseTabl e] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- Context: SqlProvider(Sql 2005) Model: AttributedMetaM odel Build:
3.5.21022.8

INSERT INTO [dbo].[tblResponseCode Table]([responseCodeTab leId],
[surveyQuestionN ame], [code], [description], [dtCreate])
VALUES (@p0, @p1, @p2, @p3, @p4)

SELECT CONVERT(Int,SCO PE_IDENTITY()) AS [value]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [Q11]
-- @p2: Input SmallInt (Size = 0; Prec = 0; Scale = 0) [3]
-- @p3: Input NVarChar (Size = 16; Prec = 0; Scale = 0) [Yet another code]
-- @p4: Input SmallDateTime (Size = 0; Prec = 0; Scale = 0) [Null]
-- Context: SqlProvider(Sql 2005) Model: AttributedMetaM odel Build:
3.5.21022.8
=============== ===

If necessary, I can send you my test project for reference.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we

can improve the support we provide to you. Please feel free to let my
manager know what you think of

the level of service provided. You can send feedback directly to my manager
at: ms****@microsof t.com.
--------------------

>From: =?Utf-8?B?UGF1bCBQcmV 3ZXR0?= <li***@communit y.nospam>
References: <BB************ *************** *******@microso ft.com>

<HQ************ **@TK2MSFTNGHUB 02.phx.gbl>

>Subject: RE: LINQ Issue - "Specified cast is not valid"
Date: Mon, 27 Oct 2008 02:56:00 -0700

>
Hi Steven -

Thanks for taking a look at this.

The first table schema is listed in my original post.

tblResponseTab le definition (which maps to CodebookVersion )
COLUMN_NAME TYPE_NAME
id int identity
versionTag nvarchar
responseVersio nTag nvarchar
dtCreate smalldatetime

dtCreate again has a default value of GETDATE()

As for the generated T-SQL, as I see the issue, there is never any

generated

>T-SQL. The error is coming from LINQ before it generates the T-SQL.

Would

>using the DataContext Log still be instructive?
--
-Paul Prewett
""Steven Cheng"" wrote:

>Hi Paul,

From the issue description, I understand you're encounering some

exception

>when try inserting a main table record via a foreignkey associated

table,

>correct?

As you said that the direct T-SQL inserting approach work, have you

tried

>using the DataContext.Log to output the LINQ translated T-SQL code to

see

>whehter it matches the working SQL code?

#DataContext.L og - Logging LINQ To SQL Output to Console or Debugger

Output

>Window

http://www.davidhayden .com/blog/dave/archive/2007/08/17/DataContextLogL oggin

>gLINQToSQLOutp utConsoleDebugg erOuputWindow.a spx

Also, if possible, you can provide a simlified version of the two tables
and code(the DDL or table schema). I'd like to perform some tests on my
side.

Sincerely,

Steven Cheng