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 (!?)

Solution 1

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.

Solution 2

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();

Solution 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.

Comments

[Scott Leckie]

Ah - in that case, have you definitely fetched them and composed the two users into the message field? You should either set the Message._xx_User field to the composed User that was returned from the save request, or do a .Get() to retrieve the composed User records and set them in the Message record.

[SharkyShark]

i dont understand. before i created message, i have did dao.Get<user>(userId) for both users. next, i have created message which takes (user u1, users u2, string content). On user class i dont have message property.

[Scott Leckie]

Sounds like you're doing what I suggested, then. Would it be possible for you to expand your original question and show the code that saves the users, gets them back, and then saves the message?

[Scott Leckie]

That dbcontext (dbc) that you have in Insert() is constructed elsewhere - is it the same context that is used to save the users and the message? If it is not the same context, was it initialised before the user changes were saved? If so then its not aware of the new user records. Generally, contexts should be very short-lived; I'd construct a new context within the Insert() method...

[SharkyShark]

even when i create new db context all time, there is still same problem : SqliteException: SQLite Error 19: 'UNIQUE constraint failed: Users.Id'. I have also readed in course to not create new DbContext all the time

[Scott Leckie]

Glad you got it sorted - it looks like the issue was as I suggested, that the messages context was not aware of the changes from the users context - sharing the context would resolve your issue.

[Opi]

Thank you for this! I was facing the same problem using Sqlite with a blazor .NET 6 application. You're post above helped me understand. I (incorrectly) added my service as Transient, changing it to Scoped solved my issue. Thank you!