primary key column being updated in database

Posts   
 
    
ww
User
Posts: 83
Joined: 01-Oct-2004
# Posted on: 28-Apr-2009 03:55:49   

I'm using llblgen pro 2.6 final, Adapter, with SQL Server 2005.

I'm seeing this behavior: when I update an entity, llblgen is updating the primary key column when it generates its update statement (confirmed by turning on logging), even though the primary key hasn't changed.

This is causing problems in some places with foreign keys--SQL Server thinks the PK column is being changed, even though the same value is being written back to it, and in the places where I don't have CASCADE on the foreign key, I'm getting key constraint errors.

Is this a bug, for llblgen to be issuing updates to the PK?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 28-Apr-2009 05:42:38   

Please post the code snippet that reproduces this behavior, and the generated sql.

David Elizondo | LLBLGen Support Team
ww
User
Posts: 83
Joined: 01-Oct-2004
# Posted on: 28-Apr-2009 16:33:21   

I have identified the problem:

My tables all use a Guid as the primary key. In InitClassEmpty I set this value to a new Guid, so that new objects will always have a guid. When I retrieve an object and change another field, llblgen is treating the PK as changed. I removed the guid initialization from InitClassEmpty and that solved the problem.

However, I'd like to be able to initialize keys like this so that I don't have to remember to initialize the value every time I create a new object.

I reproduced this in a simple project. I have a BaseObject and a DerivedObject that inherits from it, linked by the ObjectID column.

In the code, I just retrieved all BaseObjects and updated a field:


            DataAccessAdapter adapter = new DataAccessAdapter(true);
            UnitOfWork2 uow = new UnitOfWork2();
            EntityCollection<MyBaseObjectEntity> objects = new EntityCollection<MyBaseObjectEntity>(new MyBaseObjectEntityFactory());
            adapter.FetchEntityCollection(objects, null);
            foreach (MyBaseObjectEntity obj in objects)
            {
                obj.ValueB = DateTime.Now.ToString();
                uow.AddForSave(obj);
            }

            try
            {
                uow.Commit(adapter, true);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return;
            }

            adapter.CloseConnection();

Here's the trace:


Current persisted entity info: 
    Entity: TestProject.EntityClasses.MyDerivedObjectEntity. ObjectID: 29ce5c1f-ab0c-4090-9dfc-1decc82d138a
    PrimaryKey field: ObjectId. Type: System.Guid. Value: 74b1a17e-5d05-4c9f-af49-f1904e07fe52
    PrimaryKey field: ObjectId. Type: System.Guid. Value: 74b1a17e-5d05-4c9f-af49-f1904e07fe52
Method Enter: CreateUpdateDQ(4)
Method Enter: CreateSingleTargetUpdateDQ(4)
Generated Sql query: 
    Query: UPDATE [testdb].[dbo].[BaseObject] SET [ObjectID]=@ObjectId_BaseObjectEntity,[ValueB]=@ValueB WHERE ( [testdb].[dbo].[BaseObject].[ObjectID] = @ObjectId_BaseObjectEntity1)
    Parameter: @ObjectId_BaseObjectEntity : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 74b1a17e-5d05-4c9f-af49-f1904e07fe52.
    Parameter: @ValueB : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "4/28/2009 10:25:47 AM".
    Parameter: @ObjectId_BaseObjectEntity1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8ffdfe3f-8cf4-4b54-8c38-069ed803b669.


Method Exit: CreateSingleTargetUpdateDQ(4)
Method Enter: CreateSingleTargetUpdateDQ(4)
Generated Sql query: 
    Query: UPDATE [testdb].[dbo].[DerivedObject] SET [ObjectID]=@ObjectId WHERE ( [testdb].[dbo].[DerivedObject].[ObjectID] = @ObjectId2)
    Parameter: @ObjectId : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 74b1a17e-5d05-4c9f-af49-f1904e07fe52.
    Parameter: @ObjectId2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8ffdfe3f-8cf4-4b54-8c38-069ed803b669.

Method Exit: CreateSingleTargetUpdateDQ(4)
Method Exit: CreateUpdateDQ(4)
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
Method Exit: UnitOfWork2.Commit(2)
An exception was caught during the execution of an action query: The UPDATE statement conflicted with the REFERENCE constraint "FK_DerivedObject_BaseObject". The conflict occurred in database "testdb", table "dbo.DerivedObject", column 'ObjectID'.
The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.


MTrinder
User
Posts: 1461
Joined: 08-Oct-2008
# Posted on: 28-Apr-2009 21:20:53   

Have a look at the documentation for tapping into actions on entities and collections

There should be a method in there that will allow you to do what you need. The other alternative is to create a manager/factory class to return you a new entity with the PK Guid already filled in.

Matt