Unable to insert identity column value explicitly using entity framework

Question

Hello Friends,

First i would like to inform  you that i am novice in entity framework. I have reverse engineered a database in sql 2005 into entity model using vs 2010. I have a table Customer in the database which has column ID as a primary key and also an identity column. Now whenever i try to insert values using entity framework in the  customer table it generates this identity field automatically, even though i pass the value explicitly which is in a way correct since i set the id field as primary and identity column.

But now i want to enter id field explicitly for syncronizing data from old database. I tried the following steps to perform this task

1. In edmx designer i set the StoreGeneratedPattern for id field as computer or none instead of identity. But it still enters the identity column while inserting data .

2. I purposely tried to change the database schema by setting the identity property off on the ID column of the customer table and then tried to insert data using entity framework. Now even though i am passing the value for ID still it throws the exception that id field cannot be null.

My entity framework field is described as below:

<Property Name="Id" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Computed" />

Code for inserting data.

 Customer p = Customer.Create (id, 1, DateTime.Now, 3, DateTime.Now, 3, strname, nperiod, ntype);

Context.Current.AddToCustomer (p);

 Context.Current.SaveChanges(System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave);

I dont know how to resolve this problem. An urgent help is required.

 

Thanks,

Amit

Answer 1

Hi Amit,

It looks as though the StoreGeneratedPattern for the Id field is still set to "Computed". Try changing this to "None" in the designer. Additionally you may need to open the XML and change the Property element for Id under the "<!-- SSDL Content -->" section so that its StoreGeneratedPattern attribute equals "None" as well.

Also, for most circumstances it is preferable that you call SaveChanges without any arguments.

Let me know if that helps,

David

---

Blog - http://blogs.rev-net.com/ddewinter/ Twitter - @ddewinter

Answer 2

Hi,

I tried with none option both in designer and xml but still it doesnt work. and about SaveChanges i tried without arguments first and when it wasnt working i tried with arguments but in both ways same results.

Thanks,

Amit

Answer 3

Can you post the CSDL and SSDL XML for Customer?

---

Blog - http://blogs.rev-net.com/ddewinter/ Twitter - @ddewinter

Answer 4

But now i want to enter id field explicitly for syncronizing data from old database.

Two things come into play here:

1) First of all, changing StoreGeneratedPattern in the designer will only change it in the CSDL portion of the EDMX file as pointed out in other replies above. You need to also change it in the SSDL. (Using the VS XML editor or using third party tools that can sync SSDL <=> CSDL.)

2) Next, if the db column is a 'int identity' column then by default SQL Server will not allow you to provide a value when inserting. You can work around this with the 'set identity insert [table] on' TSQL statement: http://msdn.microsoft.com/en-us/library/ms188059.aspx

You'll need to run 'set identity insert...' that on the same connection that you use with EF, so you need to a) explicitly create a connection, b) run 'set identity insert...' on that connection, c) supply it to an EF object context, d) do the inserts, e) turn off identity insert.

Alternatively, changing the column in the database from 'int identity' to just 'int' (no identity) while loading data from the other db takes out the 'set identity insert...' hassle.

Also, if this is a one-off bulk load operation then using SSMS data import functionality might be a better option.

---

Kristofer - Huagati Systems Co., Ltd. Cool tools for Linq-to-SQL and Entity Framework: huagati.com/dbmltools - add-in with new useful features for the L2S and EF designers in VS2008 and VS2010 huagati.com/L2SProfiler - Query profiler for Linq-to-SQL, Entity Framework v4, and LLBLGen Pro