Hi,

I have another challenge.

The app I'm working on is being migrated to use LLBLGen. But there are still many parts with database transactions that do not involve LLBLGen (yet).

Sometimes records are written directly into the database. The identity-field is populated by sql server. But not all fields are populated.

Later on the data is read in memory. LLBLGen needs to take over. The data is stored in the entity. Also the value for the identity-field is stored in the entity. This is done to make sure, that when the entity is saved to the database, it performs an "update" instead of an "insert". The record for the entity is already in the database after all.

This works fine for most tables / entities. But there are also tables where the primary key fields are different from the identity fields. Sometimes the primary key fields are not populated yet. So the identity-field is present in the record, but the primary key fields are populated when the data is copied into an entity.

When the entity is save to the database, LLBLGen checks to see if there is a record which has the same values in the primary key fields. And there isn't in my case. So LLBLGen will insert a new record.

For reading data from the database there is a method called FetchEntityUsingUniqueConstraint. I would need something like this for saving the entity. I don't want LLBLGen to match on the primary key fields, but I want LLBLGen to match on the identity-field. Maybe there are some settings to achieve this, but I couldn't find it in the help-files.

I know the help-file says never to change the primary-key fields. But unfortunately this is the situation I'm in and it would be very hard to work around that.

General info about used software: latest builds of LLBLGen designer and runtimes version 1.0.2005.1. Visual Studio 2005. .NET framework 2.0.

Any help would be appreciated! René

But there are also tables where the primary key fields are different from the identity fields. Sometimes the primary key fields are not populated yet. So the identity-field is present in the record, but the primary key fields are populated when the data is copied into an entity.

Althoughe I didn't completely understand your issue, especially the above quoted lines.

but if you have an entity and you want it to perform an update rather than an insert, you should set entity.IsNew = false; before saving it.

Ok. Imagine I have a table with fields: "ID", "PK" and some other fields. The "ID" field is an Identity-field in SQL Server. In LLBLGen I uncheck the "part of primary key" for this field. Then I go to field "PK" in LLBLGen and I check "part of primary key". I also make sure the "ID" field is not readonly (See this thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6641 ) Now I add a record to the database. "ID" is populated by SQL Server and it is set to "1". PK is "0" and the other fields are empty.

Now, when I use the generated code, I create a new instance of my entity. I don't fetch anything. I set the value of the "ID" field to "1". I set the value of the "PK" field to a unique value which is generated by the application, for example "1234". I also populate the other fields.

When I try to save the entity, LLBLGen doesn't update the record in the database. Not even, when I set IsNew = false. Because it searches for a record with "PK" = "1234". And it can't find that record, so it assumes it is a new entity. A new record will be inserted.

I would like LLBLGen to look at the "ID" field instead of looking at the "PK" field. It should see: "ID" field of entity is "1" and the "ID" field in the database is "1", so that record will be updated.

I don't want the primary key set on the identity-field, because I have foreign keys which are mapped to the other unique field, so I want that field to be primary key.

Thanks for your suggestion, unfortunately it doesn't work in my case. René

I think you just need to fetch the entity before updating it.

First fetch the entity from the database, update the fields (use SetNewFieldValue, or ForcedCurrentValueWrite to modify teh PK value)

Make sure the PKField.IsChanged is set to true, and the entity.IsNew is set to false.

Then save the entity to the database.

Also please refer to the follwoing thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3631

Hi Walaa,

Thanks for your reply.

I think you just need to fetch the entity before updating it.

Sorry, that's not an option. First of all, performance is critical here. The code I'm working on is executed very often. The reason we're trying to implement LLBLGen here is to get better performance. We also want to reduce the number of roundtrips over the network and to the database.

Also, It would not even solve my problem. Because, when I fetch it and change the primary-key, it won't update anymore. LLBLGen will know for a fact that the primary key has changed, so it will look for a record with the old values. But that's not what I need here. The primary key fields should have a unique value in the first place in order for this to work. In my case the fields are not yet populated. I need LLBLGen to look at the identity-field (other field that PK) to match for a record to update.

If the PK is changed and IsNew is set to false, LLBLGen won't do anything. IsNew = false will cause LLBLGen not to insert and the changed PK causes LLBLGen not to update.

This thread comes close to my problem: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3064

Frans says: _The problem is, the code generating the query has to formulate a filter on the PK. (to find the row to alter). If you just set the PK field of a new entity, it will think (it has to) that the PK set in the entity is the one to use for the filter.

To ALTER a PK, you have to alter an existing's entity's PK field. This way, DbValue is set as well. This then means that the query generator can find back the row to alter. You run into a check in FieldCompareValuePredicate, where it tries to determine which value to use._

