IndexOutOfRangeException ...

[John]

I have a couple of tables in my database, and 1 of my tables has a primary
key that consist of 2 columns. I'm inserting data into another table that
has a foreign key (2 columns also) based on the primary key of the 1st
table.

All of the data going in looks correct. However, when I try to do a
SubmitChanges(), I get an IndexOutOfRangeException.

I created my foreign key with the designer, so I doubt that it's wrong, and
if I change the primary key table to just have 1 column as the primary key,
everything works fine.

So what's up? Can Linq to SQL just not support this with a primary key that
spans more than 1 column? Why the error? So sad.

Thanks.

 

[Patrice]

I tried a quick repro here and it seems to work fine with a composite pk
(used as a fk from the child table).

My schema is :
create table a(f1 int not null,f2 int not null,data varchar(10),constraint t
primary key (f1,f2))
create table b(pk int identity not null,f1 int not null, f2 int not null,
data varchar(10),
constraint f foreign key (f1,f2) references a(f1,f2),
constraint b_pk primary key(pk))
go
insert into a values (1,2,'AB')

The code is :

DataClasses1DataContext dc=new DataClasses1DataContext();
b data = new b();
data.f1 = 1;
data.f2 = 2;
dc.bs.InsertOnSubmit(data);
dc.SubmitChanges();

Or do you add both records at the same time...

 

[John]

Weird ...

That's almost exactly what I've got, and I'm still getting the error. I'll
poke around a bit more and see if I can see anything else, otherwise, I have
no idea what's wrong.

 

[John]

Oops ...

Turns out this was a problem with the test data I was using and not an
actual key problem. Sorry for the trouble, but thank you Patrice for the
help.

 

[John]

Hrmm...

I think I may have spoke too soon. I don't think it's a problem with the
test data. It might be a problem with how I'm using DateTime.

In table a, I have primary key consisting of two columns. One is an int,
and the other is a datetime. I can insert values in here fine. In table b,
I have a foreign key consisting of the primary columns in table a.

If I try to insert 1 row into this table via SubmitChanges in C#, I get the
IndexOutOfRangeException. I'm inserting the following values as a row in
table b:

myRow.Column1 = 2003;
myRow.MyDateTime =
someVariableThatWasJustInsertedIntoTableAWithAValue_DateTime.UtcNow;
myRow.SomeOtherInt = 5;

I know that I have a row in table a that has part of it's primary key =
someVariableThatWasJustInsertedIntoTableAWithAValue_DateTime.UtcNow and the
other part = 5;

In fact, if I try to do this same thing using SQL Server Management Studio
with the following line:
INSERT INTO [DBO].TableB VALUES(2003, '2009-11-22 22:27:10.767', 5)

That statement inserts into table b just fine. The only difference here is
that I copied (using SQL Server Management Studio) the line '2009-11-22
22:27:10.767' from table a and put it into that insert statement.

I'm guessing this means I have to do some type of conversion? This is
basically what I have in my C# code:
var date = DateTime.UtcNow;
....
TableARow.DateTimeColumn = date;

....
NewTableBRow.DateTimeColumn = date;
NewTableBRow.Column1 = 2003;
NewTableBRow.SomeOtherInt = 5;

How should I store the DateTime into the database so this works, or is there
possibly something else wrong?

Thanks again,
-- John