EF Core SQLITE - SQLite Error 19: 'UNIQUE constraint failed

Question

I am learning entity framework core, using it with SQLITE I have 2 tables in the database.

Messages:

CREATE TABLE `Messages` (
    `Id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `IsDeleted` INTEGER NOT NULL,
    `FromUserId`    INTEGER,
    `ToUserId`  INTEGER,
    `SendDate`  TEXT NOT NULL,
    `ReadDate`  TEXT NOT NULL,
    `MessageContent`    TEXT,
    CONSTRAINT `FK_Messages_Users_ToUserId` FOREIGN KEY(`ToUserId`) REFERENCES `Users`(`Id`) ON DELETE RESTRICT,
    CONSTRAINT `FK_Messages_Users_FromUserId` FOREIGN KEY(`FromUserId`) REFERENCES `Users`(`Id`) ON DELETE RESTRICT
);

and Users table:

CREATE TABLE `Users` (
    `Id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `IsDeleted` INTEGER NOT NULL,
    `Name`  TEXT,
    `DisplayName`   TEXT,
    `Gender`    INTEGER NOT NULL,
    `BirthDate` TEXT NOT NULL
);

my c# classes look like:

public class User
{
    public int Id {get;set;}
    public bool IsDeleted{get;set;}
    [Required]
    public string Name{get;set;}

    public string DisplayName{get;set;}

    [Required]
    public char Gender{get;set;}

    [Required]
    public DateTime BirthDate{get;set;}

    public User(string n, string dn, char g, DateTime bd)
    {
        Name=n; DisplayName = dn; Gender = g; BirthDate = bd;
    }

    protected User(){}

    public override string ToString()
    {
        return string.Format("{0} ({1}) {2}", this.Name, this.Gender,this.BirthDate.ToShortDateString());
    }
}

and Messages class:

public class Message 
{
    public int Id{get;set;}
    public bool IsDeleted{get;set;}
    [Required]
    public Users.User FromUser{get;set;}
    
    [Required]
    public Users.User ToUser{get;set;}

    [Required]
    public DateTime SendDate{get;set;}
    public DateTime? ReadDate{get;set;}

    [Required]
    public string MessageContent{get;set;}

    protected Message(){}

    public Message(User from, User to, string content)
    {
        this.FromUser = from;
        this.ToUser = to;
        this.MessageContent = content;
        this.SendDate = DateTime.Now;
        this.ReadDate = DateTime.Now;
    }

}

users table works, but when I try to add a new entity to the messages by this:

public override int Insert(Message entity)
{
    dbc.Messages.Add(entity);
    dbc.SaveChanges();
    return entity.Id;
}

I get the following error:

SQLite Error 19: 'UNIQUE constraint failed

I have no idea what is wrong. When I manually inserted data to a database (using DB browser for sqlite) it works. Are the relationships in the classes ok?

My dbContextSnapshot is:

[DbContext(typeof(RandevouDbContext))]
partial class RandevouDbContextModelSnapshot : ModelSnapshot
{
    protected override void BuildModel(ModelBuilder modelBuilder)
    {
#pragma warning disable 612, 618
    modelBuilder.HasAnnotation("ProductVersion", "2.1.4-rtm-31024");

    modelBuilder.Entity("RandevouData.Messages.Message", b =>
        {
            b.Property<int>("Id")
                .ValueGeneratedOnAdd();

            b.Property<int>("FromUserId");

            b.Property<bool>("IsDeleted");

            b.Property<string>("MessageContent")
                .IsRequired();

            b.Property<DateTime?>("ReadDate");

            b.Property<DateTime>("SendDate");

            b.Property<int>("ToUserId");

            b.HasKey("Id");

            b.HasIndex("FromUserId");

            b.HasIndex("ToUserId");

            b.ToTable("Messages");
        });

    modelBuilder.Entity("RandevouData.Users.User", b =>
        {
            b.Property<int>("Id")
                .ValueGeneratedOnAdd();

            b.Property<DateTime>("BirthDate");

            b.Property<string>("DisplayName");

            b.Property<char>("Gender");

            b.Property<bool>("IsDeleted");

            b.Property<string>("Name")
                .IsRequired();

            b.HasKey("Id");

            b.ToTable("Users");
        });

    modelBuilder.Entity("RandevouData.Messages.Message", b =>
        {
            b.HasOne("RandevouData.Users.User", "FromUser")
                .WithMany()
                .HasForeignKey("FromUserId")
                .OnDelete(DeleteBehavior.Cascade);

            b.HasOne("RandevouData.Users.User", "ToUser")
                .WithMany()
                .HasForeignKey("ToUserId")
                .OnDelete(DeleteBehavior.Cascade);
        });
#pragma warning restore 612, 618
}

By the way. I cannot configure mapping from two sides.

The Message entity has UserFrom and UserTo fields, but the User entity can not have Messages, because one time he is "userFrom" and other time he can be "userTo".

And there is also a code, where i create Message entity

