On some non-obvious hacks when working with the entity framework and unique constraints

A couple of years ago, when the trees were big and green, evil donetchiki came to me, and they said - yeah, I got caught! I had to help my colleagues in one very strange project.

Namely - imagine a pack of digital calculators , which analysts make once a month, in their favorite MS Office package. And once a month it became necessary to chew on these numbers and load them into a database running MS SQL.

And of course - this mega-tool should have been done quickly. To then transfer to the support cheap whether the Malays, or Hindus. So it was also recommended to do as clear as possible.
')

How they started to solve the problem

Evil dotnetchiki decided to simplify their lives for themselves - not to make the insert into their hands, if there are so many columns in the corresponding report that Excel gives them three-letter names. And in the ancient database a huge stack of tables, in some of which the number of columns is simply amazing.

Therefore, it was done like this - the database was slipped into the visual studio, and they got a huge code for the entity framework. Instead of sawing with a jigsaw and preparing a prepared statement with a hundred questions in values ​​(...), the usual filling of entity objects followed by context.SaveChanges ().

I note - the right decision. And the premature optimizations are known to be evil.

What came with this approach

I smell the smell of burning. Immediately run to the kitchen, take out the meat from the patch. I cut the burnt sword with a two-handed sword and immediately swallow it, as the Coal sticks into the room. Hot! But portray.
- What is it, Master?
- The steamy kiten. The latest fashion!
- Do not fill in - you know about fashion only from the dictionary, - it tries. - Although really delicious!


It was smooth on paper ... Unique constraints far from all tsiferki agreed to eat.

The amazing fact is that if the entity framework gets a database level exception, then the running boar becomes a pose on the recommendation of the microsoftware, we have only one way to shoot this context and create the next one. In this case, this means that you need to pull the master from the old context, or re-create it, and preferably not by copying all the fields in catch ().

And of course, to make it more interesting - you cannot mix objects from different contexts.

The re-creation backup in many cases turned out to be non-trivial, and how the Malays will support these hacks is a big question.

And at this moment these sinister brought an iron and a soldering iron! reached me.

What had to be done

Karapet is outraged.
- Harmful! They asked for a hundred kilograms of energy, but how much! You tell me so and say - you need a lot, why deceive Karapet? I don’t feel sorry, we need five tons - say so, Karapet, give us five tons ... They are harmful to the wickedness!

As it turned out, the entity CancelChanges in the entity framework is not provided. And its presence would give a chance not to complicate.

As it is easy to guess, it was necessary to invent.

To begin with, we define the approach - rummage through the context, and catch the very sick tooth element that leads to such a sad result. And - throw it out of context. The data of course from this in the database will not appear - but the context will remain working, which is what we need. And with the crooked data let the analysts understand, our task is to write to them - where such was found.

Where to go?

- Afa, pull yourself together!
I clasp myself with hands, get off the floor on biogras.
- Took it. Where to go?


In the examples I focused for insertions as the most obscure. Similarly, it understands and update. I do not bring a footcloth, as it differs little from the above, and it is easier to keep track of updates. Delete we just do not have - this is the paranoia of fellow bankers, how to remove something from the database? No no!

Having smoked manuals and how should google, we define the following data structure:

class MyEntry { public EntityObject entity; // entity object,  public string name; //    EF public Dictionary<string, EntityKey> refmap; // foreign keys -  - ,   //         FK      public Dictionary<string, EntityObject> objmap; //   public Dictionary<EntityObject, string> keymap; //     public MyEntry(string s, EntityObject o) { entity = o; name = s; refmap = new Dictionary<string, EntityKey>(); objmap = new Dictionary<string, EntityObject>(); keymap = new Dictionary<EntityObject, string>(); } } 

Now we can do magic. This is how it is determined - what was added to the context after the last SaveChanges ():

  // t  derived class  EntityContext var added = t.ObjectStateManager.GetObjectStateEntries(EntityState.Added); 

so that we can now determine what it was, and pass it on to us for detailed examination. Like that.

  List<MyEntry> allDataToProceed = new List<MyEntry>(); //     foreach (var a in added) { //  EF        .  //      if (!(a.IsRelationship)) { //   -    "Foo", Foo a MyEntry e = new MyEntry(a.EntitySet.Name, a.Entity); allDataToProceed.Add(e); //     foreign keys    IEnumerable<IRelatedEnd> relEnds = ((IEntityWithRelationships)a.Entity).RelationshipManager.GetAllRelatedEnds(); foreach (var rel in relEnds) { //     FKs List<EntityObject> fks = new List<EntityObject>(); foreach (var obj in rel) fks.Add((EntityObject)obj); //         -  var relname = rel.RelationshipName; if (fks.Count == 1) { //   -    ,   if (fks[0].EntityKey.EntityKeyValues != null) e.refmap[relname] = fks[0].EntityKey; else { //     -  , //    .  -   //      //       FK     e.keymap[fks[0]] = fks[0].EntityKey.EntitySetName; e.objmap[relname] = fks[0]; } } } } } 

The only thing left to do is to bring the context back to life.

  //       foreach (var a1 in added) a1.Delete(); t.SaveChanges(); 

and start an exorcism session - and what is it that we have here come up with such a strange thing, and most importantly - where to put it.

Do not yell at home either

From the pressure of the screen bursts. I take a broom and collect fragments - I am the leader of the expedition. Angle wants to say something - what is his squeaky voice at a depth of three kilometers. Telling him
- Do not yell at home.
Thinking, add
- And not at home, too, do not yell.


The first thing we need to do is take into account foreign keys. If any object is created as part of a master-slave chain, then you don’t need to add the master and the slave separately, otherwise you can break the referral integrity.

  //           FK //       List<EntityObject> usedInRefs = new List<EntityObject>(); foreach (var a1 in allDataToProceed) { foreach (var dup in a1.objmap.Values) usedInRefs.Add(dup); } //   for (int j = 0; j < allDataToProceed.Count; ++j) { if (usedInRefs.Contains(allDataToProceed[j].entity)) { allDataToProceed.RemoveAt(j); --j; } } 

And finally, things are easy - it remains to stuff in the database what you can stuff. To do this, restore our copy of foreign keys and add it to the context. Succeeded? fine, no - that means this is our bad tooth.

  //     -      foreach (var a1 in allDataToProceed) { try { //    FKs  master/slave IEnumerable<IRelatedEnd> relEnds = ((IEntityWithRelationships)a1.entity).RelationshipManager.GetAllRelatedEnds(); foreach (var rel in relEnds) { var relname = rel.RelationshipName; EntityKey key = null; //          EF //         if (a1.refmap.ContainsKey(relname)) key = a1.refmap[relname]; EntityObject o = key != null ? o = (EntityObject)t.GetObjectByKey(key) : null; //     -      // master/slave  if (o == null && a1.objmap.ContainsKey(relname)) { o = a1.objmap[relname]; if (a1.keymap.ContainsKey(o)) t.AddObject(a1.keymap[o], o); else o = null; } //    -    if (o != null) rel.Add(o); } //       t.AddObject(a1.name, a1.entity); t.SaveChanges(); } catch (Exception e2) { //        -     //      added = t.ObjectStateManager.GetObjectStateEntries(EntityState.Added); foreach (var a2 in added) a2.Delete(); //   -    ,      } } 

Voila We did it!

Perhaps this approach will be useful to someone.