How do I create a database with EF and begin using it?

Question

I have a small app I am writing as an exercise to learn how to work with SQL databases in C# .NET. My app tries to open a connection to a database, and if it fails, it assumes that the database was not there and creates it. This database is used only by my app, and is always local, so it's a lot simpler than many other database situations. Here is the code, based on things I've read on SO and also in "Pro C# 7: With .NET and .NET Core", by Andrew Troelsen:

private static DbConnection connection = null;
private static MyAppModels context = null;
public static void SetupDatabase()
{
    string dataProvider = "System.Data.SqlClient";
    DbProviderFactory factory = DbProviderFactories.GetFactory(dataProvider);

    // get connection object (closes automatically at end of using scope)
    connection = factory.CreateConnection();
    if (connection == null) { /* throw Exception */ }

    string connectionString = @"Data Source=(localdb)\mssqllocaldb;Initial Catalog=MyAppDB;Integrated Security=True";
    connection.ConnectionString = connectionString;
    try { connection.Open(); }
    catch (Exception)
    {
        // database could not be found; try to create it...
        connection.ConnectionString = @"Data Source=(localdb)\mssqllocaldb;Integrated Security=True;database=master";
        connection.Open();
        string str = "CREATE DATABASE MyAppDB ON PRIMARY " +
                     "(NAME = MyApp_Data, FILENAME = 'C:\\Users\\Me\\Documents\\MyApp\\MyAppData.mdf', " +
                     "SIZE = 2MB, MAXSIZE = 50MB, FILEGROWTH = 10%) " +
                     "LOG ON (NAME = MyApp_Log, FILENAME = 'C:\\Users\\Me\\Documents\\MyApp\\MyAppLog.ldf', " +
                     "SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 10%)";
        DbCommand myCommand = connection.CreateCommand();
        myCommand.CommandText = str;
        try { myCommand.ExecuteNonQuery(); }
        catch (System.Exception ex) { /* throw Exception */ }
    }

    context = new MyAppModels(connection);
    return;
}

This code seems to execute without a problem until I try to add data to a table, at which point I get the following error:

"The model backing the 'MyAppModels' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269)."

If I exit my app and re-open it, it's able to connect to the now-existing database and add records without a problem. But I don't want users of my app to be required to exit and re-open the app after first installing it, just to get around this problem!

I looked as several other posts here, including:

The model backing the 'POSContext' context has changed since the database was created

The model backing the context has changed since the database was created

These both seemed to point to the same solution. Also, the second one had the following quote from a member of the Microsoft EF team:

When a model is first created, we run a DatabaseInitializer to do things like create the database if it's not there or add seed data. The default DatabaseInitializer tries to compare the database schema needed to use the model with a hash of the schema stored in an EdmMetadata table that is created with a database (when Code First is the one creating the database). Existing databases won’t have the EdmMetadata table and so won’t have the hash…and the implementation today will throw if that table is missing. We'll work on changing this behavior before we ship the fial version since it is the default. Until then, existing databases do not generally need any database initializer so it can be turned off for your context type by calling:

Database.SetInitializer<YourDbContext>(null);

I don't really understand all this. But I tried adding that code to my DbContext class anyway:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    Database.SetInitializer<MyAppModels>(null);
    base.OnModelCreating(modelBuilder);
}

Now I get the following error when I try to add the first record to a table:

"An error occurred while updating the entries. See the inner exception for details."

Inner exception: "String or binary data would be truncated. The statement has been terminated."

And now, if I close and re-open the app, the same error occurs every time. So things are now worse. From looking at other SO posts, I see that this error is pointing to a problem where the data being entered into the database exceeds the field size. But I get the error on the first record I try to add and it definitely does NOT exceed any field size in that table.

I suspect that I should not be passing null to SetInitializer(). I noted the comment, "... we run a DatabaseInitializer to do things like create the database if it's not there or add seed data." My code is already trying to handle the database creation. Should my app not be doing that? How would SetInitializer() know the location to put the database or the MAXSIZE? And I don't think I need it to "add seed data", either.

