Hi there

I just got started with LLBLGen, and it sure looks great - nice work, guys

I'm having a bit of a problem with the handling of my IDs using Firebird. I'm usually using sequences to set my identity values, but as data is being imported from other data sources in some cases, I also need the freedom to manually set my IDs.

With standard data binding, this was a no-brainer. I just used a stored procedure that returned the assigned ID as an output parameter. If the ID was a positive value, the SP returns the unchanged ID, otherwise, a sequence was called. (btw: this logic is not implemented in the SP, but a "before insert" trigger). The pattern with the SP is pretty common, see for example here: http://msdn2.microsoft.com/en-us/library/ms971502.aspx

As far as I understood, there is no easy way to handle entitiy creation via the SP, but I thought I could overwrite some portions of my entity in order to reflect that logic already on the client. I guess all I needed was a conditional call of the sequence by LLBLGenPro:

IF ID >= 1: keep the ID ELSE: Call the sequence and assign the ID to the entity before doing the insert.

However, I'm not really sure whether this is the proper way to go with the tool, so it would be great to get some feedback on this first...

Thanks for your advice

Philipp

Hi,

First of all, are you using SelfServicing or Adapter ?

Aurelien wrote:

Hi,

First of all, are you using SelfServicing or Adapter ?

Adapter.

ok,

As described in the documentation (Generated code - Tapping into actions on entities and collections), you can use an overridable method OnBeforeEntitySave to set your field value.

Aurelien,

Thanks for the hint. However, I'm not sure whether I can do here what I was planning to do:

As I defined my ID as an identity field and pointed LLBLGen to the sequence, the tool nicely handles the sequence call for me in order to determine a unique ID which is then used for the insert. As I would like to keep my customizations as small as possible, it would be nice to keep it that way (without having to handle the sequence call myself etc.). This, however, requires me to prevent LLBLGen from calling the sequence if a (positive) ID has already been assigned.

I thought that I might just call the base method if my condition is true, but I'm not sure whether this works, and more important, whether this is a potentially dangerous approach as it might prevent the adapter code from working correctly:

    protected override void OnBeforeEntitySave()
    {
      //only adjust the ID if its a positive one
      //-> this does not work - the sequence is called at a later point :(
      if (DataPointId < 1) base.OnBeforeEntitySave();   
    }

Edit: Just wanted to do some debugging and noticed that the _OnEntityAfterSave _method is not virtual. Is this a bug or by design?

Cheers, Philipp

OnEntityAfterSave is called by a related entity after the entity is persisted. It should not be used for other logic.

This, however, requires me to prevent LLBLGen from calling the sequence if a (positive) ID has already been assigned

I don't get your point here, would you please elaborate more.

Trigger based PK value insertion on firebird isn't supported. So if your tables have triggers for the PK value, you should remove them as you don't need them: the fields should get a sequence assigned in the designer.

If you want to KEEP your triggers, you can't refetch the entity, as LLBLGen Pro doesn't know the value the trigger has inserted as PK value.

Walaan,

Thanks for the reply - let me explain things a bit more elaborate

• I'm using a sequence in order to automatically generate IDs for my records. In LLBLGen, I defined my ID property as an identity field and told LLBLGen which sequence to call.
• BUT: Sometimes, I need to import data with predefined IDs. These should not change.
• I solved this requirement by using a trigger on the database, which only calles the sequence on INSERT if the ID is either NULL or negative. Posititve IDs remain unchanged.
• Currently (I'm not using LLBLGen in production, I'm still evaluating), I'm using an SP that returns the final ID as an output parameter which is subsequently assigned to the ID on the client.

This approach does no longer work with LLBLGen and dynamic SQL, as LLBLGen always calls the sequence before performing my insert. The workflow is like this:

1 I create my entity 2 I assign my custom (positive) ID 3 I save my entity 4 LLBLGen calls the sequence and overwrites the ID 5 LLBLGen performs the insert in the database 6 My ID is lost

Point 4 is the problem. A simple solution would be to provide some conditional logic there: 4: LLBLGen calls the sequence and overwrites the ID IF the ID is negative

However, there might be a more elegant solution - LLBLGen seems to be pretty powerful and I don't have any experience with it (yet).

Philipp

Hi Otis

Ups, was writing my reply to Walaan while you were posting. Great support, guys!

Otis wrote:

Trigger based PK value insertion on firebird isn't supported. So if your tables have triggers for the PK value, you should remove them as you don't need them: the fields should get a sequence assigned in the designer.

If you want to KEEP your triggers, you can't refetch the entity, as LLBLGen Pro doesn't know the value the trigger has inserted as PK value.

I already noticed that triggers don't work, that's why I defined the field as an identity . The trigger itself only changes the ID if it's either NULL or negative, so everything works fine with LLBLGen, too (and others do not have a problem either - the database will be used in a heterogenous environment).

However, the main problem remains: The sequence is _always _called, which is a real problem in the import scenario I described above. I'm sure there is a solution (the worst case would be to manually call my SPs instead on using LLBLGen's dynamic SQL), but I'm not sure how to solve it properly with LLBLGen.

Cheers, Philipp

Either use a SP to INSERT a record with a pre-set ID.

Or Map another entity onto the same table (without specifying Identity or Sequence in LLBLGen Pro Designer), to be used for IMPORTING scenarioes only.

Walaa wrote:

Either use a SP to INSERT a record with a pre-set ID.

Or Map another entity onto the same table (without specifying Identity or Sequence in LLBLGen Pro Designer), to be used for IMPORTING scenarioes only.

Hmm, both sounds a bit like a hack to me (but well, you could argue that my ID problem requests a hack, too ). Just to make sure - are you saying there is no way that I could customize my entities to provide this logic (conditionally ignoring the sequence, or reverting the ID (and FKs) right before an INSERT is being performed)?

