Many to Many relationship not updating properly in Sqlite-Extensions

Question

I'm creating a chat app in Android using sqlite-extensions for creating relationships between database tables. Here's the wall of code to set the stage:

Conversation Table

public class Conversation
{
    [PrimaryKey, Unique, AutoIncrement]
    public int ID { get; set; }
    public string toUser { get; set; }
    public string FromUser { get; set; }

    [ManyToMany(typeof(ChatConversation), CascadeOperations = CascadeOperation.All)]
    public List<ChatMessage> ChatMessages { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<ConversationDeleted> DeletedConversations { get; set; }
    public DateTime CreatedAt { get; set; }

    public int UserID { get; set; }


}

ChatMessage Table

    public class ChatMessage
{
    [PrimaryKey, Unique, AutoIncrement]
    public int ChatMessageID { get; set; }
    public string fromUser { get; set; }
    public string toUser { get; set; }
    public string Message { get; set; }
    public bool DeliveryStatus { get; set; }
    public DateTime CreatedAt { get; set; }

    [Unique, AutoIncrement, NotNull]
    public Guid UniqueID { get; set; }

    [ManyToMany(typeof(ChatConversation), CascadeOperations = CascadeOperation.All)]
    public List<Conversation> Conversation { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<MessageDeleted> MessagesDeleted { get; set; }
    public int UserId { get; set; }

}

Intermediated table for chat and conversation table relationship

    class ChatConversation
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [ForeignKey(typeof(Conversation))]
    public int ConversationID { get; set; }

    [ForeignKey(typeof(ChatMessage))]
    public int ChatID { get; set; }
}

When I message a user, it creates a new conversation cascaded with a chat message object. When I login to the other user's account on the same device, a new conversation is created and that same chat message is cascaded with it. Now the issue is, after the user receives the message, the relationship that was formed between the conversation created by the first user and the chat message is no longer there. Here the user sends a message:

            string messageText = messageET.Text.ToString();
            if (TextUtils.IsEmpty(messageText))
            {
                return;
            }
            var member = dbr.GetMember(loggeduser);
            var uniqueID = Guid.NewGuid();
            proxySubscriber.InvokeSendMessage(user, messageText, uniqueID);
            ChatMessage chatMessage = new ChatMessage();
            chatMessage.Message = messageText;
            chatMessage.fromUser = loggeduser;
            chatMessage.toUser = user;
            chatMessage.CreatedAt = DateTime.Now;
            chatMessage.UniqueID = uniqueID;
            chatMessage.UserId = member.UserID;
            dbr.insertUpdateChat(chatMessage);
            var duplicat_chat_msg = dbr.GetChat().ToList().Where(u => u.UniqueID == uniqueID).FirstOrDefault();
            List<ChatMessage> msg_list = new List<ChatMessage>();
            msg_list.Add(duplicat_chat_msg);
            var update_conv = dbr.GetConversations().Where(f => f.FromUser == loggeduser && f.toUser == user && f.UserID == member.UserID).FirstOrDefault();
            var deleted_conversation = dbr.GetConversationDeleted().Where(d => d.UserID == member.UserID && d.ConversationID == update_conv.ID).FirstOrDefault();
            bool HasConversation = false;
            if (deleted_conversation != null)
            {
                HasConversation = true;
            }

            if (update_conv != null && !HasConversation)
            {
                update_conv.ChatMessages.Add(duplicat_chat_msg);
                dbr.UpdateConversationData(update_conv);
            }
            else
            {
                Conversation conv = new Conversation { ChatMessages = msg_list, CreatedAt = DateTime.Now, FromUser = loggeduser, toUser = user, UserID = member.UserID };
                dbr.insertConversationData(conv);
            }
            displayMessage(chatMessage);
            messageET.Text = "";
        };

Here the user receives the chat:

var member = dbr.GetMember(loggedonuser);
            chat_msg.DeliveryStatus = true;
            var chat = dbr.GetChat().ToList().Where(c => c.UniqueID == chat_msg.UniqueID).FirstOrDefault();
            if (chat == null)
            {
                dbr.insertUpdateChat(chat_msg);
            }
            var conversation = dbr.GetConversations().Where(f => f.toUser == chat_msg.toUser && f.FromUser == chat.fromUser && f.UserID == member.UserID).FirstOrDefault();
            var deleted_conversation = dbr.GetConversationDeleted().Where(d => d.UserID == member.UserID && d.ConversationID == conversation.ID).FirstOrDefault();
            bool HasConversation = false;
            if (deleted_conversation != null)
            {
                HasConversation = true;

            }


            if (conversation == null && !HasConversation)
            {
                Conversation conv = new Conversation { toUser = chat_msg.toUser, FromUser = chat_msg.fromUser, CreatedAt = DateTime.UtcNow, UserID = member.UserID };
                conv.ChatMessages = new List<ChatMessage> { chat_msg };
                dbr.insertConversationData(conv);

            }
            else
            {
                conversation.ChatMessages.Add(chat_msg);
                dbr.UpdateConversationData(conversation);
            }

When the sender creates a conversation, a relationship is formed between conversation and chat message sent but when the second user receives the message, this relationship is broken and a new relationship is formed between the conversation created by the receiver of the message. With the relationships being many to many, this shouldn't be happening. Here's where the conversation is inserted into the db:

        public string insertConversationData(Conversation data)
    {
        try
        {

            {
                db.InsertOrReplaceWithChildren(data, recursive:true);
            }


            return "Single data file inserted or updated";
        }
        catch (SQLiteException ex)
        {
            Thread.Sleep(500);
            return insertConversationData(data);
        }
    }

Can someone please look at my code and tell me what is wrong?

Answer 1

You have to take into account that calling UpdateWithChildren will save the current status of the object.

So what you're doing right now is equivalent to:

update_conv.ChatMessages.RemoveAll();
update_conv.ChatMessages.Add(message);

Because ChatMessages is empty by the time you add a new message.

On updating, SQLite-Net Extensions will remove all relationships but that one because that what the new list looks like.

---

Add this line:

update_conv.GetChildren();

After this line:

var update_conv = dbr.GetConversations().Where(f => f.FromUser == loggeduser && f.toUser == user && f.UserID == member.UserID).FirstOrDefault();

The same happens with your second piece of code.