I also noted this comment, "We'll work on changing this behavior before we ship the fial (sic) version since it is the default." That makes it sound like a bug. The comment was written ~9 years ago, so I would have hoped that it was already fixed.

In the meantime, does anyone have any tips for how to get this working right?

--Update--

I was able to get this working with a small addition to the creation code shown above:

myCommand.ExecuteNonQuery();
// close & try again to open the database
connection.Close();
connection.ConnectionString = @"Data Source=(localdb)\mssqllocaldb;Integrated Security=True;Initial Catalog=MyAppDB";
connection.Open();

Seems like it should be unnecessary, but it works...

Then I tried something else. I replaced all the code in SetupDatabase() with one line, as I had seen in several example tutorials:

context = new MyAppModels();

This allows/relies on EF to open the database, creating it, if necessary (in some undefined/default location). This worked when the database already existed, but when the database needed to be created, it failed:

System.Data.SqlClient.SqlException HResult=0x80131904
Message=CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Users\MyAppDB.mdf'. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. Source=.Net SqlClient Data Provider

Note the lack of a backslash between and MyAppDB.mdf. I don't know why this happens or how to fix it. I don't see anything obvious in App.config that would control this...

<connectionStrings>
  <add name="MyAppConnection" connectionString="data source=(localdb)\mssqllocaldb;initial catalog=MyAppDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />
</connectionStrings>

Can someone help me work this out, so I can use "pure" EF, or do I need to go back to my hack that works...?

Answer 1

Well

is the code, based on things I've read on SO and also in "Pro C# 7: With .NET and .NET Core"

it seems that you have tried mixing both manual DB creation and code first migrations.

And it ended badly.

Can we solve this problem?

Yes...with some additional details/code and it may even require reworking a lot of things from the scratch, so I am not sure it would be worth the time, as you seem to be studying technologies, rather than trying to solve some technological mishmash in some business critical internal program suite.

So, I recommend you to decide what you are trying to learn and do it step by step using standard tutorials:

1. You want to learn how to work with EF context and Code-First migrations? - https://learn.microsoft.com/en-us/ef/ef6/modeling/code-first/migrations/
2. Want to learn how to do it with already existing DB? - https://learn.microsoft.com/en-us/ef/ef6/modeling/code-first/workflows/existing-database
3. Want to learn how to work with basic ADO.NET classes (DbConnection, DbDataReader) - https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples
4. Want to do something in between? - just be sure that you have used each thing separately before mixing them and that you really want to try it (academic interest/desire to experiment is all good, but applying non-standard approaches to real problems is usually a bad idea).

P.S.: You seem to be using EF6 (as you used DatabaseInitializer), but I'd actually advise you to start learning EF Core as it is the latest iteration of Entity Framework.

Answer 2

The code you've posted is how you would "manually" create a database using "dynamic" SQL. You don't need to use that approach at all when using Entity Framework.

One of the easiest ways to start with Entity Framework is to use Model-First:

• Add a new "ADO.NET Entity Data Model" to your project.
• Choose "Empty EF Designer Model".
• Edit the model to design your database.
• Upon saving (or in VS2019 you have to "Run Custom Tool" on the .tt files) you will have all of your entity classes created.
• In the model choose "Generate Database from Model" to generate an SQL file containing all of the SQL statements needed to create the new database with that model. This step also creates all of the necessary bindings between the database and the C# code. (All of which exists behind the scenes.)

Entities that you add to the model become tables in the database and classes in the C# code.

Attributes that you add become fields in the database and properties in the C# code.

Associations that you add become foreign key relationships in the database and collections ("navigation properties") in the C# code.

You can then start building queries in C# code using LINQ and IQueryable.

You can make changes to the model, and go through the "Generate Database from Model" process again to get a new SQL file that creates the database, but note that it will first nuke any existing database when you execute the script. This can be ok when testing, but it can also help to use a version control system so that you can see the changes to the SQL and then make manual changes to the database structure to make sure the database matches the model without having to delete it first.