Unable to delete record from database Entity Framework C#

Question

Since adding logging functionality to my Entity Framework project, I have been unable to delete any records from the table.

Here are the data classes for the objects which are added to the database, the user to the user table and the log to the logs table:

public class User
{
    public string UserName { get; set; }

    [Key]
    public string ApiKey { get; set; }  //unique database key and API key for user

    public ICollection<Log> Logs { get; set; }

    public User() { }
}

public class Log
{
    [Key] 
    public int logID { get; set; }
    public string logString { get; set; }
    public string logDateTime { get; set; }
    public string userAPIKey { get; set; }
    public Log() { }       
}

Here is how logs are added to the table, as since adding logging I've been having the issue:

public void addLogToUserWithApiKey(string logMessage, string apiKey)
{
        Log newLog = new Log();
        newLog.logID = makeLogID();
        newLog.logString = logMessage;
        newLog.logDateTime = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToString("h:mm:ss tt");
        newLog.userAPIKey = apiKey;

        using (var context = new UserContext())
        {
            User logUser = checkIfUserExistsWithApiKeyandReturnUser(apiKey);

            if (logUser.Logs == null)
            {
                logUser.Logs = new Collection<Log>();
            }

            logUser.Logs.Add(newLog);
            context.Logs.Add(newLog);

            context.SaveChanges();
        }
}

And finally, this is the code to delete a record:

public void deleteUserFromDatabase(string mApiKey)  
{
        using (var context = new UserContext())
        {
            try
            {
                User userToDelete = checkIfUserExistsWithApiKeyandReturnUser(mApiKey);

                if (userToDelete != null) 
                {
                    context.Users.Attach(userToDelete);
                    context.Users.Remove(userToDelete);
                    context.SaveChanges();
                }
            }
            catch (Exception e) { }
        }
}

There were no exceptions being called when the delete method was like that however it still wasn't working.

I changed the delete method to this:

User userToDelete = checkIfUserExistsWithApiKeyandReturnUser(mApiKey);

if (userToDelete != null) 
{
    if (userToDelete.Logs != null) 
    { 
        userToDelete.Logs.ToList().ForEach(log => userToDelete.Logs.Remove(log)); 
    }

    context.Users.Attach(userToDelete);
    context.Users.Remove(userToDelete);
    context.SaveChanges();
}

And I got this error message:

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.Logs_dbo.Users_User_ApiKey". The conflict occurred in database "SecuroteckWebApplication.Models.UserContext", table "dbo.Logs", column 'userAPIKey'. The statement has been terminated.

OJ Raqueño · Accepted Answer · 2018-04-20 15:53:15Z

2

If you want to delete a User, you would have to delete all Log entries associated with that user first.

var apiKey = String.Empty; // The id of the user you want to delete

using (var context = new UserContext())
{
    User userToDelete = checkIfUserExistsWithApiKeyandReturnUser(apiKey);
    if (userToDelete != null)
    {
        var userLogs = context.Logs.Where(l => l.userAPIKey == apiKey);
        if (userLogs.Any())
        {
            context.Logs.RemoveRange(userLogs);
        }

        context.Users.Attach(userToDelete);
        context.Users.Remove(userToDelete);
        context.SaveChanges();
    }
}

answered Apr 20, 2018 at 15:53

OJ Raqueño

4,5912 gold badges20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jorgonor · Accepted Answer · 2018-04-20 16:26:53Z

1

This error is returned from SQL Server. As it says, you cannot delete the User because there are records from the dbo.Logs table which are related to the deleted user and there is a foreign key defined linking the userAPIKey column with this deleted User.

Seeing your entity code, I can't tell why the foreign key was created in the first place, if you are using Entity Framework Code First. If this is your case, probably you are falling in an Entity Framework convention rule.

Anyways, there are some ways to solve this.

If you are using EF Code First. Delete all the logs pointing to the deleted user or update them setting them to NULL, depending on how much you need to preserve the logs for a deleted user.

EDIT: As the OP is using Code First, then the relationship between Log and User is not completely defined. This is the proper entity code if a strong relationship is what is intended by the OP.

public class User
{
    public string UserName { get; set; }

    [Key]
    public string ApiKey { get; set; }  //unique database key and API key for user

    [InverseProperty("User")]
    public virtual ICollection<Log> Logs { get; set; }

    public User() { }
}

public class Log
{
    [Key] 
    public int logID { get; set; }
    public string logString { get; set; }
    public string logDateTime { get; set; }
    public string userAPIKey { get; set; }
    [ForeignKey("userAPIKey")
    public virtual User User {get; set; }
    public Log() { }
}

With the strong relationship, logs should be deleted or set to null before being able to the delete the user if cascade conventions are not configured.

edited Apr 20, 2018 at 16:26

answered Apr 20, 2018 at 14:48

jorgonor

1,72911 silver badges16 bronze badges

4 Comments

Lucy Copp Over a year ago

I think there may be a problem when adding the logs. I have been stepping through where the log is added to the user, and it looks to be adding to the collection. However, when stepping through the delete method the user logs are always empty!

jorgonor Over a year ago

Are you using EF Code First or EF Database First? These kind of issues have pretty different approachs depending on this, so please tell us what you are actually using.

Lucy Copp Over a year ago

EF Code First .

jorgonor Over a year ago

Look at the edit, probably you are running in an issue because some lacking definition. You should also consider if you want to keep the Logs after the User deletion, depending on your application might be the correct thing to do, but then, a relationship between Log and User might not be a good idea. You can remove the relationship and simply add an unrelated Log entity. Is not a good idea to define relationships in Entity Framework if a foreign key is not the way to go.

Collectives™ on Stack Overflow

Unable to delete record from database Entity Framework C#

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related