4

I have the following problem-

About ~60 tables on SQL that some have foreign key to each other,primary key as identity had to change to something else with logic. It was replaced by triggers that checks what is the next number and change the primary key field (by sequence)

Now this makes several problems on EF when trying to insert new identity:

  1. In order to update the foreign keys i must retrieve back the entity from DB because the primary key is not refreshing.

  2. When I add more than one entity, the EF throws exception that the primary key is not unique. (It receive the default value in my case 0 for int)

The solution for 1 is good only if I have other unique fields that I can get the entity back from the DB.

The best solution for 2 that I could come up with is using Detach entity after each insert.

Both are not optimal, to say the least.

Would glad to hear if there is any other solution / another approach instead of triggers.

At this point i cannot change the structure of the tables on the database, just manipulations on the primary key field.

Supplement:

The auto generated ID's were abandoned to eliminate duplicates on this field, as these tables will run on several db's that are merged from time to time.

  • 4
    So why did you abandon built-in identity columns? My gut feeling is that maybe you should look at an alternative to that decision rather than trying to force EF into doing something it probably wasn't built for. – Becuzz Dec 20 '16 at 14:54
  • It was abandoned because my app will run on another DB, sometimes they will get merged and I can't have duplicates on these fields. @Becuzz – Ziv Weissman Dec 20 '16 at 16:40
  • 2
    That sounds like a good candidate for using a GUID (or similar) as a merge identifier (ie. how you check to see if a local record is referring to the same thing as a remote record). – Becuzz Dec 20 '16 at 16:59
  • @Becuzz Thanks for the idea, I think it's a good idea but it might be too difficult to implement as it affect several systems and they all must support this change. Let's say I cannot change the tables structure, only the generation of this field, and I can "play" freely with the Entity Framework as long as it does not require a solution that is erased with every EF update. – Ziv Weissman Dec 20 '16 at 20:08
1

I don't think it is a perfect solution, especially for tables that are going to grow unpredictably, but many databases (SQL Server), allow fields that automatically apply an incremental (Identity) value, so be seeded at a number other than one.

Database A can start with 1 and Database B could start with 1 million. You're guaranteed uniqueness between the two databases for their tables, but obviously there is a limit.

There are also routines in the database to reset existing identify values. This would probably require you to resynchronize the data being sent from one to the other.

By doing this, Entity Framework shouldn't be affected. You get some semblance of guaranteeing uniqueness up to a point that will require some DBA-like intervention.

  • Thank you, because it is unknown for now how these tables will grow my DBA didn't approve of this approach... sadly though because It could have been done almost effortlessly. – Ziv Weissman Dec 21 '16 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.