Is there any way to do a Insert or Update / Merge / Upsert in LLBLGen

Question

I'd like to do an upmerge using LLBLGen without first fetching then saving the entity.
I already found the possibility to update without fetching the entity first, but then I have to know it is already there.

Updating entries would be about as often as inserting a new entry.

Is there a possibility to do this in one step?
Would it make sense to do it in one step?

Facts:

LLBLgen Pro 2.6
SQL Server 2008 R2
.NET 3.5 SP1

score 1 · Answer 1 · answered Sep 05 '12 at 19:37

1

I know I'm a little late for this, but As I remember working with LLBLGenPro, it is totally possible and one of its beauties is everithing is possible! I don't have my samples, but I'm pretty sure you there is a method named UpdateEntitiesDirectly that can be used like this:

// suppose we have Product and Order Entities
using (var daa = new DataAccessAdapter())
{
    int numberOfUpdatedEntities = 
        daa.UpdateEntitiesDirectly(OrderFields.ProductId == 23 && OrderFields.Date > DateTime.Now.AddDays(-2));
}

When using LLBLGenPro we were able to do pretty everything that is possible with an ORM framework, it's just great! It also has a method to do a batch delete called DeleteEntitiesDirectly that may be usefull in scenarios that you need to delete an etity and replace it with another one.

Hope this is helpful.

answered Sep 05 '12 at 19:37

Ashkan

3,322
4
36
47

Hi. I know this method. Problem is, I don't know in advance which items to update and which to insert. To know that I'd have to fetch them first (which I do now). – StampedeXV Sep 06 '12 at 06:37
@StampedeXV if you don't know which Entities to update, then you don't know which entities to fetch!! – Ashkan Sep 06 '12 at 06:41
In addition this method only updates the entities that Exist, it does not insert new entites. – Ashkan Sep 06 '12 at 06:45
I only have the PK of the entities I want to update. But for each entity the values that shall be updated may differ. So I create the objects ... and then? :) – StampedeXV Sep 06 '12 at 12:51
Now I know what you meant exactly! I think you can achieve this with an entity collection. – Ashkan Sep 06 '12 at 13:19

score 1 · Answer 2 · answered Sep 06 '12 at 16:59

I think you can achieve what you're looking for by using EntityCollection. First fetch the entities you want to update by FetchEntityCollection method of DataAccessAdapter then, change anything you want in that collection, insert new entities to it and save it using DataAccessAdapter, SaveCollection method. this way existing entities would be updated and new ones would be inserted to the Database. For example in a product order senario in which you want to manipulate orders of a specified product then you can use something like this:

int productId = 23;
var orders = new EntityCollection<OrderEntity>();
using (DataAccessAdapter daa = new DataAccessAdapter())
{
    daa.FetchEntityCollection(orders, new RelationPredicateBucket(OrderFields.ProductId == productId))

    foreach(var order in orders)
    {
        order.State = 1;
    }

    OrderEntity newOrder = new OrderEntity();
    newOrder.ProductId == productId;
    newOrder.State = 0;
    orders.Add(newOrder);

    daa.SaveEntityCollection(orders);
 }

He wants to do it in **one step**, not to fetch the entities first. Is it possible to set primary key of the Entities to update? — Ashkan, Sep 07 '12 at 09:13
I don't think if it's possible in one step, it's not the way sql server works, so it's not LLBLGen's way either. — VahidNaderi, Sep 07 '12 at 10:13
I want to do it in one step if possible. There are possibilities to do this in SQL (like MERGE or UPSERT if I remember correctly), even if they are not perfect. I am already doing it the way you propose, but I was looking for a better way to do it. Thanks for the answer though! — StampedeXV, Sep 07 '12 at 14:35

score 0 · Answer 3 · answered Jul 31 '12 at 17:55

As far as I know, this is not possible, and could not be possible.

If you were to just call adapter.Save(entity) on an entity that was not fetched, the framework would assume it was new. If you think about it, how could the framework know whether to emit an UPDATE or an INSERT statement? No matter what, something somewhere would have to query the database to see if the row exists.

It would not be too difficult to create something that did this more or less automatically for single entity (non-recursive) saves. The steps would be something like:

Create a new entity and set it's fields.
Attempt to fetch an entity of the same type using the PK or a unique constraint (there are other options as well, but none as uniform)
If the fetch fails, just save the new entity (INSERT)
If the fetch succeeds, map the fields of the created entity to the fields of the fetched entity.
Save the fetched entity (UPDATE).

Exactly. And there are DB constructs that allow this (i.e. merge, upsert). Also: Saving an Entity in LLBLGen with manually IsNew set to false allows updating without first fetching it. I thought the next step would be pretty easy, to update if available, insert otherwise. — StampedeXV, Aug 01 '12 at 06:14

Is there any way to do a Insert or Update / Merge / Upsert in LLBLGen

3 Answers3