Linq - Specified Cast is not valid

[David]

I keep getting this error on Linq to Sql when inserting or updating
(actually, I am not sure about insert, but I am definately sure about
update)

Specified cast is not valid.

I am usually fine if I am only updating one or two fields, but if I am
updating any more than that, it fails with the error.

I get this error on different tables and the only way I have found to make
it work is to remove all the associations. If I have to do that, then Linq
is not reliable and should be dropped.

If I am well into a project and I am then forced to remove associations,
just to make an insert or an update work, then I have to go back and test
EVERYTHING else to ensure removing the association doesn't break anything.

This is not really on, especially with large scale projects.

I have searched the net for how to resolve this and have seen it is fixed in
the next version of .NET. That can't be right can it? We have this problem
now.

Anyone any ideas how to work around this? I think I might have to create a
second DBML file to do inserts and updates and have no associations, but I
don't think that is really the best way to do it.

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available

 

[David]

That seems very similar to the issue and it is a very real issue. It needs
to be fixed in 3.5, not 4.

I should not have to make my relationships based on PK or unique key. In
Sql, I can set up a query to use a join on one table to another on any field
in either table (as long as it will allow me to). I should be able to do
similar with Linq. If I am not allowed to do that, then why does the
designer allow me to?

I also shouldn't have to delete the relationships or do any work-around.

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available

I keep getting this error on Linq to Sql when inserting or updating
(actually, I am not sure about insert, but I am definately sure about
update)

Specified cast is not valid.
[cut]
I have searched the net for how to resolve this and have seen it is fixed
in the next version of .NET. That can't be right can it? We have this
problem now.

Anyone any ideas how to work around this? I think I might have to create
a second DBML file to do inserts and updates and have no associations,
but I don't think that is really the best way to do it.

Hi,

Is this
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=351358 ?

I'm not familiar with this one but my understanding for now is that it
occurs when the primary key is not the unique field used in the
association. If possible my first though would be to reverse the db design
and have the primary key being used by the FK and the other unique field
used as a unique constraint...

 

[David]

Yes, sorry. I am just frustrated that this happens, but only when I want to
update more than a couple of fields. If it is broke fully, then it should
fail when I update any fields.

I am still new to linq and only just getting linq joins to work. I have
never used the sql server relationships so I don't know. I have always done
my joins in SQL.

One of my tables, which is fairly central has over 150 fields. I have made
the autonumber field the PK, but there is another field that is unique.
However, there is some data in here which comes into the table that will
correspond to data in other tables. For this, the relationship will work and
would be normal in SQL, where I can get related data from the child tables
based on the content in this table. This is all I want to be able to do. For
example... there may be a phone number in the large table... in another
table is a list of phone numbers and who they belong to... but in the large
table, many records can have the same number. At some stage, I want to pull
out the person who is on that number.

It is a simple thing to do. However, it is not the pulling of the data that
is the problem, it is the pushing of data into it that is the problem, which
means that removing the relationship, I have to build another linq query to
pull my data.

Currently, I have had to remove all links to the table I am working with
except one table where the relationship comes from another table and is on
the PK.

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available

I know it shouldn't broken and should be fixed but there is nothing I can
do about both of those points. Could we keep our focus on trying to find a
workaround ?

Could you describe the case you are in ? Do you have a relation that
doesn't use the pk field ? What is the benefit ?

In most cases you use the PK to establish your FK reference (and If I
remember SQL Server won't allow to define a FK constraint referencing a
non primary key (or is this unique) column ?).

So is updating those relations not doable or less practical than having to
delete those relations ?

--
Patrice

That seems very similar to the issue and it is a very real issue. It
needs to be fixed in 3.5, not 4.

I should not have to make my relationships based on PK or unique key. In
Sql, I can set up a query to use a join on one table to another on any
field in either table (as long as it will allow me to). I should be able
to do similar with Linq. If I am not allowed to do that, then why does
the designer allow me to?

I also shouldn't have to delete the relationships or do any work-around.

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available

I keep getting this error on Linq to Sql when inserting or updating
(actually, I am not sure about insert, but I am definately sure about
update)

Specified cast is not valid.

[cut]
I have searched the net for how to resolve this and have seen it is
fixed in the next version of .NET. That can't be right can it? We have
this problem now.

Anyone any ideas how to work around this? I think I might have to
create a second DBML file to do inserts and updates and have no
associations, but I don't think that is really the best way to do it.

Hi,

Is this
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=351358 ?