The problem is that when saving these entities, there are also a lot of related entities, and I would like to get these things handled automatically. If this simple condition requires me to write a lot of custom code, I end up with a solution that is more complex than the current prototype that just uses typed DataSets (which easily handle this very case out of the box).

Anyway - if it's not already there: How about a simple template method I could use to override the sequence call? Giving the developer the power to control the ID assignment would make a neat feature in a lot of scenarios, wouldn't it?

//overridden to suppress sequence calls if the ID is valid
protected override RetrieveIdentity(SomeMetaData info)
{

  if (SOME CONDITION)
  {
    //fall back to standard behaviour
    base.RetrieveIdentity(info);
  }
  else if (SOME OTHER CONDITION)
  {
     //or even provide my own ID generation...
     this.ID = 123;
  }
}

If this simple condition requires me to write a lot of custom code...

I think the suggestion to use another Entity mapped to the same table won't require any extra coding, it's just another design-time task that should save you a lot of hassle. With all the relations defined you can recursively save the related entities.

This might work, but I'd like to keep things as generic as possible - I'm developing an open framework, and having to deal with different types sounds like trouble. A common use case will be that people import data from different sources (e.g. an XML file that provides IDs, and a CSV file without IDs), edit the data, and then commit everything at once. And I guess that would be just the top of the iceberg

However, I'm quite fond of my idea with the template method. I have no idea how the code looks and where the sequence is called at all, but if there is a single block of code that handles the sequence call, the refactoring would probably take me just a few minutes (if that).

Unfortunately, I don't have the source at hand - I'm still evaluating LLBLGen and whether we'll purchase LLBLGen somewhat depends on how I can handle this very problem. I don't know if this is a lot to ask for (I have no idea how easily it will be for you to find that particular code), but it would be great if you could tell me whether it's as simple as I hope or not.

Cheers, Philipp

THe sequence meta-data is defined in the entity's meta-data, so you can't overwrite it as it's shared among all entities of that same type.

If you just need to override the identity setting for imports, I indeed suggest to use a different entity for that, which doesn't have the field marked as identity. If you have a lot of entities which follow this same pattern, you could create two projects: one for imports and one for normal usage. The one for normal usage has all id fields marked as identity with the proper sequence, the importer one hasn't. You then generate code for both, using different namespaces and you're set.

Otis wrote:

If you just need to override the identity setting for imports, I indeed suggest to use a different entity for that, which doesn't have the field marked as identity.

...this won't work as users will deal with data that provides IDs and other data that does not at the same time. Having to deal with different entities at the same time will eventually lead to problems (or even force the user to adjust their workflow to the limitations of the software). I guess, I'll just stick to my typed datasets for now. However, I'm still considering LLBLGen for standard runtime usage - after all, it works like a charm

Otis wrote:

THe sequence meta-data is defined in the entity's meta-data, so you can't overwrite it as it's shared among all entities of that same type.

Hmm, wouldn't that be exactly what I need? I'm only interested in cutomizing the ID for entities of a given type...

You can't overwrite it as it's readonly data stored once in memory. This speeds up fetches and greatly reduces memory usage, but it has this limitation.

Me again

I'm still struggling with this very issue. It's such a simple thing but this really could turn out to become a show stopper

After having looked at a few other threads, I guess I would have to hande the IDs on my own (calling an SP that gets me the correct ID). However, I don't know where to put my code. If possible, I'd like my entity assembly to handle this stuff on its own without having to rely on custom code in my business logic library.

a) Override OnBeforeSave within the entity That's what seemed to be the obvious point. However, it doesn't look as if I could get a DataAccessAdapter from there. Is there another method or approach?

b) Overriding some other class (e.g. EntityCollection). However, I really don't know the framework. Any hints?

Thanks again Philipp

How do the predefined keys that will be inserted get determined? Is there any reason that you couldn't change this behavior to use the identity field? I'm curious to know more about this problem. I think that may help come up with more creative solutions.

bclubb,

I'm happy that I'm not alone with this issue - thanks

If people decide to build their databases from scratch, the inserted keys will be determined by a generator (same as a sequence in Oracle, I'm using Firebird). However, quite often, my users will want to define the IDs themselves, or want to build the database (embedded, we'll have _plenty _of instances) based on imported data with predefined IDs. This is why LLBLGen identity fields are out of the question.

In order to support both scenarios, my business rule is simple:

• IDs in the database always are positive
• A trigger on the DB ensures this. If an INSERT is performed with a negative/NULL ID, the generator is called to create an ID.
• An INSERT with a positive ID uses the submitted ID.
• What I did before LLBLGen was calling an SP that performs the insert and returns the final ID.

As SPs/triggers are not supported by LLBLGen, I'll have to manage my IDs before performing the INSERT: If a negative or no ID has been set, I need to call the generator manually (through an SP) and setting the ID before saving my entity.

This is basically what LLBLGen does with identities. The difference is that I only change the ID if it's not valid yet.

private void UpdateEntityBeforeSave(MyEntity entity)
{
  if (entity.IsNew && entity.DataPointId < 1)
    entity.DataPointId = RetrievalProcedures.GetId(...);
}

What I don't know is where I should place that code (I'm working with adapters). I'd like to have it in one of my LLGLGen-generated libraries in order to ensure that anybody just can call adapter.SaveEntity() without having to worry about the ID management.

Thanks for your help, Philipp

You should drive a class from the DataAccessAdapter and override the OnBeforeEntitySave method.

Please check the following threads for similar concepts: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=4582 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6258

Works like a charm which means we'll definitely use LLBLGen in the project. And support was great - thanks a lot