Linq code fails on newer versions of Postgresql driver

Linq code fails on newer versions of Postgresql driver

Postby blurble » Thu 05 Nov 2009 01:43

Hi,
After upgrading from v4.55.39 to 4.55.49, my LINQ code that used to work, no longer does. I have also tried the 4.65.52 beta with the same result. I have two tables (the SQL for them is below). An audiosequence record can have one or more audiosequencesteps associated with it. I am using a foreign key constraint in the audiosequencesteps table to link the two tables together. When I try and update an audiosequence record with the code I have included below, I get the error also shown below.

This worked fine with 4.55.39, but not with later versions. I have used the Entity Developer to rebuild the database context, using the 'Create from database' option.

Perhaps it is something I am doing wrong, or there is a better way to do it, so please let me know.

Thanks,
Simon


---------------------------
Error:

Code: Exception
Message: Error on executing DbCommand.
Exception Details:
Source: Devart.Data.Linq
Target Site: Boolean CanThrowLinqCommandExecutionException(System.String, System.Exception)
Inner Exception: Devart.Data.PostgreSql.PgSqlException: insert or update on table "audiosequencesteps" violates foreign key constraint "step_sequence_fk"
at Devart.Data.PostgreSql.PgSqlDataReader.e(Int32 A_0)
at Devart.Data.PostgreSql.PgSqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at Devart.Data.Linq.Provider.DataProvider.a(String A_0, IList`1 A_1, IDbCommand& A_2)
Stack Trace: at Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e)
at Devart.Data.Linq.Provider.DataProvider.a(String A_0, IList`1 A_1, IDbCommand& A_2)
at Devart.Data.Linq.v.a(MetaType A_0, q A_1, Object A_2, ModifiedMemberInfo[] A_3, Boolean A_4)
at Devart.Data.Linq.f.a(b A_0, Object A_1, ModifiedMemberInfo[] A_2, Boolean A_3)
at Devart.Data.Linq.k.a(f A_0, b A_1, Boolean A_2)
at Devart.Data.Linq.k.a(DataContext A_0, ConflictMode A_1)
at Devart.Data.Linq.k.b(DataContext A_0, ConflictMode A_1)
at Devart.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
at Devart.Data.Linq.DataContext.SubmitChanges()
at Conductor.AudioSequencesView.editSelectedAudioSequence() in C:\Source\Calypso\Conductor\Conductor\AudioSequencesView.cs:line 436

---------------------------
Table SQL

CREATE TABLE audiosequences (
id bigserial NOT NULL,
name character varying(50) NOT NULL,
description character varying(1024),
priority integer NOT NULL,
repeats integer NOT NULL,
CONSTRAINT audiosequences_pk PRIMARY KEY (id),
CONSTRAINT audiosequences_name_unique UNIQUE (name) )

CREATE TABLE audiosequencesteps (
id bigserial NOT NULL,
seqid bigint NOT NULL,
kind int NOT NULL,
filename character varying(1024),
duration integer NOT NULL,
stepnumber integer NOT NULL,
CONSTRAINT audiosequencesteps_pk PRIMARY KEY (id),
CONSTRAINT step_sequence_fk FOREIGN KEY (seqid)
REFERENCES audiosequences (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)

---------------------------
Update code:

using (cDataContext dc = new cDataContext(DB.connectionString))
{
var seq = dc.audiosequences.Single(s => s.id == seqId);
if (seq == null)
return;

if (frm.edit(this, seq) == DialogResult.OK)
{
seq.name = frm.sequenceName;
seq.description = frm.description;
seq.priority = (int)frm.priority;
seq.repeats = frm.playCount;

seq.audiosequencesteps.Clear();

List steps = frm.steps;

for (int i = 0; i < steps.Count; i++)
{
audiosequencestep ss = new audiosequencestep()
{
kind = (int)steps[i].kind,
filename = steps[i].fileName,
duration = steps[i].duration,
stepnumber = i
};

seq.audiosequencesteps.Add(ss);
}

dc.SubmitChanges();
}
}

EOF
blurble
 
Posts: 5
Joined: Wed 17 Jun 2009 04:54

Postby blurble » Thu 05 Nov 2009 02:02

Update: I just tried 4.55.42 and that works OK.
blurble
 
Posts: 5
Joined: Wed 17 Jun 2009 04:54

Postby Shalex » Fri 06 Nov 2009 16:34

This is a designed behaviour, because EntitySet.Clear() breaks relations between entities by setting the Foreign key property to null. If you want to remove child objects, please call DeleteOnSubmit explicitly or set DeleteOnNull=true for the Audiosequencestep.Audiosequence property.
Shalex
Devart Team
 
Posts: 7659
Joined: Thu 14 Aug 2008 12:44

Postby blurble » Fri 06 Nov 2009 18:05

Thanks, I'll give that a try.
blurble
 
Posts: 5
Joined: Wed 17 Jun 2009 04:54


Return to dotConnect for PostgreSQL