I would like the piece of code in the query generator, which formulates the filter and which Frans is talking about here, to look at the identity-field instead of the primary-key.

I hope someone knows a solution for this. Thanks! René

PK updating only works when the entity is fetched first. This is caused by the fact that the PK filter creator has to have the original value of the PK field, which is stored in DbValue. If DbValue is set, it's used for the filter, otherwise the CurrentValue, if the PK field is changed.

To avoid a fetch, use: yourEntityObject.Fields["fieldName"].ForcedCurrentValueWrite(newCurrentValue, OldDBValue);

This would force the setting of the currentValue and the setting of the DBValue too. You then have to set IsChanged to true, if you want the field to be changed.

EntityName entity = new EntityName();
entity.Fields[(int)EntityNameFieldIndex.PKFieldName].ForcedCurrentValueWrite(PKValueToWrite, PKOldValueInDatabase);
entity.Fields[(int)EntityNameFieldIndex.PKFieldName].IsChanged = true;
entity.IsNew = false;

adapter.SaveEntity(entity);

Also you may try to save the same entity directly using

adapter.UpdateEntitiesDirectly(entity, bucket);

While the bucket may contain a filter on the identity field. And hence you may only use the following overload instead:

entity.Fields[(int)EntityNameFieldIndex.PKFieldName].ForcedCurrentValueWrite(PKValueToWrite);

Hi Walaa,

Reading your answer and also other topics on this forum didn't help me progress with my initial approach. I still can't let LLBLGen match the entity with the records on the identity-field instead of the primary-key-field. But reading about this and testing it, gave me an idea for another approach, which I will try to implement. So your help was most welcome.

Along the way I stumbled upon a topic in the LLBLGen Reference Manual: DataAccessAdapterBase.CreatePrimaryKeyFilter(). Can anyone explain to me what it does and how it can be used? It looks interresting to me, but there is no real documentation about it, anywhere.

René

So to recap: - you have one PK field (PK) for reading and - you have one PK field for saving (ID)

?

that's a little odd. an entity is identified by a PK, that's not related to the operation.

You could override CreatePrimaryKeyFilter, which creates the PK filters for various operations, but that's not recommended.

Frans,

Your recap is almost correct. It's just that I need the extraordinary primary key on the "ID"-field only once. After that the normal primary-key-values are written to the database and they can be used for reading and writing. I just need the "ID"-field to be primary-key when the normal primary-key-fields in the database are not populated yet.

Do you have a code example for CreatePrimaryKeyFilter? I would expect this method to be on Entity-level, because it only takes an ArrayList of primary-key-fields as parameter. Do you have a code example for this?

Thanks alot, René

Hi,

I got a work-around for this. This one is also not very elegant: I make sure I have some unique values inserted manually in the primary key fields and for updating I set the DbValue to these known values, using ForcedCurrentValueWrite, and set IsChanged to true and IsNew to false.

This works, but it is still having a negative effect on the performance and the code looks like sh*t.

So if anyone has another suggestion or a code-example for using CreatePrimaryKeyFilter (which might help), I would appreciate that.

Thanks, René

Rene wrote:

Hi,

I got a work-around for this. This one is also not very elegant: I make sure I have some unique values inserted manually in the primary key fields and for updating I set the DbValue to these known values, using ForcedCurrentValueWrite, and set IsChanged to true and IsNew to false.

This works, but it is still having a negative effect on the performance and the code looks like sh*t.

So if anyone has another suggestion or a code-example for using CreatePrimaryKeyFilter (which might help), I would appreciate that.

Thanks, René

CreatePrimaryKeyFilter returns a predicateexpression based on the fields passed in. If you for example check DataAccessAdapterBase.DeleteEntity(2) you'll see how it is used.

Ok, it looks like CreatePrimaryKeyFilter doesn't offer a solution either. I will have to stick with the work-around I have. Maybe I can refactor a bit.

I know, what I want is a bit unusual. And it is the result of an app being migrated from native database-access to ORM. Some parts still use native database-access. Both parts of the code have to exchange data now and then. This is when I do need to save data to database which is not always complete.

So, I can imagine LLBLGen doesn't support this. So, hereby I will mark the thread as "Done". But there is however a way, this could be solved. So I'll note that as a feature-request:

LLBLGen has a method called FetchEntityUsingUniqueConstraint. I would really like to have a method SaveEntityUsingUniqueConstraint. This would be for cases, when I know I have some unique fields in the entity and I want to update a record in the database matching those fields. In that case the primary key is not used to match the record.

Thanks, René