Issue with composite (primary) keys

[david.urting]

Hi,

We are currently evaluating 'dotConnect for SQLite' and have come across an issue with composite keys on a table.

Example of a table with a composite primary key:
CREATE TABLE [TABLE_TWO_COLUMN_KEY] (
A1 NVARCHAR(50) NOT NULL,
A2 NUMERIC NOT NULL,
B NVARCHAR(50) NOT NULL,
PRIMARY KEY ([A1],[A2])
);

When we generate an EDMX model from this table, there is this warning in the EDMX model:
<!--Errors Found During Generation: warning 6002: The table/view 'Catalog1.main.TABLE_TWO_COLUMN_KEY' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.-->

The key that is inferred contains all the 3 columns, so we have to manually remove the B column (via the EDMX designer).

At runtime we have following issue when inserting an object/row in that table:
System.Data.Entity.Core.UpdateException: "SQLite error near "(": syntax error"

The underlying reason is that the table TABLE_TWO_COLUMN_KEY consists of a defining query:
<DefiningQuery>
SELECT
TABLE_TWO_COLUMN_KEY.A1,
TABLE_TWO_COLUMN_KEY.A2,
TABLE_TWO_COLUMN_KEY.B
FROM "main".TABLE_TWO_COLUMN_KEY AS TABLE_TWO_COLUMN_KEY
</DefiningQuery>

The generated INSERT probably wants to insert into the SELECT statement which fails.
The only way to resolve this, is to manually edit de EDMX in XML view and remove the defining query.

Is it possible to have a look a this issue? Manually editing the EDMX is okay for small EDMX files, but it becomes cumbersome for large models. Especially when there are child tables that have a foreign key to such a composite key, the foreign keys are also omitted.

Relevant versions:
- Visual Studio 2013 Update 4
- Entity Framework 6.1.2
- dotConnect for SQLite 5.2.344.0 (trial)

Thanks,
David

[Shalex]

The generation of DefiningQuery is a peculiarity of EDM Designer. There are two alternative ways to fix the issue:
1) open your *.edmx with XML Editor and remove all DefiningQueries because they often lead to extra subqueries
2) use Entity Developer (*.edml) which never generates DefiningQueries in your scenario

We recommend you to use Entity Developer (Devart Entity Model, *.edml) instead of EDM Designer (ADO.NET Entity Data Model, *.edmx) because it is adjusted for working with SQLite and has an advanced functionality: http://www.devart.com/entitydeveloper/ed-vs-edm.html.