Hello, I actually found this while chasing a different bug in my application which casued this behaviour, and i think only on a very rare case this would be an issue (it isn't in my project), but still to let you know about this.

LLBLGen Pro 2.5 version: September 24th 2007: Reproduced on SQL Server Express, after a similiar case on Oracle (but not a DB issue i think)

I have two tables with a 1:1 relation between them: Table "User": UserID (PK, Identity column) UserName (nullable)

Table "UserExtension" UserID (PK, FK->User) SomeField (Nullable)

I generate the classes for this two, adapter mode for c# .net 2.0, and add a test project with this code, to create the user entity, and a related userExtension entity, and save them recursively:

            UserEntity user = new UserEntity();
            UserExtensionEntity userExt = new UserExtensionEntity();
            userExt.SomeField = "SomeFieldValue";
            user.UserName = "SomeOne";
            user.UserExtension = userExt;

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

Which runs fine. However, when neglecting to set a field value on the User Entity:

            UserEntity user = new UserEntity();
            UserExtensionEntity userExt = new UserExtensionEntity();
            userExt.SomeField = "SomeFieldValue";
            //don't set the user Name
            //user.UserName = "SomeOne";
            user.UserExtension = userExt;

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

An exception is thrown from the database: "An exception was caught during the execution of an action query: Cannot insert the value NULL into column 'UserID', table 'TestORM.dbo.UserExtension'; column does not allow nulls. INSERT fails."

The executed sql: Query: INSERT INTO [TestORM].[dbo].[UserExtension] ([SomeField]) VALUES (@SomeField) Parameter: @SomeField : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: \"SomeFieldValue\".\r\n"

When tracing it, i see that the first query, for inserting the UserEntity, Prior to inserting the UserExtensionEntity, isn't executed. I think this is because the IsDirty=false for this entity, and the IsNew=true. However, since it's new, the related new entity is expecting it to be saved first.

Anyway, i think it's a rare db design when you want to create a record with no values from the client, but it is a valid one (mostly in inheritence models). I don't know if the same thing happens with many to one relations, but i guess it is.

The workaround for this is simply setting user.IsDirty = true, but i'm not sure why IsNew isn't getting a precedence over IsDirty, or some other exception isn't thrown before trying to save the related entity, which has no "parent" saved yet.

Thanks, Amitay

By design, an entity with no changed fields won't be saved in the database (and this applies for a new entity with no fields being set). Unless you have instructed it to do so by manually setting the appropriate flags.

I'm not sure if this would solve your problem, but in your case I suggest you define a Target per Entity Hierarchy for the User & UserExtension entity.

Hi Walaa, Thanks for your prompt reply, as always. As i said, i found a way to resolve this, either by setting one of the fields or setting the IsDirty Flag to true. However, i'm not sure what is the reason by design new entities aren't saved. This is causing a strange behaviour of trying to save the sub entity, when the parent entity is yet to be saved. It seems logical to me that a new entity when saved, would be saved anyway, or at the least that LLBLGen wouldn't try to save the child entity recursively.

But then again, it's not a crucial issue at all for me, just an abnormal behaviour to be aware of, that i thought you would want to consider, or other users may find useful information.

Thanks again, Amitay

AmitayD wrote:

Hi Walaa, Thanks for your prompt reply, as always. As i said, i found a way to resolve this, either by setting one of the fields or setting the IsDirty Flag to true. However, i'm not sure what is the reason by design new entities aren't saved. This is causing a strange behaviour of trying to save the sub entity, when the parent entity is yet to be saved. It seems logical to me that a new entity when saved, would be saved anyway, or at the least that LLBLGen wouldn't try to save the child entity recursively.

But then again, it's not a crucial issue at all for me, just an abnormal behaviour to be aware of, that i thought you would want to consider, or other users may find useful information.

Thanks again, Amitay

There's a difference between a new entity without ANY values set AND no sequence/identity field vs. a new entity which DOES have values set and / or an identity field PK.

In the first situation: the entity isn't saved as there's nothing to save. If it would be saved, you'll get empty rows in your DB which you don't want (if the insert statement will succeed anyway), also known as 'phantom inserts', inserts which happen without you realizing they can happen.

In the second situation, the entity IS saved.

An entity is also saved IF the entity has pending FK syncs, so no fields set, no PK field set no Sequence/identity pk but just FK syncs pending with entities also to be saved which will be saved. This goes wrong, IF the PK side the empty entity depends on isn't saved at the last minute (i.e. it's been thrown out of the queue when it's handled). In general this doesn't happen, because having a PK side entity which is empty AND not having a sequenced PK isn't saveable at all anyway (there's no data).

