Delete using navigation properties in EF 6

Question

I am using EF 6 database modal in my application. I have a table TBL_USER which has 1:N relationship in some other table. One of them is TBL_USER_CASE where primary key of TBL_USER act as foreign key in TBL_USER_CASE.

Now i am deleting some user from TBL_USER. Before that i need to delete corresponding entries in TBL_USER_CASE. I am using following code for that

 private long DeleteUser(long UserID)
    {
        using(VerbaTrackEntities dataContext = new VerbaTrackEntities())
        {
            TBL_USER user = dataContext.TBL_USER.Where(x => x.LNG_USER_ID == UserID).SingleOrDefault();
            if(user != null)
            {
                foreach (var cases in user.TBL_USER_CASE.ToList())
                {
                    user.TBL_USER_CASE.Remove(cases);                          
                }
            }
            dataContext.SaveChanges();
        }
        return 0;
    }

Here i m getting exception

Additional information: The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted

How can i do this operation correctly ??

Normally the error shows you what the problem is in quite some detail. This error describes that there must be some relational inconstancy after your action: for example a `1:N` relation without the matching `1`. — Stefan, Dec 02 '14 at 07:35
You might want to try the inner exception to determine which field is causing this issue. If the exception is of type DbValidationException check this link to get some more details: http://stackoverflow.com/questions/5345890/getting-exact-error-type-in-from-dbvalidationexception — Stefan, Dec 02 '14 at 07:37
BTW: since your function is called `DeleteUser`; why are you not deleting the user? If that is your goal, you can rely on `cascading deletes` to delete the child relations. — Stefan, Dec 02 '14 at 07:43

Stefan · Answer 1 · 2014-12-02T08:56:03.283

0

Ok, if your goal is to delete the user, you can let the framework handle the child-relations. You can try this:

private long DeleteUser(long UserID)
{
    using(VerbaTrackEntities dataContext = new VerbaTrackEntities())
    {
        TBL_USER user = dataContext.TBL_USER.
                                 SingleOrDefault(x => x.LNG_USER_ID == UserID);
        if(user != null)
        {       
            dataContext.TBL_USER.Remove(user); 
            dataContext.SaveChanges();
        }
    }
    return 0;
}

Update: can you try this one:

private long DeleteUser(long UserID)
{
    using(VerbaTrackEntities dataContext = new VerbaTrackEntities())
    {
        TBL_USER user = dataContext.TBL_USER
                             .SingleOrDefault(x => x.LNG_USER_ID == UserID);
        if(user != null)
        {
            foreach (var cases in user.TBL_USER_CASE.ToList())
            {
                //little modification is here
                dataContext.TBL_USER_CASE.Remove(cases);                          
            }
        }
        dataContext.SaveChanges();
    }
    return 0;
}

edited Dec 02 '14 at 08:56

answered Dec 02 '14 at 07:50

Stefan

17,448
11
60
79

will it not throw the foreign key violation error ? – Rajeev Kumar Dec 02 '14 at 07:53
@RajeevKumar: If you haven't altered any `cascading delete` options, it should delete the foreign-key related entries as well. – Stefan Dec 02 '14 at 07:55
Error Occured `{"The DELETE statement conflicted with the REFERENCE constraint \"USRCAS_USR_FK\". The conflict occurred in database \"VerbaTrack\", table \"dbo.TBL_USER_CASE\", column 'LNG_USER_ID'.\r\nThe statement has been terminated."}` – Rajeev Kumar Dec 02 '14 at 08:39
@RajeevKumar: hmm, I wasn't suspecting that one :| I posted a last try. If it doesn't work, maybe you can post the entities TBL_USER and TBL_USER_CASE as well. – Stefan Dec 02 '14 at 08:56

score 0 · Answer 2 · answered Dec 02 '14 at 09:17

I managed to do so himself by reading through the net. I have done this by

 System.Data.Entity.Core.Objects.ObjectContext oc = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)dataContext).ObjectContext;
 foreach(var Cases in user.TBL_USER_CASE.ToList())
    {                        
       oc.DeleteObject(Cases);                       
    }                    
    oc.SaveChanges();
    dataContext.TBL_USER.Remove(user);

Delete using navigation properties in EF 6

2 Answers2