Pseudo-inheritance and primary keys

killswtch · Post by **killswtch** » Fri 24 Dec 2010 17:31

In my project, I have abstracted out persistence so that I can plug in currently either SQL Server or SQLite. For SQL Server I am using the VS 2008 built-in SQL-to-LINQ model generator.

The database has been designed to map directly to the objects that are represented, with inheritance implemented as a 1-1 relationship on primary keys between parent and child tables.

The 'base' table is called 'Entity', and contains various bits of metadata about object instances. This has a primary key (now 64 bit int, to get around a previous issue) with autoincrement.

All the other tables 'inherit', some indirectly, from the Entity table. They pick up their ID once the Entity record has been inserted. Or at least they are supposed to, and this works with the SQL Server version.

However, for the SQLite version, while the Entity record is inserted OK, the ID of the Entity instance is not passed back and used as the ID of the inheriting record/object i.e. while the records in the Entity table have an ID of 1, 2, 3, 4 ... etc., the other tables all have IDs of 1, 1, 1, 1 ... etc.

Am I missing something in the configuration of my dotConnect LINQ model? I can provide the database and model if needed. Thanks.

killswtch · Post by **killswtch** » Fri 24 Dec 2010 18:11

Actually, what I said above isn't quite accurate after some further testing.

Given 3 tables:

BaseTable
InheritingTable
OtherInheritingTable

Where InheritingTable and OtherInheritingTable both have a PK integer ID that is an FK to the corresponding ID on BaseTable.

In this case, inserting into BaseTable works as expected, with incrementing IDs. However, in the other 2 tables, it's as if the tables have their own autoincrement seed, starting at 1.

Example Data:

Code: Select all

BaseTable
=========

ID	CreateDate
1	2010-12-24 18:04:31.3454100
2	2010-12-24 18:04:31.4024100
3	2010-12-24 18:04:38.1434100
4	2010-12-24 18:04:38.1734100

InheritingTable
===============

ID	Name			SomethingElse
1	Some Name Here	Lorem Ipsum Dolor (Expected ID = 1)
2	Some Name Here	Lorem Ipsum Dolor (Expected ID = 3)

OtherInheritingTable
=================

ID	SomeText
1	Blah Blah (Expected ID = 2)
2	Blah Blah (Expected ID = 4)

This is the code used to insert the data:

Code: Select all

			MainDataContext dc = new MainDataContext();

			InheritingTable it = new InheritingTable();
			dc.InheritingTables.InsertOnSubmit(it);

			it.Name = "Some Name Here";
			it.SomethingElse = "Lorem Ipsum Dolor";
			it.BaseTable = new BaseTable();
			it.BaseTable.CreateDate = DateTime.Now;

			dc.SubmitChanges();

			dc = new MainDataContext();

			OtherInheritingTable oit = new OtherInheritingTable();
			dc.OtherInheritingTables.InsertOnSubmit(oit);

			oit.SomeText = "Blah Blah";
			oit.BaseTable = new BaseTable();
			oit.BaseTable.CreateDate = DateTime.Now;

			dc.SubmitChanges();

In this example, the IDs across InheritingTable and OtherInheritingTable should be unique and contained in BaseTable. In the data shown above, the code has been run twice to hopefuly illustrate the problem.

The code for this sample is available if needed.

StanislavK · Post by **StanislavK** » Mon 27 Dec 2010 11:30

Thank you for your report, we have reproduced the issue. We will investigate it and inform you about the results.

As a workaround, you can insert the base entity, and then insert inherited entities separately using the generated id.

killswtch · Post by **killswtch** » Mon 27 Dec 2010 13:04

Thanks for looking into it. Unfortunately, the workaround would require a major rewrite of the data persistence layer due to the way it works, so I will have to wait for the fix to be released. Do you think this will be included with the other issue I raised recently?

StanislavK · Post by **StanislavK** » Tue 28 Dec 2010 13:50

We are working on this issue, but cannot provide an exact timeframe at the moment.

We will inform you as soon as our investigation is completed.