Search code examples
mysqlentity-frameworkforeign-keysriaauto-increment

Entity Framework related objects insertion with stored procedure and auto_increment field

I have a problem inserting a related row through Entity Framework 5. I'm using it with RIA Services and .NET Framework version 4.5. The database system is MySQL 5.6. The connector version is 6.6.5.

It raises a Foreign Key constraint exception.

I've chosen to simplify the model to expose my issue.

LDM

Provider(id, name, address)

Article(id, name, price)

LinkToProvider(provider_id, article_id, provider_price)

// Id's are auto_increment columns.

First I create a new instance of Article. I add an instance of LinkToProvider to the LinkProvider collection of the article. In this LinkToProvider object the product itself is referenced. An existing provider is also referenced. Then I submit the changes.

Sample code from the DataViewModel

this.CurrentArticle = new Article();

...

this.CurrentArticle.LinkToProvider.Add(

new LinkToProvider { Article = this.CurrentArticle, Provider = 
this.ProviderCollection.CurrentItem }

);

...

this.DomainContext.articles.Add(this.CurrentArticle);

this.DomainContext.SubmitChanges();

NOTE :

At the begining Entity Framework inserts the product well. Then it fails because it tries to insert a row in the LinkToPrivder table with an unkown product id like the following.

INSERT
INTO LinkToProvider
VALUES(5, 0, 1.2)

It puts 0 instead of the generated id.

But if I insert a product alone without any relations the product id is generated in the database correctly.

Any help will be much appreciated ! Thank you.

Solution

  • I found the answer. You need to bind the result from the stored procedure to the id column in the edmx model

    So I have to modify my stored procedure to add an instruction to show the last instered id for the article table on the standard output. 

    SELECT LAST_INSERT_ID() AS NewArticleId;

    Then I added the binding with the name of the column name returned by the stored procedure. Here it's NewArticleId.

    It's explained here : http://learnentityframework.com/LearnEntityFramework/tutorials/using-stored-procedures-for-insert-update-amp-delete-in-an-entity-data-model/.