Using Entity Framework Core, is there a way to create the table if it does not yet exist? Exception will throw even if EnsureCreated
is called in the context:
DbSet<Ticker> Ticker { get; set }
Database.EnsureCreated();
Ticker.Add(...);
dbctx.SaveChanges(); <== exception
Results in exception:
System.Data.SqlClient.SqlException: Invalid object name 'Ticker'
Is there a way to create the table Ticker
before data is inserted?
== EDIT==
This questions is not to create/migrate the entire database, the database always exist and most of its tables also exists, but some of the tables may not. So I just need create one or two tables in runtime.
In Entity framework Core (on version 2.2.4) you can use the following code in your DbContext to create tables in your database if they don't exist:
try
{
var databaseCreator = (Database.GetService<IDatabaseCreator>() as RelationalDatabaseCreator);
databaseCreator.CreateTables();
}
catch (System.Data.SqlClient.SqlException)
{
//A SqlException will be thrown if tables already exist. So simply ignore it.
}
Database.EnsureCreated() doesn't create the schema (so your tables) when the database already exists. That's the reason why you get that exception. You can check that method's documentation.
PS: Make sure you catch the right exception if it changes in the new versions of Entity framework Core.
My guess is that your context is wrongly defined. Maybe you forgot to add the DbSet to your context implementation?
Below code is working perfectly, and I honestly prefer to EnsureCreated() in the constructor of the actual DBContext implementation.
internal class AZSQLDbContext : DbContext
{
public AZSQLDbContext() {
this.Database.EnsureCreated();
}
internal DbSet<TaskExecutionInformation> TaskExecutionInformation { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var dbUser = "your-user";
var dbPW = "your-pw";
optionsBuilder.UseSqlServer(
$"Server=tcp:sample-sql.database.windows.net,1433;Initial Catalog=sample-db;Persist Security Info=False;User ID={dbUser};Password={dbPW};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
}
}
TaskExecutionInformation is just a PoCo and could be anything. See below though if you need a bit of guidance.
public class TaskExecutionInformation
{
public Guid Id { get; set; }
public string Status { get; set; }
public int Duration { get; set; }
}
In my case there was 2 applications using same database and those could create its own code-first tables, if they were missing. So my solution for that is following extension method used in startup on dbcontext:
using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
namespace Infrastructure.Extensions
{
internal static class DbContextExtensions
{
internal static void EnsureCreatingMissingTables<TDbContext>(this TDbContext dbContext) where TDbContext : DbContext
{
var type = typeof(TDbContext);
var dbSetType = typeof(DbSet<>);
var dbPropertyNames = type.GetProperties().Where(p => p.PropertyType.Name == dbSetType.Name)
.Select(p => p.Name).ToArray();
foreach (var entityName in dbPropertyNames)
{
CheckTableExistsAndCreateIfMissing(dbContext, entityName);
}
}
private static void CheckTableExistsAndCreateIfMissing(DbContext dbContext, string entityName)
{
var defaultSchema = dbContext.Model.GetDefaultSchema();
var tableName = string.IsNullOrWhiteSpace(defaultSchema) ? $"[{entityName}]" : $"[{defaultSchema}].[{entityName}]";
try
{
_ = dbContext.Database.ExecuteSqlRaw($"SELECT TOP(1) * FROM {tableName}"); //Throws on missing table
}
catch (Exception)
{
var scriptStart = $"CREATE TABLE {tableName}";
const string scriptEnd = "GO";
var script = dbContext.Database.GenerateCreateScript();
var tableScript = script.Split(scriptStart).Last().Split(scriptEnd);
var first = $"{scriptStart} {tableScript.First()}";
dbContext.Database.ExecuteSqlRaw(first);
Log.Information($"Database table: '{tableName}' was created.");
}
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With