Many-to-many Relationship

Many-to-many Relationship

Postby palitto.consulting » Mon 21 Nov 2011 19:14

LinqConnect 3.0.14.0
Visual Studio 2010 Pro

I have the following tables:

Code: Select all
Table `Index` => {ID PK AUTOINCREMENT}
Table `Column` => {ID PK AUTOINCREMENT}
Table `IndexColumn` => {IndexID, ColumnID}
`Index`(ID) 1  =>  * `IndexColumn`(IndexID)
`Column`(ID) 1  =>  * `IndexColumn`(ColumnID)

I run the following code:
Code: Select all
         Column lFirstCol = new Column();
         Column lSecondCol = new Column();
         Index lIdx1 = new Index();
         Index lIdx2 = new Index();
         lIdx1.Columns.Add(new IndexColumn() {Column = lFirstCol});
         lIdx2.AddColumn(new IndexColumn() {Column = lSecondCol});

         mContext.Columns.InsertOnSubmit(lFirstCol);
         mContext.Columns.InsertOnSubmit(lSecondCol); // HERE
         mContext.SubmitChanges();

On the marked line, I receive the following exception:
Code: Select all
InvalidOperationException - Can not insert entity with the same key if key is not database generated.
at Devart.Data.Linq.Table.a(Object A_0, Object A_1, MetaType A_2)
at Devart.Data.Linq.Table.a(Object A_0, MetaType A_1, Dictionary`2 A_2, Boolean A_3)
at Devart.Data.Linq.Table.a(Object A_0, MetaType A_1, Dictionary`2 A_2, Boolean A_3)
at Devart.Data.Linq.Table.a(Object A_0, MetaType A_1, Boolean A_2)
at Devart.Data.Linq.Table.a(Object A_0, Boolean A_1, Boolean A_2)
at Devart.Data.Linq.Table.c(Object A_0, Boolean A_1)
at Devart.Data.Linq.Table`1.InsertOnSubmit(TEntity entity)
at MySQLDataContextTest.TestSaveTable() in MySQLDataContextTest.cs:line 74


Do I need to do something different to add a many-to-many relationship?
palitto.consulting
 
Posts: 6
Joined: Mon 21 Nov 2011 18:34

Postby StanislavK » Wed 23 Nov 2011 11:46

As far as I can understand, there is a separate entity type for the link table available in your model. Am I correct? In this case, the problem is that the 'left' and 'right' entities have no primary key defined until the submit (as their keys are auto-generated at the server side), thus both 'link' entities have zero primary keys. LinqConnect does not allow adding two entities with the same primary key, unless this key is auto-generated.

To resolve the problem, please mark one of the primary key fields of the 'link' entity as 'Auto Generated Value' and set its Auto-Sync property to 'OnInsert'. In this case, the code you've specified should work properly.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby palitto.consulting » Mon 28 Nov 2011 14:02

Unfortunately this does not work either. Neither of the columns in the table `IndexColumn` are indeed autogenerated. They are both foreign keys which reference autogenerated values.

1. By setting `ColumnID` to auto-generated, "AUTO_INCREMENT" is added to the database column definition. I can manually remove this and the database structure looks correct but:

2. When attempting to complete the insert, I get the following exception:

Code: Select all
MySQLDataContextTest.TestSaveTable:
System.InvalidOperationException : Cannot refresh Entity. Record does not exist.
at Devart.Data.Linq.s.a(MethodInfo A_0, ae A_1)
at Devart.Data.Linq.s.a(MetaType A_0, ae A_1)
at Devart.Data.Linq.s.a(k A_0, ae A_1, Object A_2, Boolean A_3)
at Devart.Data.Linq.n.a(s A_0, ae A_1, Boolean A_2)
at Devart.Data.Linq.n.a(DataContext A_0, ConflictMode A_1)
at Devart.Data.Linq.n.b(DataContext A_0, ConflictMode A_1)
at Devart.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
at Devart.Data.Linq.DataContext.SubmitChanges()
at MySQLDataContextTest.TestSaveTable() in MySQLDataContextTest.cs:line 74


I also tried to set "Auto-Sync" to "Never" instead of "Insert", but this did not change the behavior.
Here are the queries which are executed against the blank database:

Code: Select all
INSERT INTO `Column` (...) VALUES (...)
-- Context: Devart.Data.MySql.Linq.Provider.MySqlDataProvider Model: an Build: 3.0.14.0


INSERT INTO `Index` (...) VALUES (...)
-- Context: Devart.Data.MySql.Linq.Provider.MySqlDataProvider Model: an Build: 3.0.14.0


INSERT INTO `IndexColumn` (IndexID, ColumnID) VALUES (:p1, :p2)
-- p1: Input Int (Size = 0; DbType = Int32) [1]
-- p2: Input Int (Size = 0; DbType = Int32) [1]
-- Context: Devart.Data.MySql.Linq.Provider.MySqlDataProvider Model: an Build: 3.0.14.0


SELECT ColumnID FROM `IndexColumn` WHERE ColumnID = :key1 AND IndexID = :key2
-- key1: Input Int (Size = 0; DbType = Int32) [0]
-- key2: Input Int (Size = 0; DbType = Int32) [0]
-- Context: Devart.Data.MySql.Linq.Provider.MySqlDataProvider Model: an Build: 3.0.14.0
palitto.consulting
 
Posts: 6
Joined: Mon 21 Nov 2011 18:34

Postby StanislavK » Mon 28 Nov 2011 17:50

As far as I can understand, you are working with the MySQL database. Am I correct?

Thank you for your assistance, we've reproduced the issue for MySQL servers. We will analyze it and inform you about the results as soon as possible.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby palitto.consulting » Mon 28 Nov 2011 17:54

Yes, that is correct. I am using a MySQL database.
palitto.consulting
 
Posts: 6
Joined: Mon 21 Nov 2011 18:34

Postby palitto.consulting » Fri 16 Dec 2011 21:57

Is there any update available on this issue? It is beginning to become a roadblock in a sizable project we are writing.
palitto.consulting
 
Posts: 6
Joined: Mon 21 Nov 2011 18:34

Postby StanislavK » Mon 19 Dec 2011 16:57

We have fixed this issue, the fix will be available in the nearest build. We will inform you when this build is released.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StanislavK » Mon 26 Dec 2011 08:05

We have released the new 3.1.21 build of LinqConnect, which includes the fix for this issue. The new build can be downloaded from
http://www.devart.com/linqconnect/download.html
(the trial and free versions) or from Registered Users' Area (for users with active subscription only).

For the detailed information about the fixes and improvements available in LinqConnect 3.1.21, please refer to
http://www.devart.com/forums/viewtopic.php?t=22978
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Many-to-many Relationship

Postby palitto.consulting » Thu 26 Jan 2012 22:30

We've updated the software and are still having the same problem. Is there anything else we need to do to our existing project before it will work like regenerating the code from the diagram? Thanks...
palitto.consulting
 
Posts: 6
Joined: Mon 21 Nov 2011 18:34

Postby StanislavK » Fri 27 Jan 2012 16:55

Please specify whether you are encountering the 'Can not insert entity with the same key' or 'Cannot refresh Entity' error with the new build. In the first case, please try setting either the IndexID or ColumnID column to be auto-generated.

If this doesn't help (and you are getting the 'Cannot refresh Entity' error), could you please specify the scenario in which the issue can be reproduced? For example, the sample you've specified in the first post runs successfully in our environment.

Also, we are now refactoring the LinqConnect change tracking and entity submitting functionality, so one of the nearest versions should fix this problem (even without the workaround with declaring a many-to-many key field as auto-generated).
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby palitto.consulting » Fri 27 Jan 2012 21:03

The error is;

Palitto.CDRReplication.MetadataModel.DataContexts.MySQLDataContextTest.TestSaveCDRSchema:
System.InvalidOperationException : Can not insert entity with the same key if key is not database generated.

And the call stack is;

at Devart.Data.Linq.Table.a(Object A_0, Object A_1, MetaType A_2)
at Devart.Data.Linq.Table.a(Object A_0, MetaType A_1, Dictionary`2 A_2, Boolean A_3)
at Devart.Data.Linq.Table.a(Object A_0, MetaType A_1, Dictionary`2 A_2, Boolean A_3)
at Devart.Data.Linq.Table.a(Object A_0, MetaType A_1, Dictionary`2 A_2, Boolean A_3)
at Devart.Data.Linq.Table.a(Object A_0, MetaType A_1, Dictionary`2 A_2, Boolean A_3)
at Devart.Data.Linq.Table.a(Object A_0, Boolean A_1, Boolean A_2)
at Devart.Data.Linq.Table`1.InsertOnSubmit(TEntity entity)
at Palitto.CDRReplication.MetadataModel.DataContexts.MySQLDataContextTest.TestSaveCDRSchema() in C:\Users\xxxx\Documents\SourceCode\CDRReplicationCopy\CDRReplicationTest\MetadataModel\DataContexts\MySQLDataContextTest.cs:line 220

If I only create one record it is fine but if there is a second we get this error.

Setting either the IndexID or ColumnID to auto-generate would not be valid in this case since these columns are foreign keys into 2 other tables but the combination of these two columns is the primary key on this table.

Thank you for the quick response.
palitto.consulting
 
Posts: 6
Joined: Mon 21 Nov 2011 18:34

Postby StanislavK » Tue 31 Jan 2012 18:09

Thank you for the clarification. We will inform you when the new version with the fix for this issue is available.

At the moment, you can do one of the following as a workaround:
1) Insert both 'Index' and 'Column' instances and submit the changes before creating the 'IndexColumn' entities. In this case, you will have to invoke the SubmitChanges method twice.
2) Mark either the 'IndexID' or 'ColumnID' property as auto-generated (in the model, not in the database). Provided that you set its 'Auto-Sync' property to 'Never', you should be able to insert explicitly set values into this column as well.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to LinqConnect (LINQ to SQL support)