EntityFramework refuses to forget old columns

Question

I'm using EntityFramework 6.1.3, database-first. I am currently wishing I had chosen code-first...

I have a database with some tables. I've previously built my edmx off of these tables. Then I changed the type of a few columns and added a few columns. For instance, changing a bit column to an int column.

I try and update my model from the database, using right-click -> Update Model from Database.

It seems that no matter what I do, EF will only ever pick up the state of my database as it was when I created the edmx. Things I have tried:

1. Reopening Visual Studio
2. Rebuilding the project
3. Deleting and re-adding the entity (this is what most people say should work)
4. Searching the entire project for text references in xml or C# to my column while visual studio is closed and replacing them. (This seems to work at first but if I try to update from database again it writes over them)
5. Restarting SQL service
6. Restarting the machine
7. "Run Custom Tool" on all .tt files (shouldn't make a difference but what the hell)

When I right-click my entity and select "Table Mapping", it always always shows the old bit column on the left.

Here's my database table design:

It might be of note that the entity is going off a view, not directly off of the table. But the view is literally a select * of the table as I am investigating this issue, and I have confirmed with powershell that the type returned by the view is an int:

PS> $conn = new-object data.sqlclient.SqlConnection("data source=localhost;initial catalog=dbname;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework")
PS> $conn.open()
PS> $query = $conn.createcommand()
PS> $query.commandtext = "select * from [dbname].[LocalCustom].[viewname]"
PS> $reader = $query.executereader()
PS> $reader.getschematable().rows[19]

ColumnName                      : ActiveMember
ColumnOrdinal                   : 19
ColumnSize                      : 4
NumericPrecision                : 10
NumericScale                    : 255
IsUnique                        : False
IsKey                           :
BaseServerName                  :
BaseCatalogName                 :
BaseColumnName                  : ActiveMember
BaseSchemaName                  :
BaseTableName                   :
DataType                        : System.Int32
AllowDBNull                     : False
ProviderType                    : 8
IsAliased                       :
IsExpression                    :
IsIdentity                      : False
IsAutoIncrement                 : False
IsRowVersion                    : False
IsHidden                        :
IsLong                          : False
IsReadOnly                      : False
ProviderSpecificDataType        : System.Data.SqlTypes.SqlInt32
DataTypeName                    : int
XmlSchemaCollectionDatabase     :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName         :
UdtAssemblyQualifiedName        :
NonVersionedProviderType        : 8
IsColumnSet                     : False

My MAIN QUESTION I have at this time is... How does EF know that it used to be a bit type? Where is it storing this data?? Of course I would also like to know how to properly update the model using the UI without having to delete and re-add entities or whatever else I'm going to have to do to get it to update.

I'm pretty frustrated with EF :(

Answer 1

Well, just edit/update your view, even with no changes. EF doesn't update unmodified items.

As long as you haven't changed/updated the view but the underlying table(s), EF can't detect there's a need to change anything. I do believe this behavior is meant to prevent from a full rebuild of the model each time.

---

As the OP wished to understand "why", I made a few tests.

First, I created a table and a "SELECT *" view on it :

create table TableToChange(rowKey bigint IDENTITY(1,1) not null, myBitFlag bit null, myIntFlag int)
go
create view SelectStarOnChangingTable as SELECT * FROM TableToChange
go

Then I did a little check of the sys objects and columns that were created with :

select * 
from sys.all_objects AO 
    join sys.all_columns AC on AC.object_id=AO.object_id
where AO.object_id in (--insert your objet_ids here--)

Do some minor changes on the table object :

alter table TableToChange
drop column myBitFlag
go
alter table TableToChange
add myBitFlag int
go

If you run again the schema query, you'll notice that the updated column doesn't have the same type in the USER_TABLE row and the VIEW row (56 vs 104)

Altering the SelectStarOnChangingTable view without changes will force SQL Server to update.

We now have a culprit : SQL Server stores the view's column types preventing EF to update its model, even if the entity is rebuilt from scratch.