locked
DataServiceCollection - Remove() fails with xref table - Simple scenario with lots of pictures RRS feed

  • Question

  • I've created a very simple scenario that illustrates the problem.

    The DB

    The EF

    Working fine from EF directly

    	    TestEntities ctx = new TestEntities();
                
                // Add a Brain to Mike
                Person mike = ctx.People.Where(p => p.Name == "Mike").First();
                Thing brain = ctx.Things.Where(t => t.Name == "Brain").First();
    
                mike.Things.Add(brain);
    
                ctx.SaveChanges();  // Works just fine
    
                // Delete Mike's Brain
                mike = ctx.People.Include("Things").Where(p => p.Name == "Mike").First();
                brain = mike.Things.Where(t => t.Name == "Brain").First();
                mike.Things.Remove(brain);
               
                ctx.SaveChanges();  // Works just fine                     

    Exposing as a WCF Data Service

        [ServiceBehavior(IncludeExceptionDetailInFaults = true)]
        public class WcfDataService1 : DataService<TestEntities>
        {            
            public static void InitializeService(DataServiceConfiguration config)
            {
                config.UseVerboseErrors = true;
                config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
                
                config.SetEntitySetAccessRule("PersonSet", EntitySetRights.All);
                config.SetEntitySetAccessRule("ThingSet", EntitySetRights.All);
            }
        }

    Again, working just fine client side

    	    TestEntities ctx = new TestEntities(new Uri("http://localhost:2061/WcfDataService1.svc/"));
                
                // Add a Brain to Mike
                Person mike = ctx.PersonSet.Where(p => p.Name == "Mike").First();
                Thing brain = ctx.ThingSet.Where(t => t.Name == "Brain").First();
                
                ctx.AddLink(mike, "Things", brain);
                ctx.SaveChanges();  // Works just fine
    
                // Delete Mike's Brain
                // Recreate ctx to start fresh
                ctx = new TestEntities(new Uri("http://localhost:2061/WcfDataService1.svc/"));            
                mike = ctx.PersonSet.Expand("Things").Where(p => p.Name == "Mike").First();
                brain = mike.Things.Where(t => t.Name == "Brain").First();
    
                ctx.DeleteLink(mike, "Things", brain);
    
                ctx.SaveChanges();  // Works just fine        

    Failing from DataServiceCollection

    	    TestEntities ctx = new TestEntities(new Uri("http://localhost:2061/WcfDataService1.svc/"));
                DataServiceCollection<Person> people = new DataServiceCollection<Person>(ctx);
    
                // Add a Brain to Mike
                Person mike = ctx.PersonSet.Where(p => p.Name == "Mike").First();
                Thing brain = ctx.ThingSet.Where(t => t.Name == "Brain").First();
    
                people.Add(mike);
                mike.Things.Add(brain);
                
                ctx.SaveChanges();  // Works just fine
    
                // Delete Mike's Brain
                // Recreate ctx and people to start fresh
                ctx = new TestEntities(new Uri("http://localhost:2061/WcfDataService1.svc/"));
                people = new DataServiceCollection<Person>(ctx);
    
                mike = ctx.PersonSet.Expand("Things").Where(p =>p.Name == "Mike").First();
                brain = mike.Things.Where(t => t.Name == "Brain").First();
    
                people.Add(mike);
                mike.Things.Remove(brain);
    
                // The DELETE statement conflicted with the REFERENCE constraint "FK_PersonThing_Thing". 
                //The conflict occurred in database "Test", table "dbo.PersonThing", column 'Thing_id'
                ctx.SaveChanges();  

    The DataServiceCollection is incorrectly issuing the DELETE on the Things table instead of PersonThing.  I can verify this with Fiddler if necessary but it's fairly obvious.,

    How to fix this problem?


    • Edited by MMichael417 Sunday, January 12, 2014 5:39 PM
    Sunday, January 12, 2014 5:37 PM

