Related Tabled, Identity and ReferentialConstraint

[blopblop]

Hello,

I'm creating a simple log database and i've a problem on table linking Identity columns and ReferentialConstraint error :

I 'm using :
- visual studio 2010
- Entity framework 6
- dotconnect Sqlite v5.1.103

Database sample :

Code: Select all

-- Script was generated by Devart Entity Developer, Version 5.7.299.0
-- Script date 07/03/2014 08:23:54
-- Target Server: SQLite
-- Server Version: 3.7

-- 
-- Creating a table RelatedClasses 
-- 
CREATE TABLE RelatedClasses (
   IdRelatedClass INTEGER PRIMARY KEY AUTOINCREMENT,
   SubPropertyTest1 INT32
);

-- 
-- Creating a table MainClasses 
-- 
CREATE TABLE MainClasses (
   IdMainClass INTEGER PRIMARY KEY AUTOINCREMENT,
   PropertyTest1 TEXT,
   IdRelatedClass INT32,
   CONSTRAINT FK_MainClasses_RelatedClasses FOREIGN KEY (IdRelatedClass) REFERENCES RelatedClasses (IdRelatedClass)
);

1. when i add association between main and related class with an Identity column (majority of cases) entity model editor does not set corresponding MainClass.IdRelatedClass property to Identity. Entity Model cannot generate and throw an error :

The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'IdRelatedClass' on entity 'MainModel.Store.MainClasses' does not match the type of property 'IdRelatedClass' on entity 'MainModel.Store.RelatedClasses' in the referential constraint 'FK_MainClasses_RelatedClasses'.

If Ids definition must match, why entity model editor does not set it automatically ?

2. setting manually MainClass.IdRelatedClass to Identity, trying to insert anything throw an error :

Code :

Code: Select all

using (MainEntities entities = new MainEntities(ConnectionString))
			{
				RelatedClass relatedClass = new RelatedClass
				{
					SubPropertyTest1 = 1
				};

				entities.RelatedClasses.AddObject(relatedClass);
                                entities.SaveChanges(); // no pb

				MainClass mainClass = new MainClass
				{
					PropertyTest1 = "test",
					RelatedClass = relatedClass
				};

				entities.MainClasses.AddObject(mainClass);
				entities.SaveChanges();  // throw error

			}

Error :

A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'IdRelatedClass'.

Question : where is my error ? and how to have some table with Identity set for Primary Key and linked them to another table in a simple 0..1 <-> many way ?

Thanks in advance for any help/hint.

[blopblop]

Hello again,

Searching a bit more on different dialog boxes i had to go on :
1. edit properties of MainClass.IdRelatedClass
2. set "Store generated" to "Identity" for model to build
3. on "Storage column" clic on "..." and on this dialog set "Store Generated" to "None".

Note that if i click on "Regenerate Storage & Mapping" step 3 is lost.

Then Build all again and code sample works :

Code: Select all

using (MainEntities entities = new MainEntities(ConnectionString))
{
        // add a related class
	RelatedClass relatedClass = new RelatedClass
	{
		SubPropertyTest1 = 1
	};

	entities.RelatedClasses.AddObject(relatedClass);
	entities.SaveChanges();

        // test with first related class in db
	MainClass mainClass = new MainClass
	{
		PropertyTest1 = "test 1",
		RelatedClass = entities.RelatedClasses.First()
	};
	entities.MainClasses.AddObject(mainClass);

        // test with previously added related class
	mainClass = new MainClass
	{
		PropertyTest1 = "test 2",
		RelatedClass = relatedClass
	};
	entities.MainClasses.AddObject(mainClass);

        // test setting IdRelatedClass
	mainClass = new MainClass
	{
		PropertyTest1 = "test 3",
		IdRelatedClass = 1
	};
	entities.MainClasses.AddObject(mainClass);


	entities.SaveChanges();
}

I'm still interested on why do i have to do this way / where is/are my mistake(s).

Thanks

[MariiaI]

Most likely, there is a type discrepancy between the MainClass.IdRelatedClass and RelatedClass.IdRelatedClass properties(in SSDL and CSDL model parts).
Please do the following steps:
1) open Properties for the IdRelatedClass entity property (the RelatedClass class):
- set its Type to "Int32';
- open the "Storage Column Editor" for this property and set Type to 'Int32' too;

2) open Properties for the IdRelatedClass entity property (the MainClass class):
- set its Type to "Int32';
- open the "Storage Column Editor" for this property and set Type to 'Int32' too;
- do not set the MainClass.IdRelatedClass to Identity.

Save the changes. Please try performing your scenario again and notify us about the results.