asp.net 2.0 llbl 2.0 orm support 2.0.7.810 adapter

I have a project. with the core domain, web gui and unit tests. my unit and integration tests run without error. when i then run the web app and walk through my save process I get a duplicate PK exception when inserting a new user. (i get a different exception with exising users, but that's for another day.)

this is the code in question. it work with unit/integration test, but not the actual gui

public void Save(UserEntity user)
{
    using (IDataAccessAdapter adapter = gateway.Create())
    {
        UnitOfWork2 uow = new UnitOfWork2();
        uow.AddDeleteEntitiesDirectlyCall(EntityType.UserReportEntity.ToString(), new RelationPredicateBucket(UserReportFields.UserId == user.Id));
        uow.Commit(adapter, false);
        uow.AddForSave(user);
        uow.Commit(adapter, true);
    }
}

now if change the code to this all my tests pass and the gui works

public void Save(UserEntity user)
{
    using (IDataAccessAdapter adapter = gateway.Create())
    {
        try
        {
            adapter.StartTransaction(IsolationLevel.ReadCommitted, "SaveUser");
            adapter.DeleteEntitiesDirectly(EntityType.UserReportEntity.ToString(), new RelationPredicateBucket(UserReportFields.UserId == user.Id));
            adapter.SaveEntity(user, true, false);
            adapter.SaveEntityCollection(user.UserReport, true, false);
            foreach (UserReportEntity entity in user.UserReport)
            {
                adapter.SaveEntityCollection(entity.UserReportFormat, true, false);
                foreach (UserReportDelegateEntity reportDelegate in entity.Generator) adapter.SaveEntity(reportDelegate.User, true, false);
                adapter.SaveEntityCollection(entity.Generator, true, false);
            }
            adapter.Commit();
        }
        catch (ORMException)
        {
            adapter.Rollback();
            throw;
        }
    }
}

instead of letting the uow process the graph, I now save each layer with a seperate call to the adapter. not ideal but it's working.

here is the table structure:

--create tables
create table [User]( 
    [Id] varchar(20) not null,
    [DisplayName] varchar(100) not null,
    [EmailAddress] varchar(100) not null,
    constraint [User__PK] primary key clustered ([Id]),
    constraint [User_DisplayName__UC] unique ([DisplayName]),
    constraint [User_EmailAddress__UC] unique ([EmailAddress])
)
GO
create table [UserReport](
    [Id] int identity(1,1) not null, 
    [UserId] varchar(20) not null,
    [ReportId] int not null,
    constraint [UserReport__PK] primary key clustered ([Id]),
    constraint [UserReport_UserId_ReportId__UC] unique ([UserId], [ReportId])
)
GO
create table [UserReportFormat](
    [UserReportId] int not null, 
    [FormatId] int not null,
    constraint [UserReportFormat__PK] primary key clustered ([UserReportId], [FormatId])
)
GO
create table [UserReportDelegate]( 
    [UserReportId] int not null,
    [UserId] varchar(20) not null,
    constraint [UserReportDelegate__PK] primary key clustered ([UserReportId], [UserId])
)
GO
create table [Report]( 
    [Id] int identity(1, 1) not null,
    [UserDefinedId] char(5) not null,
    [Title] varchar(25) not null,
    [Description] varchar(256) not null,
    [LifeSpan] int not null,
    [CategoryId] int not null,
    constraint [Report__PK] primary key clustered ([Id]),
    constraint [Report_UserDefinedId__UC] unique ([UserDefinedId])
)
GO
create table [Category]( 
    [Id] int identity(1, 1) not null,
    [Description] varchar(50) not null,
    constraint [Category__PK] primary key clustered ([Id]),
    constraint [Category_Description__UC] unique ([Description])
)
GO
create table [Format]( 
    [Id] int identity(1, 1) not null,
    [ContentType] varchar(25) not null,
    [MimeType] varchar(25) not null,
    [Description] varchar(50) not null,
    constraint [Format__PK] primary key clustered ([Id])
)
GO
--foreign keys
alter table [UserReport] add 
    constraint [UserReport_User__FK] foreign key ([UserId]) references [User] ([Id]) on delete cascade,
    constraint [UserReport_Report__FK] foreign key ([ReportId]) references [Report] ([Id])
GO  
alter table [UserReportDelegate] add
    constraint [UserReportDelegate_UserReport__FK] foreign key ([UserReportId]) references [UserReport] ([Id]) on delete cascade,
    constraint [UserReportDelegate_User__FK] foreign key ([UserId]) references [User] ([Id]) on delete no action
GO
alter table [Report] add
    constraint [Report_Category__FK] foreign key ([CategoryId]) references [Category] ([Id])
GO
alter table [UserReportFormat] add
    constraint [UserReportFormat_UserReport__FK] foreign key ([UserReportId]) references [UserReport] ([Id]) on delete cascade,
    constraint [UserReportFormat_Format__FK] foreign key ([FormatId]) references [Format] ([Id])
go

users originate from AD, when a user is added to the system their id, name and email adress is copied into the db.

here is the domain model

UserEntity
    1:N UserReportEntity
           N:1 ReportEntity
                  N:1 CategoryEntity
           1:N UserReportFormatEntity
                  N:1 FormatEntity
           1:N UserReportDelegateEntity (aliased as Generator)
                  N:1 UserEntity

the biggest question mark is why the orginal code works for tests, but not the gui. any ideas why this may be the case.

In which table do you get the PK exception? Would you please post the exact exception error text?

public void Save(UserEntity user) { using (IDataAccessAdapter adapter = gateway.Create()) { UnitOfWork2 uow = new UnitOfWork2(); uow.AddDeleteEntitiesDirectlyCall(EntityType.UserReportEntity.ToString(), new RelationPredicateBucket(UserReportFields.UserId == user.Id)); uow.Commit(adapter, false); uow.AddForSave(user); uow.Commit(adapter, true); } }

The only thing that can make the following code behave differently if called from 2 different places (Unit tests & UI) is the User parameter passed in. I bet something is different between these calls, would you please post the Unti Test call code snippet and that of the UI?

Also in the first Commit, why don't you try: uow.Commit(adapter, true); using true rather than false for the second parameter.

In which table do you get the PK exception? User table

Would you please post the exact exception error text? An exception was caught durning the execution of an action quetry: Violation of Pimary Key contraint 'User__PK'. Cannot insert duplicate key in object 'dbo.User'.

I also tried the other variations you suggested. neither worked.

public void Save(UserEntity user)
{
    using (IDataAccessAdapter adapter = gateway.Create())
    {
        UnitOfWork2 uow = new UnitOfWork2();
        uow.AddDeleteEntitiesDirectlyCall(EntityType.UserReportEntity.ToString(), new RelationPredicateBucket(UserReportFields.UserId == user.Id));
        uow.Commit(adapter, true);
        uow.AddForSave(user);
        uow.Commit(adapter, true);
    }
}

public void Save(UserEntity user)
{
    using (IDataAccessAdapter adapter = gateway.Create())
    {
        UnitOfWork2 uow = new UnitOfWork2();
        uow.AddDeleteEntitiesDirectlyCall(EntityType.UserReportEntity.ToString(), new RelationPredicateBucket(UserReportFields.UserId == user.Id));
        uow.Commit(adapter);
        uow.AddForSave(user);
        uow.Commit(adapter, true);
    }
}

public void Save(UserEntity user)
{
    using (IDataAccessAdapter adapter = gateway.Create())
    {
        UnitOfWork2 uow = new UnitOfWork2();
        uow.AddDeleteEntitiesDirectlyCall(EntityType.UserReportEntity.ToString(), new RelationPredicateBucket(UserReportFields.UserId == user.Id));
        uow.AddForSave(user);
        uow.Commit(adapter, true);
    }
}

I am running my integration tests and manual gui tests with the same user information. for some reason it appears the uow is trying to save the graph twice. but this only occurs via the web gui, not the console app which runs tests.

and it's not a simple as hitting the break point twice. it all happens within the uow.

the save button calls the presenters save function, which validates the dto and passes it along to the service layer. the service layer translates the dto to my llbl doman and calls this function on the user repository object. view -(dto)-> presenter -(validate dto)-> task -(convert to domain)-> repository -(save to db)

I run an integration test from the presenter all the way to the db which passes.

Might be that the view(dto) is passing an already existing entity with the IsNew flag. Is the PK an Identity column? or do you set it in code?

in the Save(UserEntity user) method would you please check for the PK value and the user.IsNew value and see if you get the same values when called from the UI and from the Test routine?

the User.Id is the PK. this value is a varchar(20) which is assigned by AD. The user id is not present in the db when calling the Save function. IsNew = true.

I changed the user name/displayname/email to dummy data.

Trace Output

Method Enter: UnitOfWork2.Commit(2)
Method Enter: DataAccessAdapterBase.StartTransaction
:   Transaction name: UoWCommit. Isolation level: ReadCommitted.
Method Enter: DataAccessAdapterBase.OpenConnection
: New connection created.
: Connection physically opened.
Method Exit: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.StartTransaction
:   Handling Pre Insert CallBacks.
:   Handle Inserts.
Method Enter: DataAccessAdapterBase.PersistQueue
Persistence action info: Action: Insert. Queue length: 0
PersistQueue method result: queuePersisted result: True
Method Exit: DataAccessAdapterBase.PersistQueue
:   Handling Pre Update CallBacks.
:   Handle Updates.
Method Enter: DataAccessAdapterBase.PersistQueue
Persistence action info: Action: Update. Queue length: 0
PersistQueue method result: queuePersisted result: True
Method Exit: DataAccessAdapterBase.PersistQueue
:   Handling UpdateEntitiesDirectly Calls.
:   Handling Pre Delete CallBacks.
:   Handle Deletes.
:   Handling DeleteEntitiesDirectly Calls.
Method Enter: DataAccessAdapterBase.DeleteEntitiesDirectly
:   Deleting entities of type: UserReportEntity
Method Enter: DataAccessAdapterBase.ExecuteActionQuery
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Executed Sql Query: 
    Query: DELETE FROM [ReportingFrameworkMetaData].[dbo].[UserReport] WHERE ( ( [ReportingFrameworkMetaData].[dbo].[UserReport].[UserId] = @UserId1))
    Parameter: @UserId1 : AnsiString. Length: 20. Precision: 0. Scale: 0. Direction: Input. Value: "foo".


Method Exit: DataAccessAdapterBase.ExecuteActionQuery
Method Exit: DataAccessAdapterBase.DeleteEntitiesDirectly
:   Handling Post Delete CallBacks.
Method Enter: UnitOfWork2.Reset
Method Exit: UnitOfWork2.Reset
Method Exit: UnitOfWork2.Commit(2)
Method Enter: UnitOfWork2.Commit(2)
:   Transaction to use: UoWCommit. AutoCommit: True
Method Enter: DataAccessAdapterBase.DetermineActionQueues(7)
Active Entity Description: 
    Entity: ReportingFramework.EntityClasses.UserEntity. ObjectID: d6682269-bb00-4851-8a71-ac5560df9caa
    PrimaryKey field: Id. Type: System.String. Value: foo
Entity added to insert queue: 
    Entity: ReportingFramework.EntityClasses.UserEntity. ObjectID: d6682269-bb00-4851-8a71-ac5560df9caa
    PrimaryKey field: Id. Type: System.String. Value: foo
Entity added to insert queue: 
    Entity: ReportingFramework.EntityClasses.UserReportEntity. ObjectID: d12b4d95-2a9b-48d7-a31a-cbe766052e94
    PrimaryKey field: Id. Type: System.Int32. Value: <undefined value>
Entity added to insert queue: 
    Entity: ReportingFramework.EntityClasses.UserEntity. ObjectID: 29baff4a-dce6-4a40-ba68-7edccf3edf2b
    PrimaryKey field: Id. Type: System.String. Value: foo
Entity added to insert queue: 
    Entity: ReportingFramework.EntityClasses.UserReportEntity. ObjectID: 32f0605a-914a-48bf-8e07-a284677bfe1d
    PrimaryKey field: Id. Type: System.Int32. Value: <undefined value>
Entity added to insert queue: 
    Entity: ReportingFramework.EntityClasses.UserReportFormatEntity. ObjectID: 661fd1ae-a4b7-4b02-b8f1-a08e6de06d8e
    PrimaryKey field: UserReportId. Type: System.Int32. Value: <undefined value>
    PrimaryKey field: FormatId. Type: System.Int32. Value: 1
Method Exit: DataAccessAdapterBase.DetermineActionQueues(7)
:   Handling Pre Insert CallBacks.
:   Handle Inserts.
Method Enter: DataAccessAdapterBase.PersistQueue
Persistence action info: Action: Insert. Queue length: 5
Current persisted entity info: 
    Entity: ReportingFramework.EntityClasses.UserEntity. ObjectID: d6682269-bb00-4851-8a71-ac5560df9caa
    PrimaryKey field: Id. Type: System.String. Value: foo
Method Enter: DataAccessAdapterBase.ExecuteActionQuery
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Enter: Query.ReflectOutputValuesInRelatedFields
Method Exit: Query.ReflectOutputValuesInRelatedFields: no parameter relations.
Executed Sql Query: 
    Query: INSERT INTO [ReportingFrameworkMetaData].[dbo].[User] ([Id], [DisplayName], [EmailAddress])  VALUES (@Id, @DisplayName, @EmailAddress)
    Parameter: @Id : AnsiString. Length: 20. Precision: 0. Scale: 0. Direction: Input. Value: "foo".
    Parameter: @DisplayName : AnsiString. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: "Foo Bar".
    Parameter: @EmailAddress : AnsiString. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: "user@domain.com".


Method Exit: DataAccessAdapterBase.ExecuteActionQuery
Method Enter: EntityBase2.SyncFKFields
Active Entity Description: 
    Entity: ReportingFramework.EntityClasses.UserReportEntity. ObjectID: d12b4d95-2a9b-48d7-a31a-cbe766052e94
    PrimaryKey field: Id. Type: System.Int32. Value: <undefined value>
Data Supplying Entity Description: 
    Entity: ReportingFramework.EntityClasses.UserEntity. ObjectID: d12b4d95-2a9b-48d7-a31a-cbe766052e94
    PrimaryKey field: Id. Type: System.String. Value: foo
:   Syncing FK field UserId with PK field Id
Method Exit: EntityBase2.SyncFKFields
Current persisted entity info: 
    Entity: ReportingFramework.EntityClasses.UserReportEntity. ObjectID: d12b4d95-2a9b-48d7-a31a-cbe766052e94
    PrimaryKey field: Id. Type: System.Int32. Value: <undefined value>
Method Enter: DataAccessAdapterBase.ExecuteActionQuery
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Enter: Query.ReflectOutputValuesInRelatedFields
:   Syncing field Id with parameter @Id.
Method Exit: Query.ReflectOutputValuesInRelatedFields
Executed Sql Query: 
    Query: INSERT INTO [ReportingFrameworkMetaData].[dbo].[UserReport] ([UserId], [ReportId])  VALUES (@UserId, @ReportId);SELECT @Id=SCOPE_IDENTITY()
    Parameter: @Id : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: 19.
    Parameter: @UserId : AnsiString. Length: 20. Precision: 0. Scale: 0. Direction: Input. Value: "foo".
    Parameter: @ReportId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3.

    
Method Exit: DataAccessAdapterBase.ExecuteActionQuery
Method Enter: EntityBase2.SyncFKFields
Active Entity Description: 
    Entity: ReportingFramework.EntityClasses.UserReportFormatEntity. ObjectID: 661fd1ae-a4b7-4b02-b8f1-a08e6de06d8e
    PrimaryKey field: UserReportId. Type: System.Int32. Value: <undefined value>
    PrimaryKey field: FormatId. Type: System.Int32. Value: 1
Data Supplying Entity Description: 
    Entity: ReportingFramework.EntityClasses.UserReportEntity. ObjectID: 661fd1ae-a4b7-4b02-b8f1-a08e6de06d8e
    PrimaryKey field: Id. Type: System.Int32. Value: 19
:   Syncing FK field UserReportId with PK field Id
Method Exit: EntityBase2.SyncFKFields
Current persisted entity info: 
    Entity: ReportingFramework.EntityClasses.UserEntity. ObjectID: 29baff4a-dce6-4a40-ba68-7edccf3edf2b
    PrimaryKey field: Id. Type: System.String. Value: foo
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

Exception An exception was caught during the execution of an action query: Violation of PRIMARY KEY constraint 'User__PK'. Cannot insert duplicate key in object 'dbo.User'. The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this 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(List`1 queueToPersist, Boolean insertActions)
   at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse, Boolean autoCommit)
   at ReportingFramework.Repositories.UserRepository.Save(UserEntity user) in C:\JMeckley\My Documents\Visual Studio 2005\Projects\ReportingFramework\ReportingFramework\Repositories\UserRepository.cs:line 73
   at ReportingFramework.Repositories.CacheUserRepository.Save(UserEntity user) in C:\JMeckley\My Documents\Visual Studio 2005\Projects\ReportingFramework\ReportingFramework\Repositories\CacheUserRepository.cs:line 34
   at ReportingFramework.Tasks.ConfigureReportsTask.Save(UserSecuritySettingsDTO securitySettings) in C:\JMeckley\My Documents\Visual Studio 2005\Projects\ReportingFramework\ReportingFramework\Tasks\ConfigureReportsTask.cs:line 71
   at ReportingFramework.Presentation.Administration.ConfigureReportsPresenter.Save(UserSecuritySettingsDTO securitySettings) in C:\JMeckley\My Documents\Visual Studio 2005\Projects\ReportingFramework\ReportingFramework\Presentation\Administration\ConfigureReportsPresenter.cs:line 32
   at ReportingFramework.GUI.Administration.ConfigureReports.SaveButton_Click(Object sender, EventArgs e) in C:\JMeckley\My Documents\Visual Studio 2005\Projects\ReportingFramework\ReportingFramework.GUI\Administration\ConfigureReports.aspx.cs:line 77
   at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
   at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
   at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

I only see one INSERT statement being generated for the User table. So most probably you have an existing record with the "foo" value.

Would you please try to run that Insert statement against the database?

Hint: To let the UoW process the entire graph, set the recurse parameter to true when adding the root entity to the UoW

uow.AddForSave(user, true);

inserting directly against the db works. tried all the variations of uow.AddForSave() and uow.Commit(). all produced the same error.

the problem seems to be recursing through the graph. If I turn off recursion, and add each element individually to the uow, i can save without an error.

public void Save(UserEntity user)
        {
            using (IDataAccessAdapter adapter = gateway.Create())
            {
                UnitOfWork2 uow = new UnitOfWork2();
                uow.AddDeleteEntitiesDirectlyCall(EntityType.UserReportEntity.ToString(), new RelationPredicateBucket(UserReportFields.UserId == user.Id));
                uow.Commit(adapter, false);
                uow.AddForSave(user, null, true, false);
                uow.AddCollectionForSave(user.UserReport, true, false);
                foreach (UserReportEntity securitySetting in user.UserReport)
                {
                    uow.AddCollectionForSave(securitySetting.UserReportFormat, true, false);
                    foreach (UserReportDelegateEntity reportDelegate in securitySetting.Generator) uow.AddForSave(reportDelegate.User, null, true, false);
                    uow.AddCollectionForSave(securitySetting.Generator, true, false);
                }
                uow.Commit(adapter, true);
            }
        }

But why there was only one Insert statement to the User table shown in the trace posted above?

I have no idea.

In working on other parts of my system today, I think the problem is the PK is defined by me and not the db (auto increment). I have another routine which saves a ReportBatchEntity and all it's EntityCollection<ReportInstance>. with a simple:

UnitOfWork2 uow = new UnitOfWork2();
uow.AddForSave(batch);
using(IDataAccessAdapter gateway = factory.Create()) uow.Commit(gateway, true);

if I have time I will refactor the user table to have a PK of an auto-increment and UC for the Id.

If you want, you may attach a repro solution so we can debug it out.