My apologies if this don't make any sense, it's 10:00 and I haven't sleep.

My question is: How do I save a parent entity and it's related child entities?

To save an Order entity I do:

using (var adapter = new DataAccessAdapter(_newMySqlConnString))
{
    // New Order
    var newOrder = new OrderEntity();

    newOrder.OrderNr = Convert.ToInt32(txtOrderNr.Text);
    newOrder.OrderDate = dtpOrderDate.DateTime;
    newOrder.Customer = (CustomerEntity)luEditCustomer.GetSelectedDataRow();
    newOrder.DeliveryDate = dtpDeliveryDate.DateTime;
    newOrder.DeliveryName = txtDeliveryName.Text;
    newOrder.DeliveryAddress = txtDeliveryAddress.Text;
    newOrder.Canceled = false;

    adapter.SaveEntity(newOrder);
}

But how do I save the OrderDetail entities from the DataGridView?

The OrderDetail entity collection is binded to a BindingSource which is binded to the DataGridView

I know that to save a collection you use:

adapter.SaveEntityCollection(orderdetailcollection);

But how it saves the FK from the parent entity (OrderEntity) into each OrderDetailEntity from the DataGridView?

Thanks in advance!

PS I'm using latest version of LLBLGen

Oke, cleared my mind a little bit and got the following code:

... After saving OrderEntity
foreach (OrderDetailEntity ode in orderdetailcollection)
{
    ode.OrderId = newOrder.OrderId;
}

var recordsSaved = adapter.SaveEntityCollection(orderdetailcollection, true, true);

Haven't test it yet because there's still more work to do in the form but: Is this a good aproach?

That is ok. But What happens if the orderdetailcollection save fails? It wont be save any orderDetail, but the order entity is already save, so you will have a lonely order without details in your DB. The best approach is save all at one (recursive save), or include both saves (order and orderDetail) in a transaction:

Approach 1:

// add the details to the order object
order.OrderDetails.AddRange(orderdetailcollection);

// save recursively
order.Save(true, true);

Approach 2:

adapter.StartTransaction(IsolationLevel.ReadCommitted, "OrderSaveAction");
adapter.SaveEntity(order);

foreach (OrderDetailEntity ode in orderdetailcollection)
{
    ode.OrderId = newOrder.OrderId;
}

var recordsSaved = adapter.SaveEntityCollection(orderdetailcollection, true, true);

// commit tx
adapter.Commit();

Thanks for you answer daelmo!

I had tried the 2nd approach since the 1st approach didn't work, I got the following error:

Error   2   Argument 1: cannot convert from 'EasyRental.DAL.HelperClasses.EntityCollection' to 'System.Collections.Generic.IEnumerable<EasyRental.DAL.EntityClasses.OrderDetailEntity>

Do I need to make a conversion first or something?

Anyway, diggin more into the method I created, now I need a way to subtract the Qty of each entity from the inventoryCollection from each OrderDetailEntity in the orderdetailscollection

InventoryEntity
----------------
InventoryID
ProductID
Stock
StockMax
StockMin


OrderDetailEntity
----------------
OrderDetailID
OrderID
InventoryID
Qty
Total

And this is the result I have:

foreach (OrderDetailEntity ode in entityCollEventDetail)
{
    var inventory = new InventoryEntity(ode.InventoryId);
    adapter.FetchEntity(inventory);

    inventory.Stock -= ode.Qty;
    adapter.SaveEntity(inventory);

    ode.OrderId = newOrder.OrderId;
    //entityCollEventDetail.Add(ode);
}

So I though there must be a better approach since it could take a long time if there're a lot of OrderDetails

This approach will be slow as you are fetching each inventory item one at a time inside the loop.

pseudo code... not working...!

//work out which inventory items are needed for the update
adapter.FetchEntityCollection(inventoryItems)

foreach (OrderDetailEntity ode in entityCollEventDetail)
{
   //find the correct inventory item
   inventoryItems.Find(InventoryField.Id == ode.InventoryItemId);

    inventory.Stock -= ode.Qty;
    adapter.SaveEntity(inventory);

    ode.OrderId = newOrder.OrderId;
    //entityCollEventDetail.Add(ode);
}

adapter.SaveEntityCollection(inventoryItems)
adapter.SaveEntityCollection(orderDetailItems);

This should perform much better.

Matt

MTrinder wrote:

This approach will be slow as you are fetching each inventory item one at a time inside the loop.

pseudo code... not working...!

//work out which inventory items are needed for the update
adapter.FetchEntityCollection(inventoryItems)

foreach (OrderDetailEntity ode in entityCollEventDetail)
{
   //find the correct inventory item
   inventoryItems.Find(InventoryField.Id == ode.InventoryItemId);

    inventory.Stock -= ode.Qty;
    adapter.SaveEntity(inventory);

    ode.OrderId = newOrder.OrderId;
    //entityCollEventDetail.Add(ode);
}

adapter.SaveEntityCollection(inventoryItems)
adapter.SaveEntityCollection(orderDetailItems);

This should perform much better.

Matt

Thanks Matt for your quick reply.

Regarding about your posted code, I found out that there's no Find(), I only have FindMatches().

But looking around I found another way to get a reference to that entity.

...
var inventory = new EntityCollection<InventoryEntity>(new InventoryEntityFactory());
adapter.FetchEntityCollection(inventory, null);
var inventoryView = new EntityView2<InventoryEntity>(inventory);
inventoryView = inventory.DefaultView;

foreach (OrderDetailEntity ode in entityCollEventDetail)
{
    // Filter view
    inventoryView.Filter = (InventoryFields.InventoryId == ode.InventoryId);
    // Get a reference to the entity to find
    InventoryEntity toFind = inventoryView[0];

    ...
}

There's also something I'm confused about. How is it going to update the stock value in the entitycollection?

As I know, I need to fetch toFind, modify the values and save it. So basically it will be the same as the last code, right?

I'm confused...

This is a modified version of Matt's code. This should work:

foreach (OrderDetailEntity ode in entityCollEventDetail)
{
    //find the correct inventory item
    var inventoryFoundIndexes = inventoryItems.Find(InventoryField.Id == ode.InventoryItemId);

    // if found, update Stock
    if (inventoryFoundIndexes.Length > 0)
    {
          invetoryItems[ inventoryFoundIndexes[0] ].Stock -= ode.Qty;
    }
   ...
}

// save stuff
adapter.SaveEntityCollection(inventoryItems)
adapter.SaveEntityCollection(orderDetailItems);

However, I think you have a little problem here: What if two people run this code at same time?

Guy A: - Create new order, with one OrderDetail (inventory 1, quantity 5) - Fetch the inventory, at this point inventory is 100; - The guy save the transaction. After that, inventory is 95.

Guy B: - Create new order, with one OrderDetail (inventory 1, quantity 3) - Fetch the inventory, at this point inventory is 100; - The guy save the transaction. After that (suppose Guy A save first), inventory is 97.

Ar the end of the two guy's transactions, Inventory (1) Stock should be 92, but it's 97. So you should't fetch inventory to modify it. You should:

a. Implement a concurrency control (check the docs for detail information) or b. Perform the inventory update via DB expression. (check this).

So, the pseudo code in my opinion should be:

// START TRANSACTION WITH adapter OBJECT

// CREATE NEW ORDER AND ORDER DETAILS.
OrderEntity order = new OrderEntity();
....
// SAVE ORDER RECURSIVELY

// UPDATE Inventory
foreach (OrderDetailEntity ode in order.OrderDetails)
{
     InventoryEntity inventoryUpdateValues = new InventoryEntity();
     inventoryUpdateValues .Fields[(int)InventoryFieldIndex.Stock].ExpressionToApply = 
    (InventoryFields.Stock - ode.Quantity));

     DataAccessAdapter adapter = new DataAccessAdapter();
     IRelationPredicateBucket filter = new RelationPredicateBucket(InventoryFields.Id == ode.InventoryId);
     adapter.UpdateEntitiesDirectly(inventoryUpdateValues, filter);
}

// COMMIT TRANSACTION

Hope that helps.

daelmo wrote:

This is a modified version of Matt's code. This should work:

foreach (OrderDetailEntity ode in entityCollEventDetail)
{
    //find the correct inventory item
    var inventoryFoundIndexes = inventoryItems.Find(InventoryField.Id == ode.InventoryItemId);

    // if found, update Stock
    if (inventoryFoundIndexes.Length > 0)
    {
          invetoryItems[ inventoryFoundIndexes[0] ].Stock -= ode.Qty;
    }
   ...
}

// save stuff
adapter.SaveEntityCollection(inventoryItems)
adapter.SaveEntityCollection(orderDetailItems);

However, I think you have a little problem here: What if two people run this code at same time?

Guy A: - Create new order, with one OrderDetail (inventory 1, quantity 5) - Fetch the inventory, at this point inventory is 100; - The guy save the transaction. After that, inventory is 95.

Guy B: - Create new order, with one OrderDetail (inventory 1, quantity 3) - Fetch the inventory, at this point inventory is 100; - The guy save the transaction. After that (suppose Guy A save first), inventory is 97.

Ar the end of the two guy's transactions, Inventory (1) Stock should be 92, but it's 97. So you should't fetch inventory to modify it. You should:

a. Implement a concurrency control (check the docs for detail information) or b. Perform the inventory update via DB expression. (check this).

So, the pseudo code in my opinion should be:

// START TRANSACTION WITH adapter OBJECT

// CREATE NEW ORDER AND ORDER DETAILS.
OrderEntity order = new OrderEntity();
....
// SAVE ORDER RECURSIVELY

// UPDATE Inventory
foreach (OrderDetailEntity ode in order.OrderDetails)
{
     InventoryEntity inventoryUpdateValues = new InventoryEntity();
     inventoryUpdateValues .Fields[(int)InventoryFieldIndex.Stock].ExpressionToApply = 
    (InventoryFields.Stock - ode.Quantity));

     DataAccessAdapter adapter = new DataAccessAdapter();
     IRelationPredicateBucket filter = new RelationPredicateBucket(InventoryFields.Id == ode.InventoryId);
     adapter.UpdateEntitiesDirectly(inventoryUpdateValues, filter);
}

// COMMIT TRANSACTION

Hope that helps.

Thank you very much David, your last code is what I was looking for. It worked as expected, I only added some modifications to it to suit more my needs.

And also, thank you for pointing me out about the concurrency control. I can't believe I missed such important element.