Hi to All,

Short description:

In a Master / Detail scenario when I try to call master adapter.SaveEntity with recursion, a previous not saved master instance's Detail INSERTS also sent to the database with already not existing foreign key. Before this adapter.SaveEntity some adapter.DeleteEntityDirectly was used.

Here is an isolated demo. The 3 table creation script also copied here. Sorry this is the shortest abstract version what I could isolate. Any thoughts?

Thx for answers horo

using (IDataAccessAdapter adapter = new DataAccessAdapter()) { adapter.DeleteEntitiesDirectly(typeof(TestDetailEntity), null); adapter.DeleteEntitiesDirectly(typeof(TestReferencedEntity), null); adapter.DeleteEntitiesDirectly(typeof(TestMasterEntity), null); }

TestReferencedEntity referenced = new TestReferencedEntity(); referenced.ReferencedData = "-"; using (var adapter = new DataAccessAdapter()) { adapter.SaveEntity(referenced, true, true); }

//----------------

TestMasterEntity master = new TestMasterEntity(); master.MasterData = "-";

TestDetailEntity detail = new TestDetailEntity(); detail.DetailData = "-"; detail.TestReferenced = referenced; master.TestDetail.Add(detail); using (IDataAccessAdapter adapter = new DataAccessAdapter()) { adapter.SaveEntity(master, true, true); }

detail = new TestDetailEntity(); detail.DetailData = "-";

// A) version: Last line save will throw detail.TestReferenced = referenced;

// B) version: // Last line save works correctly in my real program, but here can not isolate this // behaviour. Here this B) version also throws the same exception than A) version.

//Uncomment this: detail.ReferencedId = referenced.ReferencedId;

master.TestDetail.Add(detail);

// Choose not to save (if we save here, last line SaveEntity will work correctly) /* using (var adapter = new DataAccessAdapter()) { adapter.SaveEntity(master, true, true); }*/

using (IDataAccessAdapter adapter = new DataAccessAdapter()) { adapter.DeleteEntitiesDirectly(typeof(TestDetailEntity), null); adapter.DeleteEntitiesDirectly(typeof(TestMasterEntity), null); }

master = new TestMasterEntity(); master.MasterData = "-";

detail = new TestDetailEntity(); detail.DetailData = "-"; detail.TestReferenced = referenced; master.TestDetail.Add(detail);

// This save will throw: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Test_Detail_Test_Master". The conflict occurred in database "Test", table "dbo.Test_Master", column 'MasterID'." using (IDataAccessAdapter adapter = new DataAccessAdapter()) { adapter.SaveEntity(master, true, true); }

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Test_Master]') AND type in (N'U')) BEGIN CREATE TABLE [Test_Master]( [MasterID] [int] IDENTITY(1,1) NOT NULL, [MasterData] nvarchar NOT NULL, CONSTRAINT [PK_Test_Master] PRIMARY KEY CLUSTERED ( [MasterID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Test_Referenced]') AND type in (N'U')) BEGIN CREATE TABLE [Test_Referenced]( [ReferencedID] [int] IDENTITY(1,1) NOT NULL, [ReferencedData] nvarchar NOT NULL, CONSTRAINT [PK_Test_Referenced] PRIMARY KEY CLUSTERED ( [ReferencedID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Test_Detail]') AND type in (N'U')) BEGIN CREATE TABLE [Test_Detail]( [DetailID] [int] IDENTITY(1,1) NOT NULL, [MasterID] [int] NOT NULL, [DetailData] nvarchar NOT NULL, [ReferencedID] [int] NOT NULL, CONSTRAINT [PK_Test_Detail] PRIMARY KEY CLUSTERED ( [DetailID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Test_Detail_Test_Master]') AND parent_object_id = OBJECT_ID(N'[dbo].[Test_Detail]')) ALTER TABLE [dbo].[Test_Detail] WITH CHECK ADD CONSTRAINT [FK_Test_Detail_Test_Master] FOREIGN KEY([MasterID]) REFERENCES [Test_Master] ([MasterID]) GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Test_Detail_Test_Referenced]') AND parent_object_id = OBJECT_ID(N'[dbo].[Test_Detail]')) ALTER TABLE [dbo].[Test_Detail] WITH CHECK ADD CONSTRAINT [FK_Test_Detail_Test_Referenced] FOREIGN KEY([ReferencedID]) REFERENCES [Test_Referenced] ([ReferencedID])

Please check if the field MasterID of the entity TestMaster is IDENTITY at LLBLGenPro Designer. What Database (and version) are you using?

What LLBLGen version and RuntimeLibraries version are you using?

Hi David,

Sorry for the missing information, my mistake:

LLBLGen version: 2.5 final, December 5th 2007

DBMS version: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

