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
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:
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 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 :(
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)
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.
This is the case I often meet when using Entity Framework database-first. When you make some changes from the database, to update EF, you should:
right-click -> Update Model from Database -> check that table