One-to-many update issue

One-to-many update issue

Postby Miros » Mon 19 May 2014 10:31

I have an issue with a classic one-to-many table association; order and order lines.

I have implemented two webservice methods, SaveOrder() and SaveOrderline(), working like this:

1. Calling SaveOrder() with an order model object with Status = "Open" and an empty list of order lines results in one perfect INSERT INTO Order query.

2. Calling SaveOrderline() repeatedly afterwards also works like a charm, inserting each order line in the database correctly associated with the order.

3. Calling SaveOrder() again, now with order.Status = "Closed", and again an empty list of orderlines, results in a perfect UPDATE of the order, but here is the issue; the devart entity model generates UPDATE queries for all the orderlines, setting their foreign key (OrderUUID) to NULL, which throws an error "Cannot add or upadte a child row: a foreign key constraint fails", obviously because the OrderUUID of the order line cannot be NULL.

It is important to note that my SaveOrder() method loads the order from the database if it already exists, in order to update the entity and save the changes to the database.

My question is this:

Is it possible to perform an update of the order, without updating its child entities (orderlines)? - Setting order.Orderlines = null seems to delete the order lines when saving changes.
Miros
 
Posts: 45
Joined: Thu 20 Jan 2011 10:12

Re: One-to-many update issue

Postby Miros » Mon 19 May 2014 11:24

Could this be related with this post: http://forums.devart.com/viewtopic.php?f=2&t=25019

We're using Devart.Mysql version 7.2.77.0
Miros
 
Posts: 45
Joined: Thu 20 Jan 2011 10:12

Re: One-to-many update issue

Postby MariiaI » Tue 20 May 2014 10:32

There were a lot of improvements and fixes since the version you are using. We recommend you to upgrade your dotConnect for MySQL and try your scenario. For example, you can try dotConnect for MySQL Professional Trial 8.3.161 to see if the error still persists with the new version or not.
If the error remains, please provide us with the following details:
- the DDL/DML scripts of the necessary database tables;
- the definitions of the DataContext class and entity classes;
- the full stack trace of the error you are getting;
- the code snippet, with which the error occurs;
- the generated SQL query, etc.

If possible, please send us a test project/model, with which the error is reproducible, so that we could investigate it in more details and find the solution for you.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: One-to-many update issue

Postby Miros » Thu 22 May 2014 13:54

Hi Mariial,

I guess that was the easiest way for you to answer my question.

Sadly it was an extremely time consuming way for me to see if you were right, since I've now been through the process of:

1. Downloading and installing the trial version as you suggested which, after hours of struggeling with various license issues in my VS projects, didn't work at all because of the notoriously annoying complexity of licensing in the dotConnect for MySql libraries. For your information, in reality it is not possible to simply download a trial version to see if things work better in that version, the way you suggested.
2. Uninstalling the trial version, removing all references in projects and GAC, to prepare for a clean install of the licensed Pro version.
3. Purchasing ($99 down the drain) and installing the licensed version 8.3, just to realize that it did not do one bit of difference.

But in the end I found out what the problem was, but it still leaves me with a question of how this could be handled differently:

1) Load an Order with a number of Orderlines from the database through the EDML, leaving you with an entity model object of type Order with an Orderlines property populated with Orderlines associated with the Order.
2) Overwrite the Orderlines property by setting order.Orderlines = null.
3) Call db.SaveChanges() and observe that there will be generated an update query per orderline "UPDATE Orderline SET OrderUUID = NULL WHERE UUID = 'XXX'" - basically erasing the association between the Orderline and the Order.

The reason why I set order.Orderlines to null is because I don't want to send any orderlines from my client app for performance reasons, so I don't want EDML to update any orderlines - just update the Order and ignore the associated Orderlines.

Is that possible somehow?
Miros
 
Posts: 45
Joined: Thu 20 Jan 2011 10:12

Re: One-to-many update issue

Postby MariiaI » Mon 26 May 2014 11:40

Thank you for the response. However, the information you've provided is not enough to reproduce your scenario and determine the reason of the behaviour you have described.
Setting "order.Orderlines = null" causes updating the list of related entities by setting foreign keys to null, and this is an expected behavior.

Please, provide us with the following details:
- the DDL/DML scripts of the necessary database tables;
- the definitions of the Context class and entity classes;
- the full stack trace of the error you are getting;
- the code snippet, with which the error occurs;
- the generated SQL query, etc.

If possible, please send us a test project/model, with which the error is reproducible, so that we could investigate it in more details and find the solution for you in a shortest time.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: One-to-many update issue

Postby Miros » Mon 26 May 2014 12:09

Setting "order.Orderlines = null" causes updating the list of related entities by setting foreign keys to null, and this is an expected behavior.


Is it possible somehow to circumvent this behaviour, eg. by setting a property on the Entity Model object like eg.:
Code: Select all
db.IgnoreRelatedEntities = true;
db.SaveChanges();
db.IgnoreRelatedEntities = false;
Miros
 
Posts: 45
Joined: Thu 20 Jan 2011 10:12

Re: One-to-many update issue

Postby MariiaI » Wed 28 May 2014 05:52

Is it possible somehow to circumvent this behaviour, eg. by setting a property on the Entity Model object like eg
No, unfortunately, there is no way.

If you want to update only parent class and do not update the child one, you shouldn't apply any changes to the child entity in the code. If you will not use "order.Orderlines = null" explicitly, etc., Orderlines will not take part in the update statement. According to your posts, "Orderlines" are included in the generated update SQL. To define the reason of such behaviour and find some workaround/solution, we should know the following details:
- entity classes definitions
- relations between them
- the generated SQL and the code snippet, etc.
Also, you have mentioned that you have found the reason. We would be very much obliged to you, if you share your scenario and your solution. Probably, we will provide you with an alternative way or your solution will be useful to other users.

Please also take a look at the LazyLoadingEnabled property(by default it is set to True):
http://msdn.microsoft.com/en-us/library/dd456846.aspx
http://msdn.microsoft.com/en-us/library/system.data.objects.objectcontextoptions.lazyloadingenabled.aspx
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for MySQL