How to write code first unit test for sqlite using Entitity Framework Core?

Question

I'm trying to do a very simple rest api for starters. I created the local sqlite file using the tool and it works. Now I want to write a unit test for the database.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace dz1
{
    [Table("User")]
    public class User
    {
        [Key]
        [Column]
        public string Name { get; set; }

        [Column]
        public string Address { get; set; }

        [Column]
        public int Age { get; set; }
    }

    [Table("News")]
    public class News
    {
        [Key]
        [Column]
        public string Title { get; set; }

        [Column]
        public string Content { get; set; }

        [Column]
        [ForeignKey("User")]
        public string Author { get; set; }
    }

    public class MyContext : DbContext
    {
        private readonly string _connectionString;

        public MyContext()
        {
            this._connectionString = "DataSource=./mydb.db";
        }

        public MyContext(string connectionString)
        {
            this._connectionString = connectionString;
        }

        public DbSet<User> Users { get; set; }
        public DbSet<News> News { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite(_connectionString);
        }
    }
}

and my unit test code:

using System;
using System.Linq;
using dz1;
using Xunit;

namespace ApiTest
{
    public class UnitTest1
    {
        [Fact]
        public void Test1()
        {
            var ctx = new MyContext("DataSource=:memory:");

            ctx.Database.EnsureDeleted();
            ctx.Database.EnsureCreated();

            var u = new User();
            u.Name = "Mirko";
            u.Address = "Tamo 2";
            u.Age = 101;

            ctx.Users.Add(u);
            ctx.SaveChanges();

            Assert.True(ctx.Users.ToList().First().Name == "Mirko");
        }
    }
}

I read on the internets that EnsureCreated should be enough to create the tables in the in-memory database. What am I missing?

I get an error

Microsoft.EntityFrameworkCore.DbUpdateException : An error occurred while updating the entries. See the inner exception for details. ---- Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'no such table: User'.

How do I force the tables to get created?

I even tried using

            RelationalDatabaseCreator databaseCreator =
                (RelationalDatabaseCreator) ctx.Database.GetService<IDatabaseCreator>();

            var s = databaseCreator.GenerateCreateScript();

            ctx.Database.ExecuteSqlRaw(s);

but I don't get it. Does the sql script even doesn't run?

Apologies. I added the question. I want the tables to get created in the database. — ditoslav
– ditoslav, Commented Oct 29, 2019 at 8:40
Did you inspect the variable s? What does it contain? This will allow you to at least determine whether its the model (if s is blank and/or missing parts of the schema, then your object model has problems) or the database (if s contains a valid schema from the model, then something is not working with the in-memory database). — C Perkins
– C Perkins, Commented Oct 30, 2019 at 0:22
You should also be inspecting the value of EnsureCreated(). Online docs indicate that it returns true or false depending on whether the database was created successfully or not. Your question is valid, but there are so many things that you could be checking... which you are not. Look at docs, trace through the code, etc. Update the question with details you find... or the debugging process just might reveal the answer to you. — C Perkins
– C Perkins, Commented Oct 30, 2019 at 0:26

Wijitha · Accepted Answer · 2019-10-30 05:37:53Z

Follow these steps, you should be able to get it working.

1) Create SQLite connection for in-memory database

2) Create the DbContextOptions for SQlite connection

3) Pass the DbContextOptions to the DataContext. For this you need to have a overload in the DataContext accepting a DbContextOptions.

4) Call the dbContext.Database.EnsureCreated() to create the database.

using(var connection = new SqliteConnection("DataSource=:memory:"))
{
    connection.Open();
    var options = new DbContextOptionsBuilder<DataContext>()
                   .UseSqlite(connection)                  
                   .Options;

    var dbContext = new DataContext(options);
    dbContext.Database.EnsureCreated();

    //You testing logic goes here.
}

5) Adding a overload constructor in DataContext

public DataContext(DbContextOptions<DataContext> options) : base(options)
{

}

6) Database will be deleted with disposing the connection

You can find a code sample in https://github.com/Wijithapaw/PMS/tree/sqlite/PMS.Api

Cheers,

Collectives™ on Stack Overflow

How to write code first unit test for sqlite using Entitity Framework Core?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related