1

I'm creating ASP.NET Core application using Dapper as ORM.

What is the proper flow to saves objects into multiple tables?

In my app architecture I got standard web api controllers that invoke command/query handlers that calculate/invoke other services/repositories etc.

My entities/db tables are User, Order, Product. One of my CommandHandler create user, then calculate products, orders etc. I just wonder how to save these objects into database. I see 2 solutions:

1.) I create some kind of DTO for all the calculated stuff during command handler:

public class TestDto
{
    public User User;
    public IList<Orders> Orders;
}

calculate all the stuff, fill the DTO one by one, and then at the end of command handler invoke all repositories:

...using (var ts = new Transaction)
{
 _userRepository.Save(dto.User);
 _ordersRepository.Save(dto.Orders);

 ts.Complete
}

etc..

2.) Create transaction per whole command handler, and save user immediately after calculating him in memory, then calculate orders and save them immediately as well, and the same with orders.

CSharpBeginner
  • 1,625
  • 5
  • 22
  • 36
  • If consistency is paramount using a transaction is the way to go. However, consistency is not always necessary. In fact most engineers don't realise that consistency is not necessary at all. Therefore atomic commands are not mandatory. You could make your methods idempotent. Which would allow this logic to be executed more than once without adding multiple users. You could also remove the user in a catch if adding orders fails. We need to learn to be more forgiving and add a bit more grace to our systems. – Thomas Heijtink Nov 19 '19 at 19:11
  • Because as engineers you get to decide how your system behaves in certain states. If you create a system that can handle those situations it isn’t a problem at all. But of course it entirely depends on the system one is creating. Sometimes consistency is mandatory. In this case one could argue that a user is still a user even though it hasn’t made any orders yet. Or even though the user has not been able to place an order successfully. If it would be a customer, the discussion could be entirely different. – Thomas Heijtink Nov 20 '19 at 05:53

1 Answers1

0

You're going to need at least one transaction regardless. You don't want to save an incomplete order. I say at least one because it depends on if your user can exist separately from the order.

You could create and save the user first in one transaction, then save the order in a second transaction. If anything goes wrong while saving the order, you still have the user committed to the database. With option #2, you could perform the two transactions within your command handler.

With option #1, I'd be concerned about introducing an extra object tying users and orders together. You may find times when you need to make changes to just the user or an order separately from each other. The TestDto wouldn't be helpful.

Andy S.
  • 176
  • 1
  • 6
  • So let's say business rule is to save all or nothing. So I know that for this requirement in #1 i will have to conclude all those repositories invocations in Transaction. So the question is calculate in memory and after all save, or save one by one while calculating? – CSharpBeginner Nov 19 '19 at 19:08
  • One could argue that that shouldn’t be a business rule. But an implementation detail. The question is, why do they want to save all or nothing. Maybe it is warranted but it doesn’t have to be. Inconsistent data can lead to bad behavior. It’s the behavior they don’t want. But if your system is robust it doesn’t have to exhibited bad behavior just because you have incomplete data. It’s your system. Build it like it makes most sense, technically. – Thomas Heijtink Nov 20 '19 at 05:46