Many-to-many Relationship

[palitto.consulting]

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?

[StanislavK]

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.

[palitto.consulting]

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

[StanislavK]

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.

[palitto.consulting]

Yes, that is correct. I am using a MySQL database.

[palitto.consulting]

Is there any update available on this issue? It is beginning to become a roadblock in a sizable project we are writing.

[StanislavK]

We have fixed this issue, the fix will be available in the nearest build. We will inform you when this build is released.

[StanislavK]

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

[palitto.consulting]

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...

[StanislavK]

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).

[palitto.consulting]

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.

[StanislavK]

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.