All PKs are identity and all correctly set by LLBLGen default: identity checkboxes are checked and using SCOPE_IDENTITY.

If it speeds up the your reproducing process I can zip all sources, .lgp, and the denerated sources too and send as attachment here or email.

As I debugged th ORM support classes somewere the recursive pending object graph visiting I saw, that the already deleted Master's Detail entites (which are never saved) are still in the pending entity list.

I think that the two commented diagnostic experiment in source has something to do to with the resolution, but I am not sure.

The first is the A) version B) version area. Unfortunatelly I can no isolate this, in the demo program both version sends phantom inserts, but in our real program only the A) version shows this feature.

The second commentes diagnostic result is the SaveEntity in the middle. If I execute that, then no error occurs.

thx for answers: horo

// A) version: Last line save will throw detail.TestReferenced = referenced;

// B) version: // Last line save works correctly in my real program, but here can not isolate this // behaviour. Here this B) version also throws the same exception than A) version

Please post the exceptions text and the stack trace.

If it speeds up the your reproducing process I can zip all sources, .lgp, and the denerated sources too and send as attachment here or email.

yeah sure this Would spead up things, and if you can create a simple repro over Northwind it would be much better too

Thanks

Hi Walaa,

Exception text was included as a comment (last lines thow te exception not the quoted ones:

// This save will throw: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Test_Detail_Test_Master". The conflict occurred in database "Test", table "dbo.Test_Master", column 'MasterID'.

I attach the complete isolated solution. The test MS database creation is jut 60 sec, create a db named Test, and run the 3 table creator script (CreateTablesAndConstraints.sql included in rar).

thx: horo

The problem is here:

detail.TestReferenced = reference;

When you do that a new TestDetailEntity is added to the reference.TestDetails collection, then you reinstantite the detail object but the new-unsaved DetailEntity remains at reference.TestDetails collection. So, when you save recirsively the save routine examines all the graph and a new-unsaved entity is found, that entity contains an inexistent MasterId reference (you deleted it a couple lines before). That's why the FK reference error.

So, use the object reference with care . Replace above line with this at all TestReferenced assignements:

detail.ReferencedId = reference.ReferencedId;

Hi David

Thx for answer.

My original posted commented source contains a commented B) version section, which is exactly the same what you mention as the solution of problem.

detail.ReferencedId = referenced.ReferencedId

Unfortunately this B) version shows the same exception than A) version.

PS: I hope the "reinstantiate" you wrote means only "keeps a reference" because reinstantiate would be a strange behaviour.

thx: horo

Just for the sake of trial, would you please try the following:

TestMasterEntity master = new TestMasterEntity();
master.MasterData = "-";

TestDetailEntity detail = new TestDetailEntity();
detail.DetailData = "-";

detail.TestReferenced = referenced;
detail.TestMaster = master;

using (IDataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.SaveEntity(master, true, true);
}

That code works. (Iput before the creation of referenced entity, becausi it uses it.)

        TestReferencedEntity referenced = new TestReferencedEntity();
        referenced.ReferencedData = "-";
        using (var adapter = new DataAccessAdapter())
        {
            adapter.SaveEntity(referenced, true, true);
        }


        TestMasterEntity master = new TestMasterEntity();
        master.MasterData = "-";

        TestDetailEntity detail = new TestDetailEntity();
        detail.DetailData = "-";

        detail.TestReferenced = referenced;
        detail.TestMaster = master;

        using (IDataAccessAdapter adapter = new DataAccessAdapter())
        {
            adapter.SaveEntity(master, true, true);
        }

Hi,

Sorry for the lame question, but why was this thread signed as done?

I think some misunderstandings are here.

I thougth that Walaa ask some diagnostics test run. That code runs but it does the half of the isolated erratic demo code what I posted originally.

The answer / or workaround / or bugfix is still pending.

thx for answers

That code works. (Iput before the creation of referenced entity, becausi it uses it.)

Your remark is not clear, would you please re-phrase it with more elaboration.

Also we found it hard to follow your posted code, as to which part is working and which is not, and these part executed simultaneously, or we have to comment one of them and test the other.

Hi Walaa,

Here is a bit more simple demo code. Sorry for the complexity, but I spent more than 8 hours at sunday to find the bug in our 10000+ lines application and isolate it.

Also, I have already posted the isolated solution in .rar format, with the 60 sec sql script which creates the 3 tables.

The next code will throw an exception as the last line comment shows:

// This is only a clean up to enable the code run more than once using (IDataAccessAdapter adapter = new DataAccessAdapter()) { adapter.DeleteEntitiesDirectly(typeof(TestDetailEntity), null); adapter.DeleteEntitiesDirectly(typeof(TestReferencedEntity), null); adapter.DeleteEntitiesDirectly(typeof(TestMasterEntity), null); }

