Parent-Child Insertion inside TransactionScope cause foreign key constraint error

Question

I'm trying to insert data for parent child relationship inside TransactionScope and I get INSERT statement conflicted with the FOREIGN KEY constraint error. Here is my code:

using (var scope = new TransactionScope())
{
    try
    {
        discount = discountDataAccess.Insert(discount);
        switch (discount.Type)
        {
            case eDiscountType.PerCustomer:
                InsertDiscountCustomer(discount.Id, idList);
                break;

            case eDiscountType.PerPackage:
                InsertDiscountPackage(discount.Id, idList);
                break;
        }

        scope.Complete();
        return discount;
    }
    catch (Exception ex)
    {
        scope.Dispose();
        throw;
    }
} 

When DiscountCustomer or DiscountPackage will be inserted, Discount.Id still is 0 because no data inserted to database until scope.Complete() called. So basically I can't save the DiscountCustomer or DiscountPackage until I commit Discount and Transaction is not committed until both saved successfully.

Is there any way to insert both parent and child inside TransactionScope ?

Answer 1

I found out it's not possible with distributed transactions because TransactionScope is still in the context of the method and nothing will be inserted till scope.Complete() gets called, but it can be done by SqlTransaction class, which can be retrieved from SqlConnection.

try
{
    var transaction = connection.BeginTransaction();
    discount = discountDataAccess.Insert(discount, transaction);
    switch (discount.Type)
    {
        case eDiscountType.PerCustomer:
            InsertDiscountCustomer(discount.Id, idList, transaction);
            break;

        case eDiscountType.PerPackage:
            InsertDiscountPackage(discount.Id, idList, transaction);
            break;
    }

    transaction.Commit();
    return discount;
}
catch (Exception ex)
{
    if (transaction != null)
        transaction.Rollback();

    throw;
}