0

I need to publish an "Orders" table to a web site, but for reasons beyond this scope, it is not possible for the web app to have direct access to the company's database.The solution is to create the same "Orders" table in another database (on a remote IIS server) and export the original orders (rows) to it. For this I'm using the EF 5 and AutomaticMigrationsEnabled = true

The original "Orders" table has a key column named "OrderNo", where "IsIdentity = Yes". I would like the web "Orders" table to also have its "OrderNo" as a key column, so that I can use the "Find" method to locate and update the rows, but here is the problem:

I cannot set the "OrderNo" to be an identity, since it must keep the original "OrderNo" value, however after running "PM> update-database" the "Orders" table is created with the "OrderNo" column "IsIdentity" set to "Yes". If I set it to "No", by opening the table in design mode, the next Migrations will give an error.

After trying some options, I found that if I add another "dummy" column with a [Key] attribute on it, the table is created with 2 keys and none has the identity set to "Yes". In this way, it is possible to insert new rows keeping the original "OrderNO" value and update rows using the "Find" method. Naturally, the "Find" method now requires 2 parameters, the dummy column (whose value is always 0 (zero)) and the original "OrderNo".

Although it is working, I know this is not the correct way to do it and I'm wondering if someone knows how to do it.

NOTE: I know I can find the entity using a query, which does not requires the "OrderNo" to be a key but I read the "Find" (using primary keys) has some advantages.

Ben Junior
  • 2,449
  • 10
  • 34
  • 51
  • You turn off the identity constraint. http://www.bennadel.com/blog/24-Turning-Off-and-On-Identity-Column-in-SQL-Server.htm – asawyer Oct 28 '13 at 15:55
  • OK, but how to do it in EF ? I didn't see any data attribute for that. Maybe using the Fluent API ? – Ben Junior Oct 28 '13 at 17:26
  • Both exist but it only works first time you create the table http://stackoverflow.com/a/19313131/150342 and http://stackoverflow.com/a/18917348/150342 – Colin Oct 28 '13 at 17:42
  • I wonder how you are going to keep the two databases in sync. Have you thought about creating a linked view in the other database instead of a table? – Colin Oct 28 '13 at 17:46
  • Colin: Thanks! The solution on the first link worked for me. I just couldn't understand the remarks "it only works first time around". Isn't that sufficient? – Ben Junior Oct 28 '13 at 20:19
  • The database in the original application is not from Microsoft. Currently, we only need to see the orders in the web, thus the sync is only in one direction. – Ben Junior Oct 28 '13 at 20:22
  • If you create a table with an IDENTITY column then try to change it EF migrations don't do it for you unless you drop and recreate the table https://entityframework.codeplex.com/workitem/509 – Colin Oct 29 '13 at 09:51
  • "we only need to see the orders in the web" - 2 tables with the same data = extra create update delete with triggers or something. Have you thought about using a view? – Colin Oct 29 '13 at 09:56

1 Answers1

1

I don't know about setting the option in Entity Framework, but from a SQL perspective the command to enable inserting into an identity column is SET IDENTITY_INSERT ON.

http://technet.microsoft.com/en-us/library/ms188059.aspx

David
  • 1,591
  • 1
  • 10
  • 22