LINQ to SQL -

Question

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.InvalidCastException Source: System.Data.Linq TargetSite: Boolean TryCreateKeyFromValues(System.Object[], V ByRef) HelpLink: null Stack: at System.Data.Linq.IdentityManager.StandardIdentityManager.SingleKeyManager2.TryCreateKeyFromValues(Object[] values, V& v) at System.Data.Linq.IdentityManager.StandardIdentityManager.IdentityCache2.Find(Object[] keyValues) at System.Data.Linq.IdentityManager.StandardIdentityManager.Find(MetaType type, Object[] keyValues) at System.Data.Linq.CommonDataServices.GetCachedObject(MetaType type, Object[] keyValues) at System.Data.Linq.ChangeProcessor.GetOtherItem(MetaAssociation assoc, Object instance) at System.Data.Linq.ChangeProcessor.BuildEdgeMaps() at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges()

(.....)

This error is being thrown on the following code:

 ResponseDataContext db = new ResponseDataContext(m_ConnectionString);
 CodebookVersion codebookVersion = db.CodebookVersions.Single(cv => cv.VersionTag == m_CodebookVersionTag);
 ResponseCode rc = new ResponseCode()
    {
       SurveyQuestionName = "Q11",
       Code = 3,
       Description = "Yet another code"
    };
 codebookVersion.ResponseCodes.Add(rc);
 db.SubmitChanges(); //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 'responseCodeTableId' and is of type: INT NOT NULL.

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

If I execute SQL directly, it works. e.g.

INSERT INTO tblResponseCode 
(responseCodeTableId, surveyQuestionName, code, description)
VALUES (13683, 'Q11', 3, 'Yet another code')

Updates to the same class work properly. e.g.

codebookVersion.ResponseCodes[0].Description = "BlahBlahBlah";
db.SubmitChanges(); //no exception - change is committed to db

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

tblResponseCodeTable's full definition:
COLUMN_NAME TYPE_NAME
id                  int identity
responseCodeTableId int
surveyQuestionName  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.

I've read around and seen the problem when you have a relationship to a non PK field, but that doesn't fit my case.

Can anyone shed any light on this situation for me? What incredibly obvious thing am I missing here?

Many thanks.
Paul Prewett

Answer 1

ResponseCode rc = new ResponseCode()
    {
       SurveyQuestionName = "Q11",
       Code = 3,
       Description = "Yet another code"
    };

and:

INSERT INTO tblResponseCode 
(responseCodeTableId, surveyQuestionName, code, description)
VALUES (13683, 'Q11', 3, 'Yet another code')

Are not the same, you are not passing in the foreign key reference. Now, I'm huge n00b at LINQ2SQL, but I'd wager that LINQ2SQL is not smart enough to do that for you, and it expects it as the first parameter of the anonymous dictionary, and is trying to cast a string to an integer.

Just some ideas.

Answer 2

This block:

codebookVersion.ResponseCodes.Add(rc);
db.SubmitChanges(); //exception gets thrown here

Can you try InsertOnSubmit instead of Add? i.e.

codebookVersion.ResponseCodes.InsertOnSubmit(rc);

I think Add is not meant to be used to insert records if my memory serves me right. InsertOnSubmit is the one to use.

Answer 3

Jonathan -

Actually, LINQ-to-SQL is smart enough to figure out the FK. As I said, I've examined the variable, rc, after the .Add() operation and it does, indeed, receive the proper responseCodeTableId.

The reason that it does is because I'm adding it to the CodebookVersion, which has the id. LINQ figures that part out for me in this case and I have seen it in a watch.

Which string are you thinking is getting cast to an integer? I'm not sure I see where you're going there.

Answer 4

Chakrit -

ResponseCodes does not have an InsertOnSubmit method. That method comes if I access the ResponseCodes directly from the ResponseDataContext. e.g.

db.ResponseCodes.InsertOnSubmit(rc);

Unfortunately, this doesn't work, either, because we're back to Jonathan's point of it not knowing it's FK at that point.

I guess one other bit of information is that this is a recreation of a problem that I'm having when I bind the ResponseCodes to a DataGridView in Windows Forms. If a user adds a row to the DGV and then goes to save, I get this error.

The code I've posted above is a simplfied unit test that I created to replicate the problem in a more controlled and repeatable environment. The error is exactly the same, though. And I'm somewhat (read not-very) certain that .Add() is called on the ResponseCodes when a row is added to the DGV in the actual application.

Any other ideas?

Code like this has worked for me on other projects and I can't figure out what is different in this case.

Thanks for the input.

Answer 5

To try and narrow down the culprit.

Have you tried replacing the anonymous dictionary with something like:

ResponseCode rc = new ResponseCode();

rc.SurveyQuestName = "Q11";
rc.Code = 3;
rc.Description = "Yet Another Code";

