title: How to write code first unit test for sqlite using Entitity Framework Core?
body: <p>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.</p>
  
  <pre><code>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&lt;User&gt; Users { get; set; }
          public DbSet&lt;News&gt; News { get; set; }
  
          protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
          {
              optionsBuilder.UseSqlite(_connectionString);
          }
      }
  }
  </code></pre>
  
  <p>and my unit test code:</p>
  
  <pre><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");
          }
      }
  }
  </code></pre>
  
  <p>I read on the internets that EnsureCreated should be enough to create the tables in the in-memory database. What am I missing?</p>
  
  <p>I get an error </p>
  
  <p><code>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'.</code></p>
  
  <p>How do I force the tables to get created?</p>
  
  <p>I even tried using </p>
  
  <pre><code>            RelationalDatabaseCreator databaseCreator =
                  (RelationalDatabaseCreator) ctx.Database.GetService&lt;IDatabaseCreator&gt;();
  
              var s = databaseCreator.GenerateCreateScript();
  
              ctx.Database.ExecuteSqlRaw(s);
  </code></pre>
  
  <p>but I don't get it. Does the sql script even doesn't run?</p>