Setting custom value for auto-increment column in EF4

I am using EF4 with ASP.NET. The entity Subject has a field Id which is set to auto-increment in MySql. This normally works great, but on one occasion I need to set a custom value for Id.

Subject sNone = new Subject { Id = 0, Title = "None", Code = "00" };
ctx.Subjects.A开发者_StackOverflow社区ddObject(sNone);

This indeed creates a new subject in the database. However, its Id is not set to 0 - it is set to an auto-increment value.

Any idea how I can get around this?

You cannot get around this with EF. Once the column is auto incremented it is configured in entity model with StoreGeneratedPattern.Identity. That means that:

• Column will be filled in database and value passed back to application after insert.
• It also means that value of this column will never be passed from your application.

If you turn off the StoreGeneratedPattern you will lose all this behavior because value of this column will be treated as any other and it will be always passed to the database = your auto incrementation logic will not work.

There is also separate problem - is it allowed to set value for auto incremented columns explicitly in MySQL? In MSSQL Server it is not unless you explicitly turn it on for the connection.

The best way to insert this record with explicit Id is using old ADO.NET or ExecuteStoreCommand on ObjectContext to execute SQL insert directly.