title: How Do SQL Transactions Work?
body: <p>I have not been working in SQL too long, but I thought I understood that by wrapping SQL statements inside a transaction, all the statements completed, or none of them did. Here is my problem. I have an order object that has a lineitem collection. The line items are related on order.OrderId. I have verified that <strong><em>all the Ids are set and are correct</em></strong> but when I try to save (insert) the order I am getting <strong>The INSERT statement conflicted with the FOREIGN KEY constraint "FK_OrderItemDetail_Order". The conflict occurred in database "MyData", table "dbo.Order", column 'OrderId'.</strong></p>
  
  <p>psuedo code:</p>
  
  <pre>
  create a transaction
  transaction.Begin()
  Insert order
  Insert order.LineItems &lt;-- error occurs here
  transaction.Commit
  </pre>
  
  <p>actual code:</p>
  
  <pre>
  ...
  entity.Validate();
  if (entity.IsValid)
  {
      SetChangedProperties(entity);
      entity.Install.NagsInstallHours = entity.TotalNagsHours;
      foreach (OrderItemDetail orderItemDetail in entity.OrderItemDetailCollection)
      {
          SetChangedOrderItemDetailProperties(orderItemDetail);
      }
      ValidateRequiredProperties(entity);
      TransactionManager transactionManager = DataRepository.Provider.CreateTransaction();
      EntityState originalEntityState = entity.EntityState;
      try
      {
          entity.OrderVehicle.OrderId = entity.OrderId;
          entity.Install.OrderId = entity.OrderId;
          transactionManager.BeginTransaction();
  
          SaveInsuranceInformation(transactionManager, entity);
          DataRepository.OrderProvider.Save(transactionManager, entity);
          DataRepository.OrderItemDetailProvider.Save(transactionManager, entity.OrderItemDetailCollection);             if (!entity.OrderVehicle.IsEmpty)
          {
              DataRepository.OrderVehicleProvider.Save(transactionManager, entity.OrderVehicle);
          }
          transactionManager.Commit();
      }
      catch
      {
          if (transactionManager.IsOpen)
          {
              transactionManager.Rollback();
          }
          entity.EntityState = originalEntityState;
      }
  }
  ...
  </pre>
  
  <p>Someone suggested I need to use two transactions, one for the order, and one for the line items, but I am reasonably sure that is wrong. But I've been fighting this for over a day now and I need to resolve it so I can move on even if that means using a bad work around. Am I maybe just doing something stupid?</p>