So I'm not really sure why you want to save the entity when there's nothing to be saved, because frankly, there's no entity (the entity instance is the data, not the object!)

Edit. I now see your User entity does have an entity field. This is strange, as saving an entity which is empty but does have an identity field should succeed on sqlserver. Is the identity just known in the DB or also in the entity/designer ?

Followup: Could you enable tracing and just save the User entity, so not recursive? Do you get a query like

INSERT INTO TestORM.dbo.User VALUES ()

?

I see that there's no code which pushes back the inserted identity value appended to this special query. This special query is emitted to be able to save empty entities which just an identity field. (which is pretty bogus)

Because the inserted identity isn't returned, the related entity isn't saveable as the FK field isn't updated.

Hi Frank, I thought you might find this issue intriguing... I've verified that the Identity column is known in the designer and PersistenceInfoProvider.cs.

I tried running the following code (non recursive save, with no values, on an entity with an identity column):

            UserEntity user = new UserEntity();
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.SaveEntity(user, true, false);
            }

And i'm getting the following trace: Method Enter: DataAccessAdapterBase.SaveEntity(4) Active Entity Description: Entity: Tests.TestOneToOneRelation.EntityClasses.UserEntity. ObjectID: 8527690b-d8a3-42ca-8922-2637bdaec053 PrimaryKey field: UserId. Type: System.Int32. Value: <undefined value> Method Exit: DataAccessAdapterBase.SaveEntity(4): Entity is not dirty and not saved recursively.

When trying it with the recursion and the child entity:

            
            UserEntity user = new UserEntity();
            UserExtensionEntity userExt = new UserExtensionEntity();
            userExt.SomeField = "SomeFieldValue";
            user.UserExtension = userExt;

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.SaveEntity(user, false, true);
            }

Trace (it seems like the first attempted INSERT is for the UserExtensionEntity, which causes an exception, so no, no parameter-less special query there):

Method Enter: EntityBase2.SyncFKFields

Active Entity Description:

Entity: Tests.TestOneToOneRelation.EntityClasses.UserExtensionEntity. ObjectID: 11d3e2a0-5398-4183-a335-7a227bb89e64

PrimaryKey field: UserId. Type: System.Int32. Value: <undefined value>

Data Supplying Entity Description:

Entity: Tests.TestOneToOneRelation.EntityClasses.UserEntity. ObjectID: 11d3e2a0-5398-4183-a335-7a227bb89e64

PrimaryKey field: UserId. Type: System.Int32. Value: <undefined value>

Method Exit: EntityBase2.SyncFKFields

'TestConsole.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'TestConsole.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

Method Enter: DataAccessAdapterBase.SaveEntity(4)

Active Entity Description:

Entity: Tests.TestOneToOneRelation.EntityClasses.UserEntity. ObjectID: 2d7832b5-8fb4-4df6-878f-26888be5f88d

PrimaryKey field: UserId. Type: System.Int32. Value: <undefined value>

Method Enter: DataAccessAdapterBase.DetermineActionQueues(7)

Active Entity Description:

Entity: Tests.TestOneToOneRelation.EntityClasses.UserEntity. ObjectID: 2d7832b5-8fb4-4df6-878f-26888be5f88d

PrimaryKey field: UserId. Type: System.Int32. Value: <undefined value>

Entity added to insert queue:

Entity: Tests.TestOneToOneRelation.EntityClasses.UserExtensionEntity. ObjectID: 11d3e2a0-5398-4183-a335-7a227bb89e64

PrimaryKey field: UserId. Type: System.Int32. Value: <undefined value>

Method Exit: DataAccessAdapterBase.DetermineActionQueues(7)

Method Enter: DataAccessAdapterBase.StartTransaction

Transaction name: RecursiveSave. Isolation level: ReadCommitted. Method Enter: DataAccessAdapterBase.OpenConnection

New connection created.