Answers

  • I have found a work around.  ctx.DeleteLink() must be called from wthin the DataServiceCollection<> EntityChangedCallback.  If DeleteLink() is called outside this callback, the Person entity will be left in an invalid state still containing a reference to the brain once the link has been deleted.  If called from within the callback, the link will be deleted and in addition the DataServiceCollection will automatically remove the brain from mike.Things() collection.

    class Program { private static testEntities ctx = null; static void Main(string[] args) { ctx = new testEntities(new Uri("http://localhost:8748/WcfDataService1.svc/")); var people = new DataServiceCollection<Person>(ctx, "PersonSet", EntityChangedCallback, CollectionChangedCallback); var mike = ctx.PersonSet.Where(p => p.Name == "Mike").First(); var brain = ctx.ThingSet.Where(t => t.Name == "Brain").First(); people.Add(mike); mike.Things.Add(brain); ctx.SaveChanges(); // Simply issuing // ctx.DeleteLink(mike, "Things", brain); // will not work because the brain is still left in the collection of Mike.Things mike.Things.Remove(brain); ctx.SaveChanges(); } private static bool CollectionChangedCallback(EntityCollectionChangedParams arg) { if (arg.Action == System.Collections.Specialized.NotifyCollectionChangedAction.Remove && arg.PropertyName == "Things" && arg.SourceEntity.GetType() == typeof(Person)) { // calling mike.Things.Remove(brain) // will lead us here where we can issue the proper ctx.DeleteLink ctx.DeleteLink(arg.SourceEntity, "Things", arg.TargetEntity); // In addition, the brain will also be properly removed from mike.Things by returning true return true; } else {

    return false; } } private static bool EntityChangedCallback(EntityChangedParams arg) { return false; } }



    Monday, January 13, 2014 4:15 PM

All replies

  • Hello MMichael417,

    >>   // The DELETE statement conflicted with the REFERENCE constraint "FK_PersonThing_Thing".            //The conflict occurred in database "Test", table "dbo.PersonThing", column 'Thing_id'

    The problem has something to do with referential integrity, the behavior of the codes below:

    mike.T.Remove(brain);

    It is to delete the brain record in Thing table, however the remove method will not delete the link relationship between Person and Thing, in other word, the mapping record in the middle table dbo.PersonThing, so we need to delete the link relationship firstly as below:

    people.Add(mike);
    
    ctx.DeleteLink(mike, "T", brain);
    
    mike.T.Remove(brain);
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 13, 2014 6:59 AM
    Moderator

  • The code mike.Things.Remove(brain) should not issue a DELETE statement on the Things table.  This should only delete the xref record in PersonThing.

    This follows exactly how the EF code will function.

    If I were trying to issue the statement ctx.Things.Remove(brain), then your solution would be valid, however I am not trying to delete the record in the Thing table, I am trying only to delete the xref record in PersonThing.

    For example, if we add a second person into the scenario called Fred and add a brain to Fred, it becomes clear now that trying to delete the brain from Mike should not delete the Thing record, thus your solution of first deleting the link (or technically the xref record) would not work because it would be necessary to delete EVERY link before issuing the command mike.Things.Remove(brain).


    • Edited by MMichael417 Monday, January 13, 2014 3:10 PM
    Monday, January 13, 2014 12:47 PM
  • I have found a work around.  ctx.DeleteLink() must be called from wthin the DataServiceCollection<> EntityChangedCallback.  If DeleteLink() is called outside this callback, the Person entity will be left in an invalid state still containing a reference to the brain once the link has been deleted.  If called from within the callback, the link will be deleted and in addition the DataServiceCollection will automatically remove the brain from mike.Things() collection.

    class Program { private static testEntities ctx = null; static void Main(string[] args) { ctx = new testEntities(new Uri("http://localhost:8748/WcfDataService1.svc/")); var people = new DataServiceCollection<Person>(ctx, "PersonSet", EntityChangedCallback, CollectionChangedCallback); var mike = ctx.PersonSet.Where(p => p.Name == "Mike").First(); var brain = ctx.ThingSet.Where(t => t.Name == "Brain").First(); people.Add(mike); mike.Things.Add(brain); ctx.SaveChanges(); // Simply issuing // ctx.DeleteLink(mike, "Things", brain); // will not work because the brain is still left in the collection of Mike.Things mike.Things.Remove(brain); ctx.SaveChanges(); } private static bool CollectionChangedCallback(EntityCollectionChangedParams arg) { if (arg.Action == System.Collections.Specialized.NotifyCollectionChangedAction.Remove && arg.PropertyName == "Things" && arg.SourceEntity.GetType() == typeof(Person)) { // calling mike.Things.Remove(brain) // will lead us here where we can issue the proper ctx.DeleteLink ctx.DeleteLink(arg.SourceEntity, "Things", arg.TargetEntity); // In addition, the brain will also be properly removed from mike.Things by returning true return true; } else {

    return false; } } private static bool EntityChangedCallback(EntityChangedParams arg) { return false; } }



    Monday, January 13, 2014 4:15 PM