I'm using EF Core 3.1 to build out my Database Models for SqlServer. I'm also using EF generated migration files to handle database changes. For testing, I'm spinning up in-memory Sqlite relational database as described in microsoft documentation: https://learn.microsoft.com/en-us/ef/core/miscellaneous/testing/sqlite.
All my tests were running as expected until I added Views to my database. Views were added according to this documentation: https://learn.microsoft.com/en-us/ef/core/modeling/keyless-entity-types.
Based on Microsoft documentation, an example of a test should look something like this:
[Fact]
public void Add_writes_to_database()
{
// In-memory database only exists while the connection is open
var connection = new SqliteConnection("DataSource=:memory:");
connection.Open();
try
{
var options = new DbContextOptionsBuilder<BloggingContext>()
.UseSqlite(connection)
.Options;
// Create the schema in the database
using (var context = new BloggingContext(options))
{
context.Database.EnsureCreated();
}
// Run the test against one instance of the context
using (var context = new BloggingContext(options))
{
var service = new BlogService(context);
service.Add("https://example.com");
context.SaveChanges();
}
// Use a separate instance of the context to verify correct data was saved to database
using (var context = new BloggingContext(options))
{
Assert.Equal(1, context.Blogs.Count());
Assert.Equal("https://example.com", context.Blogs.Single().Url);
}
}
finally
{
connection.Close();
}
}
The line context.Database.EnsureCreated(); ensures that the database is created. It creates the database, tables, and inserts associated data; according to specified logic in my protected override void OnModelCreating(ModelBuilder modelBuilder) method.
The problem is, it does not create the View. According to documentation; This is the expected behaviour.
My code to create the View looks very much like the example code:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity<BlogPostsCount>(eb =>
{
eb.HasNoKey();
eb.ToView("View_BlogPostCounts");
eb.Property(v => v.BlogName).HasColumnName("Name");
});
}
I do have manually modified migration file that will create the View using raw sql. And this works on SqlServer, but it appears the context.Database.EnsureCreated(); method ignores the migration files on creating database.
Any help is appreciated.
context.Database.EnsureCreated()) and then we execute RAW SQL (context.Database.ExecuteSqlRaw(<sql>)). We don't have so many Views and we don't expect so many changes here but if possible we would like to avoid this work around because it causes effort in maintenance. – Wizardry