2

I have an Entity Framework .edmx file in which one of the models is based on a view in a SQL Server instance on Azure. I alter this view so that it includes one additional column.

In SQL Server Management Studio the ALTER statement succeeds without incident. Afterward, SELECT * FROM theView does show the new column as expected.

Turning to the solution in Visual Studio I open the .edmx file to show the design surface for models. I then right-click and select Update Model from Database. I'm shown the update wizard and switch to the Refresh tab. Many views are shown on the tab. This does include the one recently altered.

Update Wizard: Refresh tab

I click Finish and after several seconds the wizard completes. However, the view in the database no longer has the additional field as confirmed by running the SELECT statement above and viewing its structure in Design mode. Unexpected, but consistent with the column now being missing, my solution encounters this error when trying to return the corresponding DbSet:

System.Data.SqlClient.SqlException: Invalid column name

I've read a few related items that suggest there are problems as well as quirks to the Update Models from Database feature:

Still, none of these quite mention the behavior I am seeing. Also I think my steps agree with this tutorial. It's admitedly dated, but everything except for the ultimate result seems to lineup in VS 2017.

Is my expectation that the generated models should conform to the database and not vice versa altogether incorrect? Am I Perhaps missing some step or setting.

UPDATE [2019-06-19]: Although the commentator who asked about it reconsidered this as a possible red herring, I'll mention it for the sake of completeness. The OnModelCreating for the context looks like this (It's the default):

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
Trevor Reid
  • 3,310
  • 4
  • 27
  • 46
  • 1
    No, this shouldn't happen and normally can't happen. It seems that somehow you run a context that is in code-first mode and is allowed to auto-migrate its database. What does `OnModelCreating` look like in the context you use to query the view? – Gert Arnold Jun 18 '19 at 20:46
  • 1
    On second thoughts, that wouldn't modify a view. This is really fishy. – Gert Arnold Jun 18 '19 at 21:04
  • @Gert Arnold You're right. Using [XE](https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events?view=sql-server-2017) I've watched my own ALTER statements as well as the Update model from Database. The reversion of the view does ***not*** seem to be part of the UMFD. Something else is going on—now to find out what. – Trevor Reid Jun 20 '19 at 14:48
  • This is a large solution. Some projects use .edmx others use code first. Could this have anything to do with mixing the two in the same solution? Say when a Rebuild All is performed? – Trevor Reid Jun 20 '19 at 14:51
  • 1
    Code-first will only modify a view if it's explicitly (=manually) included in migration code. Tables may get modified automatically, views never. Maybe you should look for textual occurrences of the view name anywhere in the code base. – Gert Arnold Jun 21 '19 at 13:58

0 Answers0