Hi!

This is supposed to be trivial, but I seem to be missing something .

I am trying to do recursive save for new entity and I get the following exception:

An exception was caught during the execution of an action query: Cannot insert the value NULL into column 'OrderID', table 'Northwind.dbo.Order Details'; column does not allow nulls. INSERT fails.

Speaking in "Northwind" terms, here is the code that generated exception:

        public static void SaveOrdersDeep()
        {
            OrdersEntity order = new OrdersEntity();
            OrderDetailsEntity orderDetail = new OrderDetailsEntity();
            orderDetail.ProductId = 1;
            orderDetail.Quantity = 4;
            orderDetail.UnitPrice = 12.5M;
            orderDetail.Discount = 0;
            orderDetail.Orders = order; // I know this isn't necessary
            order.OrderDetails.Add(orderDetail);
            new DataAccessAdapter().SaveEntity(order, true, null, true);
        }

What's wrong? Please help!

that northwind lookalike code probably fails because of the Orders' fields not being filled in, but that aside, the main reason why these kind of setups fail is that in the following situation:

Entity A 1:n Entity B, and you do: myA.Bs.Add(myB); myA is saved but it is assumed A has an autonumber PK but in the database, this isn't defined. So myA is saved, but has not a new value, as the sequence isn't set in the database.

If you're using Oracle or other sequence using database, the PK of A has to be set to a sequence in the designer before generating code.

that northwind lookalike code probably fails because of the Orders' fields not being filled in

Sorry, I didn't provide all the code (I was just illustrating the intention), and it would be enough to add the following initialization:

            order.CustomerId = "ALFKI";
            order.EmployeeId = 1;

Entity A 1:n Entity B, and you do: myA.Bs.Add(myB); myA is saved but it is assumed A has an autonumber PK but in the database, this isn't defined. So myA is saved, but has not a new value, as the sequence isn't set in the database.

If you're using Oracle or other sequence using database, the PK of A has to be set to a sequence in the designer before generating code.

I am using SQLServer 2000 with autonumber for Orders PK (OrderID). I tried saving just OrdersEntity (without saving it recursevly) and it got OrderID generated, but as soon as I set SaveEntity parameter recurse to true, I get the exception.

So, if I understood it well, it fails because it doesn't set generated OrderID to orderDetails object, but I don't know how can I set it differently.

I did some additional experimenting and here is some code that works:

        public static void SaveOrdersDeep()
        {
            OrdersEntity order = new OrdersEntity();
            order.CustomerId = "ALFKI";
            order.EmployeeId = 1;
            OrderDetailsEntity orderDetail = new OrderDetailsEntity();
            orderDetail.ProductId = 1;
            orderDetail.Quantity = 4;
            orderDetail.UnitPrice = 12.5M;
            orderDetail.Discount = 0;
            orderDetail.Orders = order; // I know this isn't be necessary
            order.OrderDetails.Add(orderDetail);
            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.OpenConnection();
            try
            {
                if(adapter.SaveEntity(order, true, null, false))
                {
                    orderDetail.OrderId = order.OrderId;
                    adapter.SaveEntityCollection((EntityCollection)order.OrderDetails, true, false);
                }
            }
            finally
            {
                adapter.CloseConnection();
            }
        }

I've a lot of unittests with this kind of code, so I'm really surprised it fails in your situation. I'll have a look.

I can't reproduce it:

[Test]
public void RecursiveSaveTest()
{
    DataAccessAdapter adapter = new DataAccessAdapter();
    adapter.StartTransaction(IsolationLevel.ReadCommitted, "inserttest");

    OrderEntity order = new OrderEntity();
    order.CustomerId = "ALFKI";
    order.EmployeeId = 1;
    OrderDetailsEntity orderDetail = new OrderDetailsEntity();
    orderDetail.ProductId = 1;
    orderDetail.Quantity = 4;
    orderDetail.UnitPrice = 12.5M;
    orderDetail.Discount = 0;
    orderDetail.Orders = order; // I know this isn't be necessary
    //order.OrderDetails.Add(orderDetail);
    try
    {
        Assert.IsTrue(adapter.SaveEntity(order, true));
        Assert.AreEqual(order.OrderId, orderDetail.OrderId);
        Assert.IsTrue((order.OrderId>0));
    }
    finally
    {
        adapter.Rollback();
    }
}

works. (I rollback the transaction to keep the db clean, but no errors whatsoever turn up).

I commented out the add of the collection, but it doesn't matter, if the line isn't commented out, it also succeeds.

So I'm a bit confused why this doesn't work for you...

Frans,

Will it work also?

Commented out the line

//orderDetail.Orders = order;

instead of line

order.OrderDetails.Add(orderDetail);

Thanks

Gabor

Yes