Does LLBLGen support Merge statement? If so, what would be the adapter code to perform this? And in what version of LLBLGen?

http://en.wikipedia.org/wiki/Merge_(SQL)

LLBLGen doesn't support MERGE statement, because you don't need it. How OR/M systems work is: you fetch entities (table rows which are mapped into objects), you edit those entities or add new ones to the collection, then you save back the collection, the framework takes care of identifying the new or updated ones to emit the correct SQL.

Is there any reason that you want to use sql MERGE statement?

daelmo wrote:

...you don't need it....

Is there any reason that you want to use sql MERGE statement?

Obviously you're telling me what I don't need, so it doesn't sound like you really want to hear about my use case.

daelmo wrote:

How OR/M systems work is: you fetch entities (table rows which are mapped into objects), you edit those entities or add new ones to the collection, then you save back the collection

Your own documentation details updates that do not require a fetch first, in both option 2 and 3. You are basically making a pretty strong argument against features in your own product.

http://www.llblgen.com/documentation/3.0/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Adapter/gencode_usingentityclasses_adapter.htm#Modifyinganentity

The ANSI discussions surrounding acceptance of merge into the standard outlines the use cases, and I believe that those apply to use cases for an ORM as well.

There are scenarios where the client has a list of entities that have been modified. Some may be new, some may be changed, and currently I must query the DB and perform two loops to compare the client and DB collections, and prepare a list for updates and a list for inserts. I just had the notion that I could significantly improve the design of the client code if I could hand off that logic to the DB engine, since the MERGE statement would make a lot of that client logic unnecessary.

It was just a notion, and I thought I'd pop in and find out if it was supported in some form. sorry to ruffle anyone's feathers.

AaronLS wrote:

daelmo wrote:

...you don't need it....

Is there any reason that you want to use sql MERGE statement?

Obviously you're telling me what I don't need, so it doesn't sound like you really want to hear about my use case.

the question was asked so we get more insight in why you'd use MERGE over what's already available to you. Nothing else. I don't understand your answer, to be honest.

daelmo wrote:

How OR/M systems work is: you fetch entities (table rows which are mapped into objects), you edit those entities or add new ones to the collection, then you save back the collection

Your own documentation details updates that do not require a fetch first, in both option 2 and 3. You are basically making a pretty strong argument against features in your own product.

No he didn't.

Direct updates are meant for bulk updates mostly, so you can update a set of entities directly, e.g. based on an expression. Upserts/Merges are not meant for that. They're meant for solving a total different situation, namely the one where you try to insert a new entity into a table and another user/thread has already inserted an entity with the same PK. Instead of throwing an error that the PK is already taken, you 'merge' the row you want to insert with the row already there, as if you're updating that row instead.

So it's a concurrency resolving statement.

The ANSI discussions surrounding acceptance of merge into the standard outlines the use cases, and I believe that those apply to use cases for an ORM as well.

Sure, but in the concurrency resolving area.

There are scenarios where the client has a list of entities that have been modified. Some may be new, some may be changed, and currently I must query the DB and perform two loops to compare the client and DB collections, and prepare a list for updates and a list for inserts. I just had the notion that I could significantly improve the design of the client code if I could hand off that logic to the DB engine, since the MERGE statement would make a lot of that client logic unnecessary.

Not necessarily. What if you updated an entity X and another user/thread deleted entity X. MERGE will insert a new X, while it should throw an error: X is no longer available. This isn't solvable with the single mechanism of having a set of altered/new entities.

This requires a different save mechanism, where you direct the o/r mapper not to simply save the entities, but to use merge / upserts instead (e.g. SaveOrUpdate). We currently don't support that, the concurrency system relies on whether the action succeeds or fails based on update == update, insert == insert. We do that as using MERGE instead of insert/update is not always efficient nor always wanted: it has to be directed explicitly by the developer.

It's indeed a good suggestion to add this in a future release, especially since nowadays databases do support merge (years ago when we looked at it, it was only Oracle if I recall correctly).

I still fail to see how a support request is responded to with such an arrogant statement. You don't go to a restaurant and ask for a glass of wine and the waiter tell you "We don't have wine, because you don't need it."

AaronLS wrote:

I still fail to see how a support request is responded to with such an arrogant statement. You don't go to a restaurant and ask for a glass of wine and the waiter tell you "We don't have wine, because you don't need it."

I fail to see what's so arrogant about what's been said. Perhaps you misunderstood what we meant? I think David meant: 'it's not something you need to accomplish what you want to do'.

I typed a long post and all you can say is 'such an arrogant statement'. Now, please tell ME, where on earth I was arrogant. I tried to explain things to you and you apparently didn't read any of it, as you have no comments on that whatsoever. I hope I don't have to explain to you what I think of that.