I'm not familiar with this one but my understanding for now is that it
occurs when the primary key is not the unique field used in the
association. If possible my first though would be to reverse the db
design and have the primary key being used by the FK and the other
unique field used as a unique constraint...

 

[Patrice]

For now it seems I'm able to get the same message with :

CREATE TABLE a(
a_pk INT IDENTITY NOT NULL CONSTRAINT a_pk PRIMARY KEY,
Data VARCHAR(10) NOT NULL,
Code CHAR(2) NOT NULL CONSTRAINT a_Code UNIQUE)
GO
CREATE TABLE b(
b_pk INT IDENTITY NOT NULL,
Data VARCHAR(10) NOT NULL,
Code VARCHAR(2) NOT NULL)

In the Linq to SQL designer, I create a relation with "a" as a parent on the
"Code" field.

If I try to create a row in "b" without attaching it to an instance of "a"
I've got the "cast not specified" message (translated from French here).

However, If I affect a value to the navigational b.a property, it seems to
fix the issue.

 

[Patrice]

So with data as shown above, a relation established in the designe from a to
b using the Code column and this code :

const bool UseCode=false;
DataClasses1DataContext dc=new DataClasses1DataContext();
a aObject;
b bObject;
aObject = new a();
aObject.Data = "I'm A";
aObject.Code = "A";
(e-mail address removed)(aObject);
dc.SubmitChanges();
bObject = new b();
bObject.Data = "I'm B";

if(UseCode)
bObject.Code = "A";
else
bObject.a = aObject; //


dc.bs.InsertOnSubmit(bObject);
dc.SubmitChanges();

If UseCode is true and I'm using the Code property to establish the
relation, I've got a "Specified cast is not valid" message. If UseCode is
false and I'm using the navigational property to establish the relation then
it works...

Not sure if you are in the same case. If not try to repro the problem
(likely starting from zero as we don't need to deal with all those 150
fields, we are just interested in the smallest amount of code that allows to
reproduce the problem) as I tried here so that others can experience the
same problem and possibly help...

I'm interested as I'm using LINQ and plan to use it more heavily and never
ran yet into this issue...

 

[David]

Hi,

In my particular situation, the actual table is only a few fields, but I
have had exact same problem on my table with over 150 fields.

I have a table...

CREATE TABLE [dbo].[PenUserDetails](
[id] [int] IDENTITY(1,1) NOT NULL,
[UserID] [uniqueidentifier] NULL,
[PenID] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VendorCode] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_PenUserDetails] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


This is joined to aspnet_user on aspnet_user.UserId -> PenUserDetails.UserID
(aspnet_user is parent). With my update query, this works fine, if I only
have this, so we can put aspnet_user out of the equation.

Now, I have another table that was related but I have had to remove the
relationship. This is...

CREATE TABLE [dbo].[VendorDetails](
[id] [int] IDENTITY(1,1) NOT NULL,

 [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address1] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address2] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Town] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[County] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostCode] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_VendorDetails] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



This was joined...

PenUserDetails.VendorCode -> VenderDetails.Code

(It was also joined to the 150 field table as well on the PenID )


My query is...


using (coDataClassesDataContext dc = new coDataClassesDataContext())
{
Guid UserGuid = new
Guid(Membership.GetUser(VendorNameLabel.Text).ProviderUserKey.ToString());

var q = from p in dc.PenUserDetails
where p.UserID == UserGuid
select p;

if (q.Count() <= 0)
{
PenUserDetail pen = new PenUserDetail { UserID = UserGuid, PenID =
EditPenIDTextBox.Text.ToUpper(), VendorCode = EditCompanyTextBox.Text, Phone
= EditPhoneTextBox.Text };
dc.PenUserDetails.InsertOnSubmit(pen);
}
else
{
PenUserDetail pen = dc.PenUserDetails.First(p => p.UserID ==
UserGuid);
pen.PenID = EditPenIDTextBox.Text.ToUpper();
pen.VendorCode = EditCompanyTextBox.Text;
pen.Phone = EditPhoneTextBox.Text;
}

dc.SubmitChanges();
}



Funnily enough though, It was working fine until I added
PenUserDetails.Phone to the table (and updated the dbml) and then failed
when I put pen.VendorCode = EditCompanyTextBox.Text; and  pen.Phone =
EditPhoneTextBox.Text; into the code. (I have had this happen elsewhere...
when I have just one or two update fields, it worked fine, any more than two
(might have been 3), it failed. This is why it is odd.

I am not quite understanding what you are saying... it is getting late, so I
will have another look at your messages in the morning and try and
understand them.

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available