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.

Improve this question
4
  • 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, 2016 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, 2016 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, 2016 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, 2016 at 20:08

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 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, 2016 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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