none
InvalidOperationException trying to add records to SQLite db using Entity Framework RRS feed

  • Question

  • I had previously encountered problems when trying to reverse engineer an SQLite database using Microsoft.EntityFrameworkCore.Sqlite.  (See that post here.)  I was initially able to make progress, but now I continue to have problems.

    I decided to start a new, small WinForms test project.  I added the Microsoft.EntityFrameworkCore.Sqlite Nuget package.  Then I ran the following from the Package Manager Console:

    Install-Package Microsoft.EntityFrameworkCore.Tools
    Update-Package Microsoft.EntityFrameworkCore.Tools
    Scaffold-DbContext 'Data Source=C:\Users\Todd\Documents\ProfDev\SQLiteDemo\EFTest.sqlite' Microsoft.EntityFrameworkCore.Sqlite

    The database I used has these two tables:

    CREATE TABLE "Lessons" (
        "RefList"	TEXT,
        "Lesson"	TEXT NOT NULL
    )
    CREATE TABLE "Requests" (
        "RefList"	TEXT,
        "Request"	TEXT NOT NULL,
        "Category"	TEXT
    )

    I had previously tried several configurations with a PRIMARY KEY field, but could not get that to work.  I had 2 basic problems:

    • I could not get EF to reverse-engineer the database OR
    • I could not get EF to add more than one record to the database

    This second problem was caused by EF using the same PRIMARY KEY value for both the first and second records!  Adjusting the PRIMARY KEY attributes of AUTOINCREMENT and UNIQUE in various combinations would cause one or the other of these two problems.  (And each time, I had to start over with a new project, because I could never find a way to undo the reverse-engineering and try again.)

    So I did some research and found the article Introduction to SQLite ROWID table in order to "learn about SQLite AUTOINCREMENT column attribute and when to use it".  Right away I learned that, "Whenever you create a table without specifying the WITHOUT ROWID option, you get an implicit auto-increment column called rowid."

    If my database already had a PRIMARY KEY named rowid, I decided to remove my own PRIMARY KEY from the 2 tables (as you can see above), and try again.

    After using the commands shown above to reverse-engineer the database, I saw the following C# code was generated:

    using System;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.EntityFrameworkCore.Metadata;
    
    namespace EFTest
    {
        public partial class EFTestContext : DbContext
        {
            public EFTestContext()
            {
            }
    
            public EFTestContext(DbContextOptions<EFTestContext> options)
                : base(options)
            {
            }
    
            public virtual DbSet<Lessons> Lessons { get; set; }
            public virtual DbSet<Requests> Requests { get; set; }
    
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                if (!optionsBuilder.IsConfigured)
                {
    #warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
                    optionsBuilder.UseSqlite("Data Source=C:\\Users\\Todd\\Documents\\ProfDev\\SQLiteDemo\\EFTest.sqlite");
                }
            }
    
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Lessons>(entity =>
                {
                    entity.HasNoKey();
                    entity.HasKey(e => e.rowid);
                    entity.Property(e => e.rowid).ValueGeneratedNever();
     
                    entity.Property(e => e.Lesson).IsRequired();
                });
    
                modelBuilder.Entity<Requests>(entity =>
                {
                    entity.HasNoKey();
    
                    entity.Property(e => e.Request).IsRequired();
                });
    
                OnModelCreatingPartial(modelBuilder);
            }
    
            partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
        }
    }


    using System;
    using System.Collections.Generic;
    
    namespace EFTest
    {
        public partial class Lessons
        {
            public string RefList { get; set; }
            public string Lesson { get; set; }
        }
    }


    using System;
    using System.Collections.Generic;
    
    namespace EFTest
    {
        public partial class Requests
        {
            public string RefList { get; set; }
            public string Request { get; set; }
            public string Category { get; set; }
        }
    }


    I then added the following test code to Main() in Program.cs:

        try
        {
            SetupDatabase();
    
            /***  Quick Test Stub ***/
            long recID = AddLesson("Some Ref", "Test Lesson");
            recID = AddLesson("Some other Ref", "Test Lesson Again");
            recID = AddLesson("Yet another Ref", "Test Lesson Yet Again");
            Lessons rec = GetLesson(recID);
            rec = GetLesson(recID - 1);
            rec = GetLesson(recID - 2);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }


    As well as the following helper methods:

    public static void SetupDatabase()
    {
        try
        {
            context = new EFTestContext();
            context.Database.EnsureCreated();
        }
        catch (System.Exception exCreate)
        {
            MessageBox.Show(exCreate.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }
    }
    
    private static long AddLesson(string RefList, string Lesson)
    {
        Lessons lsnRec = new Lessons();
        lsnRec.Lesson = Lesson;
        lsnRec.RefList = RefList;
    
        try
        {
            context.Lessons.Add(lsnRec);
            context.SaveChanges();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }
    
        return lsnRec.rowid;
    }
    
    public static Lessons GetLesson(long RecID)
    {
        Lessons rec = new Lessons();
    
        try
        {
            Lessons lsnRec = context.Lessons.Find(RecID);
    
            if (lsnRec != null)
            {
                rec.rowid = lsnRec.rowid;
                rec.Lesson = lsnRec.Lesson;
                rec.RefList = lsnRec.RefList;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }
    
        return rec;
    }


    When I tried running the test, it failed, because EF had failed to account for the rowid field that was automatically part of the table.  So I edited the 2 table definitions to add:

    public long rowid { get; set; }

    and I removed this line from EFTestContext.OnModelCreating():

    entity.HasNoKey();

    I then removed the original model database, so EF would be forced to create a new one according to this definition.

    When I ran the code, I received this error:

    System.InvalidOperationException: The instance of entity type 'Lessons' cannot be tracked because another instance with the same key value for {'rowid'} is already being tracked....

    Can anyone help me get EF working with an SQLite database?  (It seems like pretty much no one is doing anything like this, and yet, Microsoft has provided the Nuget package, so I have to assume it must have worked for someone.)


    • Edited by thoatson Wednesday, July 1, 2020 2:39 AM Add detail
    Wednesday, July 1, 2020 2:33 AM

All replies

  • Hi thoatson,
    First, a primary key is a field in a table which uniquely identifies each row/record in a database table.
    A table can have only one primary key, which may consist of single or multiple fields.
    Based on your error, your DB Context is being shared by multiple requests, meaning that the entity you're editing has been tracked already.
    This is likely because your repository service is a Singleton rather than Scoped, and so your DB Context is being reused with the entity being tracked when it's pulled out, and then put back in to the same instance of the DB Context.
    What you should be doing instead is having a Scoped Repository, which means that a new instance will be created for each request. Likewise, you will also have a per-request DB Context.
    Here are some similar links you can refer to.
    [The instance of entity type cannot be tracked because another instance with the same key value for {'Id'} is already being tracked]
    [The instance of entity type 'Product' cannot be tracked because another instance with the same key value is already being tracked]
    Hope these are helpful to you.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 1, 2020 6:52 AM
  • Daniel_Zhang-MSFT, sorry, I don't understand what you are suggesting.  I looked at the 2 links you provided, and I don't really understand them, either.  It's not clear to me that SQLite or Entity Framework is being used in those cases.

    This is likely because your repository service is a Singleton rather than Scoped...

    Can you identify which part of the code I have posted leads you to this conclusion?  How would I change my code to implement your solution?

    I should mention that I have a working app already which uses SQL Server and EF.  But I had problems creating an installer (), so someone suggested I would do better to use SQLite for my situation.  I spent months trying to convert my existing app to use SQLite (this is a side project), and now I have this problem, which I did not have for SQL Server, and the approach I use for this is the same.

    I apologize, I should have mentioned in my original post that I had the following definition of context:

    static class Program
    {
        private static EFTestContext context = null;
        ...

    So please, help me understand what you are suggesting that I should do differently.

    Friday, July 3, 2020 9:25 PM
  • Hi thoatson,
    Here is a example about  EF working with an SQLite database in this link and you can refer to it. 
    Meanwhile, you need to note that the SQLite provider has many migration restrictions.
    More detalis you can refer to this document.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 7, 2020 7:39 AM
  • Daniel_Zhang-MSFT, thanks for taking the time to respond, but I get the feeling you don't really have any idea what's wrong with my code.

    You posted a link to the EF Core code, and there I see minimal sample code for using it.  It also has a link to the Getting Started documentation, but I didn't find anything helpful there.  My code isn't doing anything with migrations (and I don't plan for it to).

    So I'm just as stuck as when I posted this question...
    Thursday, July 9, 2020 2:33 AM