Entity Framework is trying to insert into wrong table?

.net c# entity-framework entity-framework-core sql

Question

I've built a test application with Entity Framework to simulate a database that contains friends lists.

I want the database to store the user's ID's and when I retrieve them (the "AcceptedFriends") I want Entity Framework to also return the friends "usermodel".

But every time I try to add 2 users as friends to the "AcceptedFriends" table it throws an exception:

" Violation of PRIMARY KEY constraint 'PK_Users'. Cannot insert duplicate key in object 'dbo.Users'. The duplicate key value is (GUID value of a user's ID) "

Some attempted solutions:

  1. Solution 1

  2. Attempting to create 2 lists of the same friend list (received, sent) but that defeats the purpose of what I am trying to achieve.

  3. Solution 2

Here are the code files:

"Users Model"

 public class Users
 {

    #region Private fields

    #endregion


    #region Public properties

    public string Username { get; set; }

    public string ID { get; set; }

    public string PasswordHash { get; set; }


    public virtual List<AcceptedFriends> AcceptedFriendsList { get; set; }

    // public virtual List<PendingFriends> PendingFriendsList { get; set; }

    // public virtual  List<RemovedFriends> RemovedFriendsList { get; set; }

    #endregion
}

"Accepted Friends model"

    public class AcceptedFriends
    {
        #region Public properties

        public string RelationKey { get; set; }

        public string RequestSenderID { get; set; }

        public string RequestReceiverID { get; set; }


        public virtual List<Messages> ChatList { get; set; }


        public Users RequestSender { get; set; }

        public Users RequestReceiver { get; set; }

        #endregion
     }

"Database model creation"

        #region Users table

        // Create primary key in Users table
        modelBuilder.Entity<Users>().HasKey(property => property.ID);

        // Map Username to be unique
        modelBuilder.Entity<Users>().HasIndex(property => property.Username).IsUnique();

        // Create a one to many relation with AcceptedFriends table
        modelBuilder.Entity<Users>()
            .HasMany(property => property.AcceptedFriendsList)
            .WithOne(property => property.RequestReceiver)
            .HasForeignKey(property => property.RequestReceiverID);


        #endregion


        #region Accepted friends table

        // Create key for AcceptedFriends
        modelBuilder.Entity<AcceptedFriends>().HasKey(property => property.RelationKey);

        #endregion

Edit

Here is how I am inserting the friends

public static void AddFriends(AcceptedFriends friends)
{
    using(Context context = ConnectToDatabase())
    {
        context.AcceptedFriends.Add(friends);

        context.SaveChanges();
    };
 }

Edit 2

Here is where I add the friends/users

Plus I've noticed another odd behaviour When I add new users to the friends table without adding them to the users table first it adds them both to the friends table and users table.

        Console.WriteLine("Connecting to database");

        DB.ConnectToDatabase();
        Console.WriteLine("Connected to database successfully");

        List<Users> userList = new List<Users>(DB.GetUsersList());

        List<AcceptedFriends> friendsCount = new List<AcceptedFriends>(DB.GetAcceptedFriends());


        if(userList.Count != 2)
        {
            DB.AddUser(new Users()
            {
                Username = "User1",
                PasswordHash = "PasswordHash",
            });

            DB.AddUser(new Users()
            {
                Username = "User2",
                PasswordHash = "PasswordHash",
            });

            userList = new List<Users>(DB.GetUsersList());
        };


        if(friendsCount.Count < 1)
        {
            Users user1 = userList[0];
            Users user2 = userList[1];

            DB.AddFriends(new AcceptedFriends()
            {
                RequestReceiver = user2,

                RequestSender = user1,
            });
        };

        Console.WriteLine("Server is great success!");

        Console.ReadLine();

Edit 3

I might have found a solution. It does return the models both for the user and friends, But I can't accept this as a solution yet because it feels to hackey(?) for me

(Thanks to @wertzui, You helped me to get to this solution)

Basically everytime a new context is created it sets up the the friends and users to return thier usermodels

/// <summary>
    /// Gets the friends user models
    /// </summary>
    /// <param name="context"> The database context that was created </param>
    private static void SetupFriends(Context context)
    {
        // For every "AcceptedFriend"
        foreach(AcceptedFriends friend in context.AcceptedFriends)
        {
            // Get sender and receiver usermodels 
            // by matching ID's
            Users sender = context.Users.FirstOrDefault(user => user.ID == friend.RequestSenderID);
            Users receiver = context.Users.FirstOrDefault(user => user.ID == friend.RequestReceiverID);

            sender.AcceptedFriendsList.Add(friend);
            receiver.AcceptedFriendsList.Add(friend);

            friend.RequestSender = sender;
            friend.RequestReceiver = receiver;
        };
    }
1
2
10/17/2018 9:23:28 AM

Popular Answer

When you create new User Instances in your new AcceptFriends {...} Code, you are not setting their Id, so they keep their default which is 0. Now Entity Framework thinks, that you want to create a new Friendship with 2 new Users. Instead you should populate them with the Users, you created earlier.

if(friendsCount.Count < 1)
{
    Users user1 = userList[0];
    Users user2 = userList[1];

    DB.AddFriends(new AcceptedFriends()
    {
        RequestReceiver = user1,
        RequestSender = user2,
    });
}
0
10/16/2018 3:15:55 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow