EF Code First - Insert or Update; Error if Table Does Not Exist

Question

I'm getting an error when using Entity Framework - Code First in the following way.

Inside of the Global.asax.cs file (during development time)

void Application_Start(object sender, EventArgs e)
{
    // Code that runs on application startup
    Database.SetInitializer<Investments>(new DropCreateDatabaseIfModelChanges<Investments>());
}

The Investments class is relatively simple:

public class Investments : DbContext
{
    public Investments() : base("Investments") { }
    public DbSet<Holding> Holdings { get; set; }
}

I can update records if they exist in the table (only if the table exists), as well as insert records if they do not exist (even if the table does not exist) using the following code:

Decimal totalPercentage;
List<Holding> mergedHoldings = GetMergedHoldings(fund, out totalPercentage);

try
{
    Investments inv = new Investments();
    foreach (Holding h in mergedHoldings)
    {
        if (inv.Holdings != null && inv.Holdings.Count<Holding>() > 0)
        {
            var record = inv.Holdings.FirstOrDefault(m => m.Name == h.Name);

            if (record != null)
            {
                record.Percentage = h.Percentage;
                record.Symbol = h.Symbol;
                record.Sector = h.Sector;
                inv.Holdings.Attach(record);
                inv.Entry(record).State = System.Data.EntityState.Modified;
            }
        }
        else
            inv.Holdings.Add(h);
    }
    inv.SaveChanges();
}
catch (Exception e)
{
    throw;
}

My problem comes when the table does not exist yet. I can't check to see if any records match, because, well, the table isn't there! Is there any easy way around this? I know I want to keep the if-exists-then-update-else-add code in there, but I have to comment out the check for if-exists-then-update during my initial load run. This is annoying during development.

The error is: System.Data.SqlClient.SqlException: Invalid object name 'dbo.Holdings'

score 0 · Accepted Answer · edited May 23 '17 at 12:27

The best you seem to be able to do is Run some code to check if a table exists. However, there is a lot of overhead here. Really, I would suggest just letting this get caught by your exception handling and deal with it at that time. How often will you not have a table.

If you really want this check , then that is something that you should check at app start, and then trust that it exists after that point.

EF Code First - Insert or Update; Error if Table Does Not Exist

1 Answers1