Create SQLite DB using Entity Framework Core Code First

In my earlier post, I mentioned that Entity Framework Core supports multiple DB providers. And SQLite is one of them. In this post, we will see how to create SQLite DB using Entity Framework Core code first approach.

Create SQLite DB using Entity Framework Core Code First

For demonstration, let’s create a .NET Core console application.

Entity Framework Core is lightweight and compared to its previous versions. Since EF Core was developed from scratch and it is decomposed in smaller packages so you can use only those which are needed in your project. So open Project.JSON and add highlighted line in the dependencies section to install SQlite nuget package. Save Project.json and wait until VS 2015 installs the package.

"dependencies": {
  "Microsoft.EntityFrameworkCore.Sqlite":  "1.0.0",
  "Microsoft.NETCore.App": {
    "type": "platform",
    "version": "1.0.0"
  }
},

Let’s create model class for SQlite tables. Here is code for the model class.

public class Category
{
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }
        
}

Now we need to create our DBContext. Add a new class file and name it SampleDBContext.cs and add following code. As mentioned in post, Database initializers no longer exist in EF Core. There are no such strategies as CreateDatabaseIfNotExists, DropCreateDatabaseIfModelChanges, DropCreateDatabaseAlways, MigrateDatabaseToLatestVersion. There are 2 new methods EnsureCreated() and EnsureDeleted() to create/delete the database. Below code in class constructor shows how to implement DropCreateDatabaseAlways.

public class SampleDBContext : DbContext
{
    private static bool _created = false;
    public SampleDBContext()
    {
        if (!_created)
        {
            _created = true;
            Database.EnsureDeleted();
            Database.EnsureCreated();
        }
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionbuilder)
    {
        optionbuilder.UseSqlite(@"Data Source=d:\Sample.db");
    }

    public DbSet<Category> Categories { get; set; }
}

OnConfiguring() method is used for configuring the DBContext. Here we are saying that use Sqlite database and also provide a connection string to connect.

Now, let’s add some data. Open Main.cs and add the following code.

public static void Main(string[] args)
{
    using (var dataContext = new SampleDBContext())
    {
        dataContext.Categories.Add(new Category() { CategoryName = "Clothing" });
        dataContext.Categories.Add(new Category() { CategoryName = "Footwear" });
        dataContext.Categories.Add(new Category() { CategoryName = "Accessories" });
        dataContext.SaveChanges();

        foreach (var cat in dataContext.Categories.ToList())
        {
            Console.WriteLine($"CategoryId= {cat.CategoryID}, CategoryName = {cat.CategoryName}");
        }

        Console.ReadLine();
    }
}

Run the application now. And you should see following output.

And if you should see a SQlite DB file created on the path specified in connection string. Below is the screenshot of file when opened in the SQLite Browser tool.

Simple and easy. Isn’t it?

Thank you for reading. Keep visiting this blog and share this in your network. Please put your thoughts and feedback in the comments section.

PS: If you found this content valuable and want to return the favour, then