I've yet to really work with .NET 3.5 yet (day job is still all 2.0), so I'm wondering if there is an issue with passing the data using the anonymous dictionary (The cases don't match the SQL Columns for one).

Answer 6

Indeed I have. Same problem.

Answer 7

Post up the schema of the parent table.

if you look here, some other people have had your problem. http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3493504&SiteID=1

It appears that Linq2SQL has trouble mapping some foreign keys to some primary keys. One guy had a resolution, but I think you are already mapping to an IDENTITY column.

Answer 8

Yea, I've read that and other posts, but it always seems to involve someone linking up to a field that simply has a unique contraint. Or in this guy's case (which does sound exactly like mine), he didn't get a solution.

Here's the parent table:

tblResponseTable definition (which maps to CodebookVersion)
COLUMN_NAME TYPE_NAME
id  int identity
versionTag  nvarchar
responseVersionTag  nvarchar

versionTag does have a unique contraint on it, but that's not represented anywhere that I can see in the LINQ-to-SQL stuff - and since nothing ever goes to the database... still stuck.

Answer 9

Since the database isn't being called I think you have to look at the mappings linq to sql is using. What does the Association look like? There should be an Association on both the parent and child classes. Take a look at the linq to sql Association between the two classes. The Association should have a ThisKey property. The cast that is failing is trying to cast the value of the property that ThisKey points to, I think. As far as I can tell there can be a problem when there is more than one key and the type of the first key does not match the type that ThisKey points too. I'm not sure how linq would determine what the first key is. From the looks of it you only have one key and one foreign key so that shouldn't be the problem, but the designer, if you are using it, has been known to get creative. I'm pretty much guessing, but this looks like something I've seen before.

Answer 10

Here's another thought if anyone can shed some light.

I've tried downloading the public symbols so that I can actually break into the code where the exception is being thrown and then work my way back up the stack.

However, when I do, the lines in the Call Stack become black (instead of grey) but it tells me there's no source code available for that line and only offers the disassembly. I'm not good enough to figure out what that's telling me. So is there a way that someone knows where I could actually see the source code? I thought that was the point of offering those public symbols.

Answer 11

ResponseCode rc = new ResponseCode()
{
   CodebookVersion = codebookVersion,
   SurveyQuestionName = "Q11",
   Code = 3,
   Description = "Yet another code"
};
db.ResponseCodes.InsertOnSubmit(rc);
db.SubmitChanges();

Answer 12

David -

No dice. Same error.

Answer 13

You may want to check to see that any fields in your database tables which are set by the db server when inserting a new record have that reflected in the Linq to SQL diagram. If you select a field on the Linq to SQL diagram and view its properties you will see a field called "Auto Generated Value" which if set to true will ensure all new records take on the default value specified in the database.

Answer 14

LINQ to SQL has been deprecated, FYI - http://blogs.msdn.com/adonet/archive/2008/10/29/update-on-linq-to-sql-and-linq-to-entities-roadmap.aspx.

Answer 15

I ran into a very similar problem. I'll link you over to my wordy post: http://forums.asp.net/p/1223080/2763049.aspx

And I'll also offer a solution, just a guess...

    ResponseDataContext db = new ResponseDataContext(m_ConnectionString);
    CodebookVersion codebookVersion = db.CodebookVersions.Single(cv => cv.VersionTag == m_CodebookVersionTag); 
    ResponseCode rc = new ResponseCode()
    {   
        ResponseCodeTableId = codebookVersion.Id,   
        SurveyQuestionName = "Q11",   
        Code = 3,   
        Description = "Yet another code"
    };
    db.ResponseCodes.InsertOnSubmit(rc);
    db.SubmitChanges();

Answer 16

Hi, I am having the exact same problem as you, yet I am getting this just doing a simple insert into a table, no foreign keys involved. This worked for me before so I suspect it has something to do with the database version. (I switched from SQL SERVER 2005 to 2008 not long ago). Which database are you using?

Answer 17

Somewhere in your object graph there is a conversion error, the underlying data model (or the Linq To SQL model) has changed. This is typically something like NVARCHAR(1) -> CHAR when it should be STRING, or something similar.

This error is not fun to hunt down, hopefully your object model is small.

Answer 18

Is this an example of this bug? If so, try running your code in .NET 4.0 now that the beta is out.

If, like me, you aren't ready to start using the beta, you may be able to work around the problem. The issue seems to be that LINQ does not properly support relationships defined on non-primary key fields. However, the term "primary key" does not refer to the primary key defined on the SQL table, but the primary key defined in the LINQ designer.

If you dragged your tables into the designer, then Visual Studio automatically inspects the primary key defined in the database and marks the corresponding class field(s) as "primary keys". However, these do not need to correspond to each other. You can remove the key Visual Studio chose for you, and pick another field (or group of fields). Of course, you need to make sure this is logical (you should have a unique constraint in the database on the field/fields you choose).

So I had 2 tables/classes related to eachother using an alternative key. The parent table had 2 keys: a surrogate primary key defined as an int, and an alternative natural key defined as a string. In the LINQ designer, I had defined the association using the alternative key, and I experienced the InvalidCastException whenever trying to update that association field on the child object. To work around this, I went into the LINQ designer, selected the int, and then changed the Primary Key property from True to False. Then I chose the string, and set it's Primary Key property to True. Recompiled, retested, and the InvalidCastException is gone.

Looking at your screen shot it looks like you may be able to fix your issue by changing the LINQ primary key on ResponseCode from ResponseCode.ID to ResponseCode.ResponseCodeTableID

Answer 19

We had a similar problem, caused by using non-integer keys. Details and hotfix number are here: https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=351358