// Create entity and persist: TestReferencedEntity referenced = new TestReferencedEntity(); referenced.ReferencedData = "-"; using (var adapter = new DataAccessAdapter()) { adapter.SaveEntity(referenced, true, true); }

//---------------------------------------

//Test scenario: // Step 1: Create master and detail, and persist:

// Create master TestMasterEntity master = new TestMasterEntity(); master.MasterData = "-";

// Create detail and reference to an existing entity:

TestDetailEntity detail = new TestDetailEntity(); detail.DetailData = "-"; detail.TestReferenced = referenced; master.TestDetail.Add(detail);

// Persist master and detail using (IDataAccessAdapter adapter = new DataAccessAdapter()) { adapter.SaveEntity(master, true, true); }

// Step 2: // Create other detail for the exising master, but this will not be saved for example som user indicated reason:

detail = new TestDetailEntity(); detail.DetailData = "-"; detail.ReferencedId = referenced.ReferencedId;

master.TestDetail.Add(detail);

// Step 3 // Delete using (IDataAccessAdapter adapter = new DataAccessAdapter()) { adapter.DeleteEntitiesDirectly(typeof(TestDetailEntity), null); adapter.DeleteEntitiesDirectly(typeof(TestMasterEntity), null); }

// Step 4 // Create new master and detail master = new TestMasterEntity(); master.MasterData = "-";

detail = new TestDetailEntity(); detail.DetailData = "-"; detail.TestReferenced = referenced; master.TestDetail.Add(detail);

// Step 5: The exception

// This save will throw: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Test_Detail_Test_Master". The conflict occurred in database "Test", table "dbo.Test_Master", column 'MasterID'."

using (IDataAccessAdapter adapter = new DataAccessAdapter()) { adapter.SaveEntity(master, true, true); }

I hope this helps. thx:horo

Also, I have already posted the isolated solution in .rar format, with the 60 sec sql script which creates the 3 tables

Unfortunatly Icouldn't open it since I don't have VS 2008 installed yet. Also other support members reported that the console application throws me the following error: .....Microsoft.CSharp.Targets was not found......

Anyway just for the sake of trial would you please try the following:

Trial #1: Just before the save. check the following:

// Step 5:
int detailsCount = master.TestDetail.Count;

Trail #2: modify step 4 code as follows:

// Step 4
// Create new master and detail
TestMasterEntity master2 = new TestMasterEntity();
master2.MasterData = "-";

TestDetailEntity detail2 = new TestDetailEntity();
detail2.DetailData = "-";
detail2.TestReferenced = referenced;
master2.TestDetail.Add(detail2);

int masterId = detail2.MasterID;

Hi Walaa,

I did the trials what you asked the results are below. Also I repackaged the solution in VS 2005 projects and solution and attached it.

Trial #1: detailsCount is 1 (as expected). Monitoring with SQL Server Profiler 2 INSERTS sended against the TestDetail table, the second fails and causes exception.

Trial #2: Shows the same exception. Exception details below.

Please try to reproduce it with the attached source.

I hope this helps.

