Oracle: Can't insert new entity with one-to-one relationship

[Konamiman]

I am having trouble with a LinqConnect for Oracle data context: I can't insert a new entity together with a new associated entity when the relationship is one-to-one.

I have created a simple Visual Studio solution that reproduces the problem. It contains a LinqConnect data context with just three tables:

• Customers
  Cards, related to Customers with a one-to-one relationship
  Orders,related to Customers with a one-to-many relationship

I want to be able to insert a new customer with a new card in the database in one single operation, like this:

Code: Select all

var dataContext = new CustomersDataContext();

var customer = new Customer()
{
    Name = "Customer Smith"
};
customer.Card = new Card()
{
    Number = 1234
};

dataContext.Customers.InsertOnSubmit(customer);
dataContext.SubmitChanges();

But this does not work. I get the following error: "integrity constraint (FK_TEST_Card_TEST_Customers_0) violated - parent key not found".

If I try to set the Card property to an already existing customer record, it works fine. Also, there is no problem when the relationship is one-to-many. Thus the following works perfectly:

Code: Select all

var dataContext = new CustomersDataContext();

var customer = new Customer()
{
    Name = "Customer Smith",
};
customer.Orders.Add(new Order()
{
    Date = DateTime.Now
});
customer.Orders.Add(new Order()
{
    Date = DateTime.Now
});

dataContext.Customers.InsertOnSubmit(customer);
dataContext.SubmitChanges();

Another less important issue is that if I want to add a card to an already existing customer, I need to explicitly set the parent key field (IdCustomer), otherwise the primary key field of the customer (Id) is set to zero and the SubmitChanges invokation fails:

Code: Select all

var dataContext = new CustomersDataContext();

var customer = new Customer()
{
    Name = "Customer Smith",
};
dataContext.Customers.InsertOnSubmit(customer);
dataContext.SubmitChanges();

customer.Card = new Card()
{
    IdCustomer = customer.Id,   //FAIL without this (customer.Id is set to zero)
    Number = 1234
};
dataContext.SubmitChanges();

So, is this a bug in LinqConnect, or I am just doing something wrong?

The Visual Studio 2010 project is here in case someone wants to take a look, just change the connection string and create the tables in some Oracle server:
http://dl.dropbox.com/u/17125937/DevArt ... veTest.zip

Thank you!

[StanislavK]

Thank you for the report. The issue with inserting 'master' and 'detail' entities simultaneously is a known one. We are working on it, and will inform you when it is fixed. It occurs if the 'master' table has an auto-generated primary key. As a workaround, you can insert 'masters' and 'details' separately (i.e., within two SubmitChanges calls).

As for the second issue, could you please specify the version of LinqConnect (or dotConnect for Oracle) you are using? You can check it in the Tools -> LinqConnect -> 'About LinqConnect' (or Tools -> Oracle -> 'About dotConnect for Oracle') item of the Visual Studio main menu. We couldn't reproduce the problem with the latest 2.20.17 version of LinqConnect (the corresponding version of dotConnect for Oracle is 6.10.126).

[Konamiman]

Hello, thanks for your reply.

Yes, I realized that the problem disappears when using two SubmitChanges calls. But I'm afraid that this can cause integrity errors in the database if the first SubmitChanges succeeds and the second one fails. I have tried to use transactions but this doesn't seem to work either (I get the same error).

dotConnect for oracle version is 6.10.121, maybe this is the cause of the second problem?

[StanislavK]

As for transactions, could you please specify how exactly you were using them? For example, the following code worked well in our environment:

Code: Select all

static void CreateCustomerWithCard() {

  var dataContext = new CustomersDataContext() { Log = Console.Out };

  dataContext.Connection.Open();
  OracleTransaction t = (OracleTransaction)dataContext.Connection.BeginTransaction();

  var customer = new Customer() { Name = "Customer Smith" };
  dataContext.Customers.InsertOnSubmit(customer);
  dataContext.SubmitChanges();

  customer.Card = new Card(){ Number = 1234 };
  dataContext.SubmitChanges();

  t.Commit();

  dataContext.Dispose();
}

As for the second issue, there was a related fix in the 6.10.126 build:
http://www.devart.com/linqconnect/revision_history.html
Please try updating to the latest version, and tell us if the problem persists.

[fmarakasov]

The small addition to the above. The problem can be reproduced at one-to-many relationship without the need for simultaneous recording of results in master and detail tables:
R1(Detail)={ID(PK, IDENTITY), MasterID(FK, NULL), DetailProps}
R2(Master) = {ID(PK, IDENTITY), MasterProps}

Code: Select all

using(var ctx = new TestDataContext())
{
     var newDetail = new Detail();
     ctx.Details.InsertOnSubmit(newDetail);
     ctx.SubmitChanges();
     var newMaster =  new Master();
     newDetail.Master = newMaster;
     ctx.SubmitChanges();         
}   

This is a fairly common scenario, when the record in the detail table must be created before the master. For example, the entry of stage of the schedule is created before creating an entry about the act closing this step (and one act could relate to sevral stages).

Thanks

[StanislavK]

We've reproduced the latter problem with incorrect order of operations on SubmitChanges(). We will analyze it and inform you about the results.

As for the issue with auto-generated keys, we have fixed it. The fix will be available in the next build (the one we will release after the 6.10.135 version). We will post here when this build is available.

[StanislavK]

We have released the new 2.20.21 version of LinqConnect, which includes the fix for the issue with database-generated entity keys. The new build can be downloaded from
http://www.devart.com/linqconnect/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For more information about the fixes and improvements available in LinqConnect 2.20.21, please refer to
http://www.devart.com/forums/viewtopic.php?t=20796

[StanislavK]

We have fixed the problem with incorrect operation order on SubmitChanges(). The fix is available in the latest 2.50.22 Beta build of LinqConnect. The new build can be downloaded from
http://www.devart.com/linqconnect/download.html
(the trial version) or from Registered Users' Area (provided that you have an active subscription):
http://secure.devart.com/

For more information about the fixes and improvements available in LinqConnect 2.50 Beta, please refer to
http://www.devart.com/forums/viewtopic.php?t=20827