Databases
StackOverflow2013
Postsdbo
POProblem with EF STE and Self-Referencing tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POProblem with EF STE and Self-Referencing tables
    primarykey
    data
    text
    <p>This is my first post here, so I hope everything is fine.</p> <p>Here is my problem: I have a table in my database called <strong>UserTypes</strong>. It has:</p> <ol> <li>ID;</li> <li>IsPrivate;</li> <li>Parent_ID;</li> </ol> <p>The relevant ones are the first and the third one. I have another table called <strong>UserTypes_T</strong> which has information for the different types, that is language specific. The fields are:</p> <ol> <li>Language_ID;</li> <li>UserType_ID;</li> <li>Name;</li> </ol> <p>What I'm trying to achieve is load the entire hierarchy from the <strong>UserTypes</strong> table and show it in a TreeView (this is not relevant for now). Then, by selecting some of the user types I can edit them in separate edit box (the name) and a combo box (the parent).</p> <p>Everything works fine until I try to persist the changes in the database. EF has generated for me two entity classes for those tables:</p> <p>The class for the user types has:</p> <ol> <li>ID;</li> <li>IsPrivate;</li> <li>Parent_ID;</li> <li>A navigational property for the self-reference (0..1);</li> <li>A navigational property for the child elements;</li> <li>Another navigational property for the UserTypes_T table (1..*);</li> </ol> <p>The class for the translated information has:</p> <ol> <li>UserType_ID;</li> <li>Language_ID;</li> <li>Name;</li> <li>A navigational property to the UserTypes table (*..1);</li> <li>A navigational property to the Languages table (*..1);</li> </ol> <p>I get the data I need using:</p> <pre><code>return context.UserTypes.Include("UserTypes_T").Where(ut =&gt; ut.IsPrivate==false).ToList(); </code></pre> <p>in my WCF Web service. I can add new user types with no problems, but when I try to update the old ones, some strange things happen.</p> <p>If I update a root element (Parent_ID==null) everything works! If I update an element where Parent_ID!=null I get the following error:</p> <blockquote> <p>AcceptChanges cannot continue because the object’s key values conflict with another object in the ObjectStateManager.</p> </blockquote> <p>I searched all over the internet and read the blog post from <a href="http://blogs.msdn.com/b/diego/archive/2010/10/06/self-tracking-entities-applychanges-and-duplicate-entities.aspx" rel="nofollow">Diego B Vega</a> (and many more) but my problem is different. When I change a parent user type, I actually change the Parent_ID property, not the navigational property. I always try to work with the IDs, not the generated navigational properties in order to avoid problems.</p> <p>I did a little research, tried to see what is the object graph that I get and saw that there were lots of duplicate entities:</p> <p>The root element had a list of its child elements. Each child element had a back reference to the root or to its parent and so on. You can imagine. As I wasn't using those navigational properties, because I used the IDs to get/set the data I needed, I deleted them from the model. To be specific I deleted points <strong>4</strong> and <strong>5</strong> from the <strong>UserTypes</strong> entity class. Then I had an object graph with each element only once. I tried a new update but I had the same problem:</p> <p>The root element was updated fine, but the elements, that had some parents, threw the same exception.</p> <p>I saw that I had a navigational property in the <strong>UserTypes_T</strong> entity class, pointing to a user type, so I deleted it too. Then this error disappeared. All the items in the object graph were unique. But the problem remained - I could update my root element with no problems, but when trying to update the children (with no exclusions) I got a null reference exception in the generated Model.Context.Extensions class:</p> <pre><code>if (!context.ObjectStateManager.TryGetObjectStateEntry(entityInSet.Item2, out entry)) { context.AddObject(entityInSet.Item1, entityInSet.Item2);//here! } </code></pre> <p>I tried to update only the name (which is in <strong>UserTypes_T</strong>) but the error is the same.</p> <p>I'm out of ideas and I've been trying to solve this problem for 8 hours now, so I'll appreciate if someone gives me ideas or share their experience.</p> <p>PS:</p> <p>The only way I succeeded updating a child object was using the following code to retrieve the data:</p> <pre><code>var userTypes = argoContext.UserTypes.Include("UserTypes_T").Where(ut =&gt; ut.IsPrivate==false).ToList(); foreach (UserType ut in userTypes) { ut.UserType1 = null; ut.UserTypes1 = null; } return userTypes; </code></pre> <p>where <strong>UserType1</strong> is the navigational property, pointing to the parent user type and <strong>UserTypes1</strong> is the navigational property, holding a list of the child element. The problem here was that EF "fixups" the objects and changes the <strong>Parent_ID</strong> to <em>null</em>. If I set it back again, EF sets the <strong>UserTypes1</strong>, too... Maybe there is a way to stop this behavior?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USIvan Nikolov
    1. USIvan Nikolov
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POProblem with EF STE and Self-Referencing tables
      1. USIvan Nikolov
      1. VTFavorite
    2. VO
      singulars
      1. POProblem with EF STE and Self-Referencing tables
      1. USbeastie
      1. VTFavorite
    3. VO
      singulars
      1. POProblem with EF STE and Self-Referencing tables
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload