locked
Error "property list cannot be used, because it contains a duplicate" when I try to reverse engineer an SQLite database for Entity Framework RRS feed

  • Question

  • I want to use a "code first from database" approach to develop my C# app.  The SQLite database was created using SQLite Browser (downloaded from sqlitebrowser.org).  Now I want to create the Entity Framework model using reverse engineering, as described in Reverse Engineering.

    Here is what I tried, using PMC (Tools > NuGet Package Manager > Package Manager Console):

    1.  Install-Package Microsoft.EntityFrameworkCore.Tools

    2.  Update-Package Microsoft.EntityFrameworkCore.Tools

    3.  Scaffold-DbContext 'Data Source=C:\Users\...\MyData.db' Microsoft.EntityFrameworkCore.Sqlite

    After step #3, I get the following messages:

    Build started...
    Build succeeded.
    System.InvalidOperationException: The property list {'RecId', 'RecId'} cannot be used, because it contains a duplicate - 'RecId'.
       at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityType.AddKey(IReadOnlyList`1 properties, ConfigurationSource configurationSource)
       at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.HasKeyInternal(IReadOnlyList`1 properties, Nullable`1 configurationSource)
       at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.PrimaryKey(IReadOnlyList`1 properties, ConfigurationSource configurationSource)
       at Microsoft.EntityFrameworkCore.Metadata.Builders.EntityTypeBuilder.HasKey(String[] propertyNames)
       at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitPrimaryKey(EntityTypeBuilder builder, DatabaseTable table)
       at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTable(ModelBuilder modelBuilder, DatabaseTable table)
       at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTables(ModelBuilder modelBuilder, ICollection`1 tables)
       at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitDatabaseModel(ModelBuilder modelBuilder, DatabaseModel databaseModel)
       at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.Create(DatabaseModel databaseModel, Boolean useDatabaseNames)
       at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
       at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
       at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
       at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0()
       at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
       at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
    The property list {'RecId', 'RecId'} cannot be used, because it contains a duplicate - 'RecId'.
     

    Please help me understand what is going wrong and how to get this working...


    • Edited by thoatson Monday, May 18, 2020 11:44 PM Adjust title
    • Moved by CoolDadTx Tuesday, May 19, 2020 1:59 PM EF related
    Monday, May 18, 2020 11:44 PM

Answers

  • Please share the CREATE TABLE statment for your tables with RecId as primary key components.

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Marked as answer by thoatson Friday, May 22, 2020 1:29 AM
    Tuesday, May 19, 2020 3:04 PM

All replies

  • If the build succeeded then this error sounds like a runtime error, is this correct? And if this is correct what was the failing operation? Have you verified the DbContext OnModelCreating is setup properly according to the database relations? If this is on a SaveChanges look at the ChangeTracker.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, May 19, 2020 12:31 AM
  • The entire error message is produced in response to the scaffolding command.  I thought it strange that it says build completed, but then goes on to fail.  Since the scaffolding failed, my app has no database model and no database operations.

    If it had succeeded, the OnModelCreating and SaveChanges would have been handled automatically by Entity Framework, right?

    Anyway, I was not running my app when the error message was produced, I was trying to create my app.

    I had previously used reverse engineering via Entity Model Data Wizard for another app which used SQL Server localdb, but the Wizard doesn't work for an SQLite database.

    Tuesday, May 19, 2020 1:43 AM
  • Hi thoatson,

    Thank you for posting here.

    In Oracle, this error occurs when there are two schemas in a database, and there are tables with the same name in both schemas, and both tables contain the same fields.

    But you are using sqlite, and sqlite does not seem to support multiple schemas.

    So I was thinking, is it possible that you have attached multiple sqlite database files in some functions, and these files have tables and fields with the same name causing this problem?

    Best Regards,

    Timon


    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, May 19, 2020 3:18 AM
  • Take a look at the following Visual Studio extension, Entity Framework Power tools. The extension supports reverse engineering SQLite.

    Once installed, right click on the project in Solution Explorer and follow as per below.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, May 19, 2020 10:49 AM
  • Please share the CREATE TABLE statment for your tables with RecId as primary key components.

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Marked as answer by thoatson Friday, May 22, 2020 1:29 AM
    Tuesday, May 19, 2020 3:04 PM
  • Hi @ErikEJ, as you know, the database was created with SQLite Browser, so I did not write a CREATE TABLE statement.  But the statement I can copy from SQLite Browser looks like this:

    CREATE TABLE "Lessons" (
    	"RecId"	INTEGER NOT NULL,
    	"RefList"	TEXT,
    	"Lesson"	TEXT NOT NULL,
    	PRIMARY KEY("RecId")
    )

    My database has 18 tables, similar to this one, and the primary key for each one is RecId.  I did something similar previously for an SQL Server database, and I was able to reverse engineer that one with the Wizard, no problems.

    Also, as you can see from her post, @Kareninstructor has recommended your Entity Framework Power tools to solve this problem.  Do you think that would help...?


    • Edited by thoatson Wednesday, May 20, 2020 1:30 AM Correction
    Wednesday, May 20, 2020 1:28 AM
  • Hi @Timon Yang, thanks for your response & sharing your experience.

    I don't know how there could be 2 schemas in this database.  It looks OK when I have it open in SQLite Browser.  There are 18 tables in the database, but they all have unique names.  Though the primary key in each table has the name RecId, I don't think that should be a problem.  I already did something similar to this in SQL Server and that database was able to be reverse engineered by the Wizard without any problem.

    > ... is it possible that you have attached multiple sqlite database files in some functions, and these files have tables and fields with the same name causing this problem?

    Actually, no.  I don't have any database functions in my app, yet, because I wanted to generate the database model for EF first.  After that add the functions which interact with the model.

    Any other ideas?

    Wednesday, May 20, 2020 1:38 AM
  • Could you (privately) share a database file, and I will have a look. Sounds like a potential bug.


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Wednesday, May 20, 2020 1:00 PM
  • @ErikEJ, I sent the db to your hotmail account.  (I don't know any other way to privately share it with you.)
    • Edited by thoatson Thursday, May 21, 2020 2:06 AM
    Thursday, May 21, 2020 2:06 AM
  • The problem was indeed in a CREATE TABLE statement.  One of the 18 tables contained the following:

    PRIMARY KEY("RecId","RecId")

    I had done the same thing for all 18 tables, but for some reason one of them came out wrong.  Even now I cannot reproduce the problem in SQLite Browser, now that I have fixed it.  Very strange.

    It would have helped a lot if the error message from the Scaffold-DbContext command would have mentioned the name of the table containing the error!

    Thanks to @ErikEJ for finding this.

    Now I have been able to successfully run Scaffold-DbContext and it has generated the model code, so I can continue to move forward in my exploration of SQLite!


    • Edited by thoatson Friday, May 22, 2020 1:30 AM
    • Proposed as answer by John Boncek Wednesday, May 27, 2020 1:38 PM
    Friday, May 22, 2020 1:29 AM