Auto Create Database Tables from Objects, Entity Framework

Auto Create Database Tables from Objects, Entity Framework

is it possible to generate tables from objects created in C#?

Yes it is possible. Did you happen to create the Database manually in Management Studio before running the Code? That could be your problem. With Code First, the default convention is to create the database if it does not exist already. If the database already exists (even without the tables) then it is going to just use the existing database (but it won't try and create the tables).

You can either delete the database and try and run the code again to see if it will create it for you or put the following line in Global.asax:

Database.SetInitializer(new DropCreateDatabaseAlways<YourDbContextHere>());

Once it has run then I would suggest changing that line to:

Database.SetInitializer(new DropCreateDatabaseIfModelChanges<YourDbContextHere>());

These namespaces are defined in System.Data.Entity

The DbContext class also exposes a Database property which defines the following useful methods:

Delete()
Create()
CreateIfNotExists()

So if you defined your class like so:

public class MyContext : DbContext {}

You can construct an instance like so:

MyContext db = new MyContext();
db.Database.Delete();
db.Database.Create();

Entity Framework Code First Auto-create Table

I accidentally deleted dbo.Comments. it throws an "Invalid object 'dbo.Comments'" error at me. Why does it do this?

Because you aren't suppose to edit/delete the table the way you've configured it. It is not syncronizing database changes to models and models to the database auto-magically.

Why doesn't it just recreate the database?

Because you haven't told it that automatically, nor have you asked it to.

Why does changing then connection string to something invalid then changing it back allow EF to generate table from my models again?

Because it creates a table (forgot the name) that knows about the last time it synchronized and what tables it has and hasn't created. When you change the connection string to a different database that does not have the table, it recreates everything.

You should probably take a look at the following articles on MSDN and choose how you want Entity Framework to work for your scenario:

Code First Migrations

Auto create tables (Code first) using Entity Framework WITH MYSQL

In your ApplicationDbContext class make sure you add all needed class as public property like Settings Table.

public DbSet<PeopleDataModel> People { get; set; }

After that, you need to run Add-Migration AddPeople command that will create a new migration class with new changes, then run Update-Database command to apply pending migrations to the database.

Create database and generate table with dotnet migration from created models

first create a database manually.

after that create a dbContext and connect it to your database by using this link and this one
then add your models context inside it

now go to package manager console inside visual studio
write this command enable-migrations
then write this command to add a migration class add-migration {migrationName}
, after creating migration write this command to update your migration code inside database
by using this command update-database

for a complete documentation visit microsoft tutorial

Create Tables on runtime in EF Core

If you need to create you model tables also if in your DB there are some other tables you can use

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

How can I create a database and tables at runtime using Entity Framework 6?

Thanks for the feedback. I was able to solve the problem by recreating my ADO.NET entity model using the "Code First from database" option (in Visual Studio 2015). Previously, I used the "EF designer from database" option. Apparently, I needed to opt into "code-first" in order to leverage this feature of EF.

Entity Framework database tables association

When using Code-First where EF is defining your schema, EF will use conventions to create suitable FK columns in the tables and use those behind the scenes. You can define your own FK with whatever naming convention you want, but you will need to tell EF what column to use as the FK relationship for what table. This is done by using the [ForeignKey] attribute or configuring the relationship (modelBuilder or EntityTypeConfiguration) with the foreign key.

By convention EF will define the FK column name by the Type of the related object, which may not be what you want in the schema. For example if you have an Order entity which you want references to a User entity for CreatedBy and LastModifiedBy, you might expect to have something like

public class Order
{
    // ...
    public virtual User CreatedBy { get; set; }
    public virtual User LastModifiedBy { get; set; }
}

EF would create FK as something like: User_Id and User_Id2

This can trip up people that want to include the FK in the entity:

public class Order
{
    // ...
    public int CreatedByUserId { get; set; }
    public virtual User CreatedBy { get; set; }
    public int LastModifiedByUserId { get; set; }
    public virtual User LastModifiedBy { get; set; }
}

... expecting that to work then wondering why the IDs in the table aren't being populated.

To use more meaningful FKs you need to configure them:

public class Order
{
    // ...
    [ForeignKey("CreatedBy")]
    public int CreatedByUserId { get; set; }
    public virtual User CreatedBy { get; set; }
    [ForeignKey("LastModifiedBy")]
    public int LastModifiedByUserId { get; set; }
    public virtual User LastModifiedBy { get; set; }
}

The ForeignKey attribute can be put on either the FK pointing at the navigation property or on the navigation property pointing back to the FK.

However, I generally recommend not defining FKs in the entity as this can lead to potential issues when updating references as there are 2 sources of truth for the relationship.

Do I use order.LastModifiedByUserId or order.LastModifiedBy.UserId? What if I want to update a user and some code references one or the other of those? If I change the User navigation property when does the FK on Order get updated? What if I set the FK and not the navigation property?

If I want to update the LastModifiedBy for an order:

I might be tempted to do this:

var order = context.Orders.Single(x => x.OrderId == orderId);
order.LastModifiedByUserId = currentUserId;

However, I should do this:

var currentUser = context.Users.Single(x => x.UserId == currentUserId);
var order = context.Orders.Single(x => x.OrderId == orderId);
order.LastModifiedBy = currentUser;

Depending on whether the LastModifiedBy navigation property was eager loaded, or the DBContext could populate it (because it was already loaded and could be populated when saturating the order entity) will determine the behaviour of trying to update the FK. The second option is the consistent one, however note that even there, any FK on Order will not be updated automatically until SaveChanges is called.

Generally it is a better idea to avoid exposing FKs in the entity at all. For EF6 this can be accomplished by using Map.MapKey, where EF Core supports Shadow Properties. For example, to configure the following entity to define the FK column name to use, but avoid exposing a FK property:

public class Order
{
    // ...
    public virtual User CreatedBy { get; set; }
    public virtual User LastModifiedBy { get; set; }
}

Using modelBuilder or EntityTypeConfiguration

EF6

.HasRequired(x => x.CreatedBy)
    .WithMany()
    .Map(x => x.MapKey("CreatedByUserId"));
.HasRequired(x => x.LastModifiedBy)
    .WithMany()
    .Map(x => x.MapKey("LastModifiedByUserId"));

EF Core

.HasOne(x => x.CreatedBy)
    .WithMany()
    .HasForeignKey("CreatedByUserId");
.HasOne(x => x.LastModifiedBy)
    .WithMany()
    .HasForeignKey("LastModifiedByUserId");

/w EF Core the HasForeignKey can be used to define a shadow property for the FK column rather than point at an exposed property in the entity. The advantage here is that there is no longer two sources of truth for the related entity's key.

---

Related Topics