cant update a record with linq

Question

User2124904452 posted

I have two tables in a linq-to-sql class. I build an assaciation between the two tables with two columns both of which are not primary key column. But One column which is used as the parent column has unique values.

The problem comes when I try to update any column in the child table. The system. throws an  exception "System.InvalidCastException: Specified cast is not valid."

Could you have any knowledge or solution to this problem? 

 

 

Answer 1

User1759999623 posted

Can you provide some code associated with this?  Please include your LINQ statement.

Also, are you building a DataContext for each table separately?

Answer 2

User-330204900 posted

Are there any primary keys on the table?

Answer 3

User2124904452 posted
Sorry, let me explain There are two tables, table A and table B, in the same datacontext. Table A has columns of id, name, age. Table B has columns of id, name, product. Both id columns are primary keys. I build an association between table A and table B in the dataconext. The name column in table A is unique though is not the primary key. The name column in table A is the parent column of the association, the name column of table B is the child column of the association. The problem is that, I can update columns in table A, but cant update columns in table B. When trying to update table B, the system throws the InvalidCastException.

Answer 4

User-330204900 posted

I may be wrong but I don't think that would be valid in Dynamic Data and maybe not even Linq to SQL, have you tried say a commnd line app with the same model and then do an insert in code using linq see this video here:

Inserts with LINQ to SQL  on MSDN

If it does not work with just linq then you've not a hange with DD.

Hope this helps [:D]

Answer 5

User1759999623 posted

Can you provide your LINQ statement?

Answer 6

User1641955678 posted

Indeed, I don't think this type of relationship is supported.  Have you considered using a more standard association where table B has a foreign key column pointing to table A's ID?  This is generally the way to go in a db schema, instead of using non-PK columns.

Of course, the fact that it causes an InvalidCastException is a bug which we should fix (I think it came up before), but the behvaior after the fix would be that Dynamic Data ignores this relationship.

thanks,
David

Answer 7

User1641955678 posted

Christopher, please note that this forum relates to ASP.NET Dynamic Data, and in most cases the user does not write their own LINQ queries.  That's likely why your question went unanswered :)

thanks,
David

Answer 8

User2124904452 posted

To: davidebb.

Indeed, I don't think this type of relationship is supported. 

reply: That relationship works when navigating between 2 tables. Also I can insert any new record to those 2 tables. But It is queer that I just can not update table B.  If I insert  into table B a new id relating to the id column of table A  and build an association between the 2 tables, the problem will be OK.  Isn't that problem/bug funny?

To Christopher

reply: This is the updating linq query. In fact I am using linq, not entity framework.

Dim db As New mynudeDataContext
        Dim t2s = (From t2 In db.test2s Where t2.id = 1 Select t2).First
        t2s.product = "Apple"
        db.SubmitChanges()

Thanks for all your kind replies.

Answer 9

User2124904452 posted

I can navigate betwwen 2 tables. I can do all operations on 2 tables, but just cant update table B. That is very funny.

Answer 10

User1641955678 posted

What's the complete error stack that you get when you hit the InvalidCastException?

David

Answer 11

User-1005219520 posted

This is really a L2S question. To get a definitive answer, create a simple console application that reproduces the problem and post in the L2S forum. Keep the repro as simple as possible and complete (ie, T-SQL to create tables/relationships). If you can do that I'll make sure you get an answer.