I can't make my application to insert into a table with foreign key relation. I'm using SqLite3 & Mono 2.8.
My sqlite scripts look as follows:
CREATE TABLE IF NOT EXISTS appointments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) UNIQUE);
CREATE TABLE IF NOT EXISTS courses_appointments (
course_code VARCHAR(50) NOT NULL REFERENCES courses (code),
appointment_id INTEGER NOT NULL REFERENCES appointments (id));
CREATE TABLE IF NOT EXISTS courses (
code VARCHAR(50) NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE);
So i have a many-to-many relation; Next, i have mapping classes:
[Table(Name="appointments")]
public partial class Appointment
{
private EntitySet<CourseAppointment> _courseAppointments;
public Appointment()
{
_courseAppointments = new EntitySet<CourseAppointment>();
}
[Column(Name="id", AutoSync=AutoSync.OnInsert, IsPrimaryKey=true, IsDbGenerated=true)]
public int Id { get; set; }
[Column(Name="name", CanBeNull=false)]
public string Name { get; set; }
[Association(Storage="_courseAppointments", Name="appointments__courses_appointments", ThisKey="Id", OtherKey="AppointmentId")]
public EntitySet<CourseAppointment> CourseAppointments
{
get { return _courseAppointments; }
set { _courseAppointments.Assign(value); }
}
}
[Table(Name="courses_appointments")]
public partial class CourseAppointment
{
private string _courseCode;
private int _appointmentId;
private EntityRef<Appointment> _appointment;
private EntityRef<Course> _course;
[Column(Name="_course_code", Storage="_courseCode", CanBeNull=false)]
public string CourseCode
{
get
{
return _courseCode;
}
set
{
if (_course.HasLoadedOrAssignedValue)
throw new ForeignKeyReferenceAlreadyHasValueException();
this._courseCode = value;
}
}
[Column(Name="appointment_id", Storage="_appointmentId")]
public int AppointmentId
{
get
{
return _appointmentId;
}
set
{
if (_appointment.HasLoadedOrAssignedValue)
throw new ForeignKeyReferenceAlreadyHasValueException();
_appointmentId = value;
}
}
[Association(Name="appointments__courses_appointments", Storage="_appointment", ThisKey="AppointmentId", OtherKey="Id", IsForeignKey=true, DeleteOnNull=true)]
public Appointment Appointment
{
get
{
return _appointment.Entity;
}
set
{
var previousValue = _appointment.Entity;
if (previousValue == value && _appointment.HasLoadedOrAssignedValue)
return;
if (previousValue != null)
{
_appointment.Entity = null;
previousValue.CourseAppointments.Remove(this);
}
_appointment.Entity = value;
if (value != null)
{
value.CourseAppointments.Add(this);
_appointmentId = value.Id;
}
else
{
_appointmentId = 0;
}
}
}
[Association(Name="courses__courses_appointments", Storage="_course", ThisKey="CourseCode", OtherKey="Code", IsForeignKey=true, DeleteOnNull=true)]
public Course Course
{
get
{
return _course.Entity;
}
set
{
var previousValue = _course.Entity;
if (previousValue == value && _course.HasLoadedOrAssignedValue)
return;
if (previousValue != null)
{
_course.Entity = null;
previousValue.CourseAppointments.Remove(this);
}
_course.Entity = value;
if (value != null)
{
value.CourseAppointments.Add(this);
_courseCode = value.Code;
}
else
{
_courseCode = null;
}
}
}
}
[Table(Name="courses")]
public partial class Course
{
private EntitySet<CourseAppointment> _courseAppointments;
public Course()
{
_courseAppointments = new EntitySet<CourseAppointment>();
}
[Column(Name="code", CanBeNull=false, IsPrimaryKey=true)]
public string Code { get; set; }
[Column(Name="name", CanBeNull=false)]
public string Name { get; set; }
[Association(Name="courses__courses_appointments", Storage="_courseAppointments", ThisKey="Code", OtherKey="CourseCode")]
public EntitySet<CourseAppointment> CourseAppointments
{
get { return _courseAppointments; }
set { _courseAppointments.Assign(value); }
}
}
}
When i execute a code:
var a =new Appointment() { Name = "abracadabra" };
appointments.InsertAllOnSubmit(entities);
dc.SubmitChanges();
An exception is thrown:
System.ArgumentException: Value does not fall within the expected range. at DbLinq.Util.MemberInfoExtensions.GetMemberValue (System.Reflection.MemberInfo memberInfo, System.Object o) [0x00000] in :0 at System.Data.Linq.DataContext+c__AnonStorey2C.<>m__29 (System.Data.Linq.Mapping.MetaDataMember m) [0x00000] in :0 at System.Linq.Enumerable+c__Iterator10`2[System.Data.Linq.Mapping.MetaDataMember,System.Object].MoveNext () [0x00000] in :0 at System.Collections.Generic.List`1[System.Object].AddEnumerable (IEnumerable`1 enumerable) [0x00000] in :0 at System.Collections.Generic.List`1[System.Object]..ctor (IEnumerable`1 collection) [0x00000] in :0 at System.Linq.Enumerable.ToList[Object] (IEnumerable`1 source) [0x00000] in :0 at System.Data.Linq.DataContext.UpdateReferencedObjects (System.Object root) [0x00000] in :0 at System.Data.Linq.DataContext.InsertEntity (System.Object entity, DbLinq.Data.Linq.Sugar.QueryContext queryContext) [0x00000] in :0 at System.Data.Linq.DataContext.SubmitChangesImpl (ConflictMode failureMode) [0x00000] in :0 at System.Data.Linq.DataContext.SubmitChanges (ConflictMode failureMode) [0x00000] in :0 at System.Data.Linq.DataContext.SubmitChanges () [0x00000] in :0 at Controllers.HomeController.InsertAppointments (System.Data.Linq.DataContext dc) [0x00000] in :0 at Controllers.HomeController.Index () [0x00000] in :0
DataContext.Log contains this:
INSERT INTO "appointments" ("name") VALUES (:Name) -- :Name: Input String (Size = 0; Prec = 0; Scale = 0) [слесарь по обслуживанию буровых 4-5 разряда] -- Context: SQLite Model: AttributedMetaModel Build: 3.5.0.0 SELECT last_insert_rowid() -- Context: SQLite Model: AttributedMetaModel Build: 3.5.0.0
When i comment AssociationAttribute on Appointment.CourseAppointments all works fine...
Yes it does! I've had to mark a CourseAppointment.CourseCode and CourseAppointment.AppointmentId as distributed primary key with
IsPrimaryKey=true