Connection physically opened. Method Exit: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.StartTransaction Method Enter: DataAccessAdapterBase.PersistQueue Persistence action info: Action: Insert. Queue length: 1 Current persisted entity info: Entity: Tests.TestOneToOneRelation.EntityClasses.UserExtensionEntity. ObjectID: 11d3e2a0-5398-4183-a335-7a227bb89e64 PrimaryKey field: UserId. Type: System.Int32. Value: <undefined value> 'TestConsole.vshost.exe' (Managed): Loaded 'C:\Documents and Settings\amitay\My Documents\LLBLGen Pro Projects\TestOneToOneRelation\TestConsole\bin\Debug\SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll' Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO [TestORM].[dbo].[UserExtension] ([SomeField]) VALUES (@SomeField) Parameter: @SomeField : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "SomeFieldValue".

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ Method Enter: DataAccessAdapterBase.ExecuteActionQuery Method Enter: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.ExecuteActionQuery Method Enter: DataAccessAdapterBase.Rollback Method Enter: DataAccessAdapterBase.Reset Method Exit: DataAccessAdapterBase.Reset Method Exit: DataAccessAdapterBase.Rollback A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/ 2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>TestConsole.vshost.exe</AppDomain><Exception> <ExceptionType>SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException, SD.LLBLGen.Pro.ORMSupportClasses.NET20, Version=2.5.0.0, Culture=neutral, PublicKeyToken=ca73b74ba4e3ff27</ExceptionType><Message>An exception was caught during the execution of an action query: Cannot insert the value NULL into column 'UserID', table 'TestORM.dbo.UserExtension'; column does not allow nulls. INSERT fails. The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.</Message><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(List1 queueToPersist, Boolean insertActions, Int32&amp;amp; 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 TestConsole.Program.Test() in C:\Documents and Settings\amitay\My Documents\LLBLGen Pro Projects\TestOneToOneRelation\TestConsole\Program.cs:line 26 at TestConsole.Program.Main(String[] args) in C:\Documents and Settings\amitay\My Documents\LLBLGen Pro Projects\TestOneToOneRelation\TestConsole\Program.cs:line 33 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()</StackTrace><ExceptionString> SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of an action query: Cannot insert the value NULL into column 'UserID', table 'TestORM.dbo.UserExtension'; column does not allow nulls. INSERT fails. The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---&amp;gt; System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'UserID', table 'TestORM.dbo.UserExtension'; column does not allow nulls. INSERT fails. 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) 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() --- End of inner exception stack trace --- 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(List1 queueToPersist, Boolean insertActions, Int32&amp; 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 TestConsole.Program.Test() in C:\Documents and Settings\amitay\My Documents\LLBLGen Pro Projects\TestOneToOneRelation\TestConsole\Program.cs:line 26 at TestConsole.Program.Main(String[] args) in C:\Documents and Settings\amitay\My Documents\LLBLGen Pro Projects\TestOneToOneRelation\TestConsole\Program.cs:line 33 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()</ExceptionString><InnerException> <ExceptionType>System.Data.SqlClient.SqlException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Cannot insert the value NULL into column 'UserID', table 'TestORM.dbo.UserExtension'; column does not allow nulls. INSERT fails. The statement has been terminated.</Message><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()</StackTrace> <ExceptionString>System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'UserID', table 'TestORM.dbo.UserExtension'; column does not allow nulls. INSERT fails. 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) 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()</ExceptionString> <DataItems><Data><Key>HelpLink.ProdName</Key><Value>Microsoft SQL Server 'TestConsole.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.DebuggerVisualizers\ 9.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.DebuggerVisualizers.dll' 'TestConsole.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Design\2.0.0.0__b03f5f7f11d50a3a\System.Design.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled. Method Enter: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.SaveEntity(4)

I've noticed that code was run with refetchAfterSave=false, on the second run/trace, but i've checked and it's the same behaviour up to the exception part (didn't want to litter the thread with more trace logs...)

In UtilityClasses.cs -> ObjectGraphUtils.DetermineActionQueues, there's the problem. At line 1635, the check is performed if the entity is necessary to save. The check doesn't take into account an empty entity with an identity field.

The problem is: it doesn't know the information if the field is an identity field, because that info is db specific and not stored in the entity info, which is the info the code works with. So it can't decide at that point if an entity is saveble because of the identity flag, as it doesn't have that info. I.o.w.: it can only look at the entity itself and it sees an empty entity so it doesn't accept that entity.