        public int SendMessage(int senderId, int receiverId, string content)
    {
        var dao = new MessagesDao();
        var usersDao = new UsersDao();
        var userService = new UserService(mapper);
        
        var sender = usersDao.Get(senderId);
        var receiver = usersDao.Get(receiverId);
        
        var entity = new Message(sender,receiver,content);
        var id = dao.Insert(entity);
        return id;
    }

user dao where i override GET method

 public override User Get(int id)
        {
            using (var context = new RandevouDbContext())
            { 
                var user = dbc.Find<User>(id);
                return user;
            }
        }

and MessagesDao where i override Add method

 public override int Insert(Message entity)
        {
            using (var dc = new RandevouDbContext())
            {
                dc.Messages.Add(entity);
                dc.SaveChanges();
                return entity.Id;
            }
        }

btw, i dont know if it is ok, but i have 0 entries to update (!?)

Answer 1

To fix 'UNIQUE' constraint exceptions you need to make sure all the entities you're working with are being tracked by the context you want to save to.

e.g.

// Get user from one context

User thisUser = db.Users.Find(3);

//Now imagine you have another context which has been created later on

DbContext newContext = new DbContext();

//and you want to create a message, assign it to your user 
//and save it to the new context

Message newMessage = new Message
{
    Text = "Hello",
    User = thisUser
}

newContext.Messages.Add(newMessage);

// saving this to newContext will fail because newContext is not tracking thisUser,
// Therefore attach it...

newContext.Users.Attach(thisUser).

//now newContext is tracking thisUser *AND* newMessage, so the save operation will work

newContext.SaveChanges();

Answer 2

Ok, i solved problems. Read some articles about EF DbContext. When i have used two other context's for GET User and CREATE message, the entities (user1 and user2) were not the same entities which i have used for second context. I mean, the entiies were this same, but they had another tracking

EF Core / Sqlite one-to-many relationship failing on Unique Index Constraint

So i solved problem by using one context on one Business-Transaction (not one database transaction). Just moved creating context to business logic, and the Generic dao class was waiting for context in constructor, like this:

public class MessagesDao : BasicDao<Message>
    {
        public MessagesDao(RandevouDbContext dbc) : base(dbc)
        {

        }
        public IQueryable<Message> QueryMessages()
        {
                return dbc.Messages.AsQueryable();

        }

        public override int Insert(Message entity)
        {
            dbc.Messages.Add(entity);
            dbc.SaveChanges();
            return entity.Id;

        }
    }



 public abstract class BasicDao<TEntity> where TEntity : BasicRandevouObject
    {
        protected RandevouDbContext dbc;
        public BasicDao(RandevouDbContext dbc) { this.dbc = dbc; }

        public virtual int Insert(TEntity entity)
        {
                dbc.Add<TEntity>(entity);
                dbc.SaveChanges();
                return entity.Id;

        }

        public virtual void Update(TEntity entity)
        {
                dbc.Update(entity);
                dbc.SaveChanges();

        }

        public virtual void Delete(TEntity entity)
        {
                dbc.Remove(entity);
                dbc.SaveChanges();

        }

        public virtual TEntity Get(int id)
        {
                var entity = dbc.Find<TEntity>(id);
                return entity;

        }


    }

and in business-logic code :

public int SendMessage(int senderId, int receiverId, string content)
        {
            using (var dbc = new RandevouDbContext())
            { 
                var dao = new MessagesDao(dbc);
            var usersDao = new UsersDao(dbc);
            var userService = new UserService(mapper);

            var sender = usersDao.Get(senderId);
            var receiver = usersDao.Get(receiverId);

            var entity = new Message(sender,receiver,content);
            var id = dao.Insert(entity);
            return id;
            }
        }

now everything works. By the way it is still dirty code, because i am just trying some things, but i hope if someone will have similiar problem then he will find this post.

Answer 3

What's missing from your (very detailed) question is the makeup of the Message you're passing to Insert(). Have you previously saved the two Users to the database, or are they two new objects, too? If they are new then their ID should be zero so that EF knows to create them first, prior to creating the Message.