Creating table Entity Framework Core and SQLite

Question

Using Microsoft.EntityFrameworkCore.SQLite, I'm attempting to create a code level creation of a database, and add a simple row to a table. I get the error, SQLite error: no such table Jumplists.

From last to first, here are the classes

using JumpList_To_Clipboard.Data.Tables;
using Microsoft.EntityFrameworkCore;

namespace JumpList_To_Clipboard.Data
{
    public class DataSQLite : IData
    {
        public const string DATABASE = "data.sqlite";

        public DataSQLite()
        {
            using (var db = new SQLiteDbContext(DATABASE))
            {
                // Ensure database is created with all changes to tables applied
                db.Database.Migrate();

                db.JumpLists.Add(new JumpList { Name = "Default" });
                db.SaveChanges(); // Exception thrown here
            }
        }
    }
}

The DbContext class

using JumpList_To_Clipboard.Data.Tables;
using Microsoft.EntityFrameworkCore;

namespace JumpList_To_Clipboard.Data
{
    class SQLiteDbContext : DbContext
    {
        readonly string db_path;

        public DbSet<JumpList> JumpLists { get; set; }
        public DbSet<Group> Groups { get; set; }
        public DbSet<Item> Items { get; set; }

        public SQLiteDbContext(string database) : base()
        {
            db_path = database;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite(string.Format("Data Source={0}", db_path));
        }
    }
}

The JumpList class

using System.Collections.Generic;

namespace JumpList_To_Clipboard.Data.Tables
{
    public class JumpList
    {
        public int JumpListId { get; set; }
        public string Name { get; set; }

        public List<Group> Groups { get; set; }
        public List<Item> Items { get; set; }
    }
}

The other two classes aren't worth repeating here, and don't give errors.

When I use the firefox sqlite extension to look at the data.sqlite file, none of my three tables are listed.

The command db.DataBase.Migrate says it

Applies any pending migrations for the context to the database.

What are pending migrations? I can't seem to find any documentation anywhere on these.

I'm combining examples from:

Edit: If I replace db.Database.Migrate(); with db.Database.EnsureCreated(); it works. From the documentation, Migrate() is the same, but lets you create updates to the table structures, where EnsureCreated() does not. I'm confused.

Chuck Savage · Accepted Answer · 2017-05-31 03:39:56Z

2

So,

Microsoft has a serious issue making decent documentation, but I did find a site that has somewhat dated documentation for Learning Entity Framework Core, specifically migrations which is in the link.

At the top, it mentions,

If you have Visual Studio, you can use the Package Manager Console (PMC) to manage migrations.

Which led to the Package Manager Console page which states right at the top, that you need to have:

If you want to use the Package Manager Console to execute migrations command, you need to ensure that the latest version of Microsoft.EntityFrameworkCore.Tools is added to your project.json file.

The problem is, there is no project.json file anywhere in my project (or solution). After some searching, I found that via NuGet, to add Microsoft.EntityFrameworkCore.Tools

Then via Tools > NuGet Package Manager > Package Manager Console I was able to run the add-migration InitialDatabases command. The last part InitialDatabases is the name of the class it creates for you, and sticks in a folder called Migrations at the base of the project.

Now when:

context.Database.Migrate();

is run, all is well!

answered May 31, 2017 at 3:39

Chuck Savage

12k6 gold badges53 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bricelam Over a year ago

Here's some extremely hard to find documentation by Microsoft on EF Core Migrations: learn.microsoft.com/en-us/aspnet/core/data/ef-mvc/migrations

Chuck Savage Over a year ago

@bricelam I looked at the "Getting Started" page of that, and since it wasn't what I was looking for at the time. Binding WCF and EFC, I moved on. Good to know. I think you were being sarcastic, but check out the official docs Getting Started and EF Core with WPF and .Net and you'll see what I mean. They don't even contain the off quoted Blog example. If you fix it, add binding posts, selecting blog in a combo-box, and populating posts all within code and xaml.

bricelam Over a year ago

No sarcasm. There is a serious lack of documentation, and what documentation there is is impossible to find and navigate.

charly989 · Accepted Answer · 2017-05-28 06:17:25Z

0

Try this (worked for me in a project a few months ago, i don't remember why):

 public virtual DbSet<JumpList> JumpLists { get; set; }
 public virtual DbSet<Group> Groups { get; set; }
 public virtual DbSet<Item> Items { get; set; }

Also i had to use LONG instead of INT for classes ID because sqlite uses LONG as default for table ID, so after when you do a CRUD operation it fails because it can't compare/convert/cast LONG(64) to INT(32).

answered May 28, 2017 at 6:17

charly989

13 bronze badges

Collectives™ on Stack Overflow

Creating table Entity Framework Core and SQLite

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related