Conditional multi table insert

Question

My application parses a web resource for data constantly. The data is then used to build a domain object. The domain object is represented in my database across several tables. The domain object is defined by the class below, Order, and its db table equivalent is named order. The OrderItem class that is part of its composition is stored in the table order_item;

class Order
{
    public string Id;
    public int Amount;
    public List<OrderItem> Items;
}

class OrderItem
{
    public string Id;
    public string Name;
    public decimal Price;
}

After parsing a web resource, I would like to insert the Order into the database if it doesn't exist. The way I would do this without the 'if it doesn't exist' constraint is to add an insert statement for the order object, and a separate insert statement for every orderitem object it contains, as part of a transaction. I am not sure how to structure it with the unique constraint, because if i do insert ignore/or on duplicate update, then the order items will be inserted regardless. What is the best way to handle these scenarios? I am using mysql and c#;

Answer 1

... if it doesn't exist ...

See

INSERT IGNORE ...

And

INSERT INTO .. ON DUPLICATE KEY UPDATE ...

Both require a suitable UNIQUE key to catch its "existence".