thx: horo

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled Message="An exception was caught during the execution of an action query: The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_Test_Detail_Test_Master\". The conflict occurred in database \"Test\", table \"dbo.Test_Master\", column 'MasterID'.\r\nThe statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception." Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20" RuntimeBuild="01092008" RuntimeVersion="2.5.0.0" QueryExecuted="\r\n\tQuery: INSERT INTO [Test].[dbo].[Test_Detail] ([MasterID], [DetailData], [ReferencedID]) VALUES (@MasterId, @DetailData, @ReferencedId);SELECT @DetailId=SCOPE_IDENTITY()\r\n\tParameter: @DetailId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: <undefined value>.\r\n\tParameter: @MasterId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 24.\r\n\tParameter: @DetailData : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: \"-\".\r\n\tParameter: @ReferencedId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.\r\n" StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() at SD.LLBLGen.Pro.ORMSupportClasses.BatchActionQuery.Execute() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(List`1 queueToPersist, Boolean insertActions, Int32& totalAmountSaved) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, Boolean recurse) at ConsoleTest.Program.Main(String[] args) in T:\tmp\Isolated\ConsoleTest\Program.cs:line 104 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException: System.Data.SqlClient.SqlException Message="The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_Test_Detail_Test_Master\". The conflict occurred in database \"Test\", table \"dbo.Test_Master\", column 'MasterID'.\r\nThe statement has been terminated." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=16 LineNumber=1 Number=547 Procedure="" Server="localhost" State=0 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()

Looking into it.

Your test code isn't very clear. For example you create THREE detail entities, though the 3rd you call 'detail2'. Also, you all give them '-' as value. If you give the datavalue field a value which represents the entity its' in e.g. Detail_2, it's more clear.

I can reproduce the error, which occurs when saving:

            // Step 2:
            // Create other detail for the exising master, but this will not be saved for example som user indicated reason:

            detail = new TestDetailEntity();
            detail.DetailData = "Detail_1_1";
            detail.ReferencedId = referenced.ReferencedId;

            master.TestDetail.Add(detail);

because:

Generated Sql query: 
    Query: INSERT INTO [TestDBLLBLGen].[dbo].[Test_Detail] ([MasterID], [DetailData], [ReferencedID])  VALUES (@MasterId, @DetailData, @ReferencedId);SELECT @DetailId=SCOPE_IDENTITY()
    Parameter: @DetailId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: <undefined value>.
    Parameter: @MasterId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3.
    Parameter: @DetailData : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "Detail_1_1".
    Parameter: @ReferencedId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.


Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
TestCase 'M:ConsoleTest.Program.Main(System.String[])' failed: An exception was caught during the execution of an action query: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Test_Detail_Test_Master'. The conflict occurred in database 'TestDBLLBLGen', table 'Test_Master', column 'MasterID'.
The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
    SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of an action query: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Test_Detail_Test_Master'. The conflict occurred in database 'TestDBLLBLGen', table 'Test_Master', column 'MasterID'.
    The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Test_Detail_Test_Master'. The conflict occurred in database 'TestDBLLBLGen', table 'Test_Master', column 'MasterID'.
    The statement has been terminated.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

The reason for this is the following: Line A in the snippet:

// Step 2:
// Create other detail for the exising master, but this will not be saved for example som user indicated reason:
detail = new TestDetailEntity();
detail.DetailData = "Detail_1_1";
detail.ReferencedId = referenced.ReferencedId;

master.TestDetail.Add(detail);   // <<<<<<<<<<<< LINE A

adds 'detail' to master.TestDetail. It ALSO sets 'detail.TestMaster' to 'master' and syncs detail.MasterId with master.MasterId, which has a valid value.

Now, after this block, you remove 'master''s data from the database. And how is this 'detail' instance suddenly part of the graph that it gets inserted? Via 'referenced'. master references via master.TestDetail the first 'detail' instance (referenced in step 1). And this one references 'referenced'.

As these references are 2-ways as I explained above, the code in step 4, at line 91, ties these entities to the new entities to insert: there you reference 'referenced' via detail2. Which means all entities referenced by 'reference' are now reachable.

To avoid this, clear master.TestDetail. Though I find the code a bit obscure: removing data from the db directly while the entities are in scope is a bit problematic as the entity instances in-memory have no notion that they're removed from the db.

Hi Otis,

Thx for the "code review" :-) (variable names etc). Actually llblgen support (Wallaa) was who asked me to rename detail to detail2... :-) to make a trial. Original code was as you expected.

Anyway, thank you for your explanation on object graph and references.

The original bug was a lot more complex and object graph was also more complex (7 level) I looked for dispose or similar when I realized that the problem is to do something with object graph. When I not found, I tried something less exact, set references to null and force a GC.

I think the problem is not the direct delete. Without direct delete the code still will try to save the 2nd detail entity, and it will be saved "silently", however the programmer thinks that he relesed all gc references the old master, and refetched a new one in a new instance.

I am not sure, but would not be imlpementing IDisposable on entities a safe solution for this scenarios? I mean dispose implementation will remove the instance from the graph.

The problem is that currently the programmer has no tool to explicitly drop (dispose) an entity.

thx horo

horo wrote:

Hi Otis,

Thx for the "code review" :-) (variable names etc). Actually llblgen support (Wallaa) was who asked me to rename detail to detail2... :-) to make a trial. Original code was as you expected.

I didn't mean to lecture .

Anyway, thank you for your explanation on object graph and references.

The original bug was a lot more complex and object graph was also more complex (7 level) I looked for dispose or similar when I realized that the problem is to do something with object graph. When I not found, I tried something less exact, set references to null and force a GC.

I think the problem is not the direct delete. Without direct delete the code still will try to save the 2nd detail entity, and it will be saved "silently", however the programmer thinks that he relesed all gc references the old master, and refetched a new one in a new instance.

I am not sure, but would not be imlpementing IDisposable on entities a safe solution for this scenarios? I mean dispose implementation will remove the instance from the graph.

The problem is that currently the programmer has no tool to explicitly drop (dispose) an entity.

thx horo

You know, that's a great suggestion. I'm not sure if 'Dispose' is a proper method for this, but perhaps 'DetachFromGraph' is, which then resets all references to other entities.

I'll add this to the todo list Thanks.

Hi Otis,

Cool. Thx for your time.

horo