EnsureCreated not working when exist a Database Diagrams on SQL Server

[m4ss1m0g]

Description

When use SQLServer with database diagrams, the EnsureCreated command fail without any error.

Steps to reproduce

Define a simple DbContext with a single Entity

public class Book 
{
    public int Id {get; set;}

    public string Title {get; set; }
}

public class AppDbContext : DbContext
{
    public DbSet<Book> Books { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        const string cs = @"Server=.\sqlexpress;Database=EFCore;Trusted_Connection=true";
        optionsBuilder.UseSqlServer(cs);
    }
}

Open SSMS and create a new database diagrams (is sufficient to create the table sysdiagrams) and create an empty diagram

Launch the EnsureCreated command

using (var db = new AppDbContext())
{
    db.Database.EnsureCreated();
}

Check the SSMS and No Tables are created

Workaround

DROP table sysdiagrams under System Tables node

Further technical details

EF Core version: 2.2.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.9.7

[ajcvickers]

@m4ss1m0g Curious: what's your intended workflow around using EnsureCreated? For example, if you need to modify the database after the initial creation, how do you intend to do that?

[m4ss1m0g]

@m4ss1m0g Curious: what's your intended workflow around using EnsureCreated? For example, if you need to modify the database after the initial creation, how do you intend to do that?

I already know EF6 and the migration tools, I use EnsureCreate only for dev/testing.
I learning EF Core and some times I use the EnsureCreate to create the DB, then with the DB Diagram I check if the tables and relations are correct.

[ajcvickers]

@m4ss1m0g So do you ever call EnsureDeleted?

[m4ss1m0g]

The problem is on EnsureCreated AND the sysdiagrams table.

1. Create a new Database with SSMS
2. On the new database right click on Database Diagram node and select new database diagram
3. Click yes when asking for create the support object, this create a new sysdiagrams table under system tables
4. Launch the EnsureCreated
5. Check SSMS and verify that NO tables was created.
6. Delete the sysdiagrams table
7. Launch the EnsureCreated
8. Check SSMS and verify that tables WAS created.

[ajcvickers]

@m4ss1m0g I understand what is happening; that's not the point of this conversion. The current behavior is by-design; it's possible we could change that, but to do so the end-to-end experience needs to make sense. Since usually EnsureCreated is used together with EnsureDeleted, and EnsureDeleted will delete the entire database, including system tables. What we're trying to understand is whether or not it makes sense to allow EnsureCreated to ignore something that EnsureDeleted will remove. So, to move forward with this, I'm asking for more information about the bigger picture in which you are using EnsureCreated.

[m4ss1m0g]

@ajcvickers I did not know EnsureDeleted ! In my workflow I deleted the tables by DROP TABLE command. Now has more sense.
However I noticed that EnsureDeleted remove ALL tables, also tables from other schemas; I think a warning message or a better documentation would be much appreciated.
Thanks

[ajcvickers]

Note from triage: it makes sense to ignore system tables when considering whether or not the database is "empty", even though EnsureDeleted deletes the entire database, including system tables.

[ErikEJ]

Should the milestone not be updated?

[smitpatel]

Updated tags.