Entity framework - not work if pk is unsigned

annie · Post by **annie** » Wed 16 Jul 2008 19:58

for the sample project MasterDetail. I changed the pk in all the tables to bigint(20). then I tried to insert a new record to emp table with the following code:

TestModel.emp eml = new emp();
eml.EMPNO = Convert.ToInt64(3);
eml.ENAME = "3 relation test";
TestModel.dept dep = entities.dept.Where("it.DEPTNO=@id", new ObjectParameter("id", Convert.ToInt64(0))).First();
eml.dept = dep;
entities.AddToemp(eml);
int i=entities.SaveChanges();

it give me the error when debugged to the last line:
InnerException: System.ArgumentException
Message="The specified value is not an instance of type 'Edm.Int64'\r\nParameter name: value"
Source="System.Data.Entity"
ParamName="value"

it is working fine if pk is int.

Is there anyway to fix in the code, or if it's a bug could you pls fix ASAP, cause my entire database is using bigint(20) as pk.

Thanks

AndreyR · Post by **AndreyR** » Thu 17 Jul 2008 11:27

Hello, Annie.

Check your SSDL and CSDL mapping, the type of bigint column should be bigint and Int64, accordingly.
We could not reproduce described error, your code works OK at our side.

Regards, Andrey.

annie · Post by **annie** » Thu 17 Jul 2008 14:05

Hello, Andrey

Thanks for your reply.

The SSDL and CSDL mapping IS bigint and int64, the MasterDetail sample itself running fine, it's just when I trying to create a new emp entity and saveChanges will cause the error. It's because the pk is Unsigned, once I checked the "Unsigned" off, it's working no problem.

My code can be add to the tsbUpdate_Click event

private void tsbUpdate_Click(object sender, EventArgs e) {

if (entities != null) {
TestModel.emp eml = new emp();
eml.EMPNO = Convert.ToInt64(3);
eml.ENAME = "test";
TestModel.dept dep = entities.dept.Where("it.DEPTNO=@id", new ObjectParameter("id", Convert.ToInt64(0))).First();
eml.dept = dep;
entities.AddToemp(eml);

int changes = entities.SaveChanges();
statusLabel.Text = string.Format("Updated ({0} changes).", changes);
}
else
statusLabel.Text = "Cannot update: not opened.";
}

CREATE TABLE `test`.`dept` (
`DEPTNO` bigint(20) unsigned NOT NULL,
`DNAME` varchar(200) default NULL,
`LOC` varchar(200) default NULL,
PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `test`.`emp` (
`EMPNO` bigint(20) unsigned NOT NULL,
`DEPTNO` bigint(20) unsigned default NULL,
`ENAME` varchar(200) default NULL,
`JOB` varchar(200) default NULL,
`MGR` int(11) default NULL,
`HIREDATE` datetime default NULL,
`SAL` float default NULL,
`COMM` float default NULL,
PRIMARY KEY (`EMPNO`),
KEY `FK_emp` (`DEPTNO`),
CONSTRAINT `FK_emp` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Is there any way to fix this?

-Annie
Regards, Annie

AndreyR · Post by **AndreyR** » Fri 18 Jul 2008 11:38

Hello, Annie.

The conversion of MySQL unsigned bigint to the CLR System.Int64 type can cause an overflow.
That's why in the upcoming build unsigned bigint is mapped to System.Decimal by default.
If you want to use unsigned bigint, please change the mapping to Decimal manually or wait for the build and regenerate the model.

Regards, Andrey.

annie · Post by **annie** » Fri 18 Jul 2008 19:09

Thanks, Andrey.

Manually fix does solve the problem with sample MasterDetail. However, when I apply to my project (which has a complicated db schema), I got another error when trying to update record(there is no ConcurrencyConflict in db):
Message="Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries."

The other issue I found are:
1, when generate the model, datatype "time" changed to "DateTime"
2, dateTime method (dateTime.Add()) is not supported,(is this a bug?)
var appts = from c in context.pt_appointments
select new
{
c.id,
appt_schedule_id=c.clinic_schedules.id,
physician_name = c.clinic_schedules.name,
c.start_datetime,
reason=c.schedule_reason.description,
c.room_number,
c.status,
c.duration,
pt_name= c.patients.lastname.ToUpper()+","+c.patients.firstname.ToUpper(),
end_datetime=c.start_datetime.Add(c.duration) //not working
//end_datetime=c.start_datetime+c.duration not working either
};

BTW, when will be the upcoming release, I am looking forword to it.

Regards,
Annie.

AndreyR · Post by **AndreyR** » Mon 21 Jul 2008 10:08

Hello, Annie.

Could you please send us (support*devart*com) the DDL script of your DB objects and a small test project or piece of code to reproduce the issue with the update?
The mapping of MySQL Time type will be corrected in the next build.
The DateTime.Add method is not supported in LINQ to Entities, so no support of it can be available from our side.

Regards, Andrey.