How to Exclude Certain Columns in EF6 Using Database First?

c# ef-database-first entity-framework-6 sql-server

Question

I am working on a project which has a fairly complex database, and the code is stop-gap while another system overhaul is underway.

I am fixing up some legacy code, and need to perform work quickly and use work-arounds wherever possible.

There is an existing database and I am using EF6 Datbase First with much success.

However, I am discovering various cases (up to my 4th one now) where a particular column is not needed in my code, and EF is tripping up on type conversion issues.

I have found the fast solution (and preferred by my client) is to simply remove these columns from the EF model - which I have been doing manually after "update from database" - which I must do each time I fix a view or table, etc.

Is there a way I can somehow instruct EF "Update from Datbase" operation to ignore certain columns?

I have seen this for Code First - where OnModelCreating can be overridden to set an ignore property - or where a similar attribute is added to the existing property to inform EF when to ignore certain properties.

I had thought about using a partial class - defined in a .cs file in a subfolder where I could put some code to cause this to happen - however I can not annotate the property - as it would be defined twice - once in my partial, and one in the .cs generated by the T4 template.

I've been searching online for a technique to manage this, and I have not found anything.

For example:

Database has Table X, Column1, Column2, Column3.

Update from Database will create entity model for Table X, with all 3 columns.

After Update From Database, I can removed "Column2" reference, and all is well - there is no Column2 property on the model class, and my code simply never references any data in Column2.

What I am seeking is something I can put somewhere - in the database, the EF model, or partial class that will not be over-written each time I do an Update from Database.

Any suggestions?

1
0
7/18/2016 8:59:31 PM

Accepted Answer

An important first step is to understand exactly what happens when you use the update model wizard.

From the MSDN Library:

The ADO.NET Entity Data Model Designer (Entity Designer) uses the Update Model Wizard to update an .edmx file from changes made to the database. The Update Model Wizard overwrites the storage model as part of this process. The Update Model Wizard also makes some changes to the conceptual model and mappings, but it only makes these changes when objects are added to the database. For example, new entity types are added to the conceptual model when tables are added to the database, and new properties are added to entity types when columns are added to a table. For details about what changes are made to the .edmx file, see Changes Made to an .edmx File by the Update Model Wizard.

When you updated the database using the update model wizard, it updated the storage model in the .edmx file and not the conceptual model. When changes are made to the definition of existing objects, only the storage model is updated; the conceptual model is not updated. For a complete description of changes that are made by the update model wizard, please see the "Changes Made to an .edmx File by the Update Model Wizard" link above.

Here are some options on how to update objects that are not updated by the update model wizard (based on your scenario where a the column definition was altered):

  1. Use the update model wizard (to update the storage model), open the .edmx file using the designer (default), find the desired scalar property and edit the desired properties in the Properties windows.
  2. Use the update model wizard (to update the storage model), open the .edmx file using the XML editor, find the desired property in the CSDL (conceptual model) section and change the desired attributes. This is basically the same as option 1, but you're editing the XML directly (a find and replace might be useful here).
  3. From the Model Browser, delete the desired entity from the Entity Types section of the conceptual model and the desired table from the Tables / Views section of the storage model. Then use the update model wizard to add it back.

The best option would depend on the given scenario. For example, if you just altered the definition of one column, then option 1 is likely you best choice. If you altered the definition of a number of columns in a single table, then option 3 might be your best choice. If you altered a column that is used across a number of tables (such as a primary / foreign key), then editing the .edmx XML directly might be your best option.

40
3/14/2012 8:39:06 PM

Popular Answer

Updating an EDMX the safe way:

As you have found, the update from database does not always change existing properties correctly.

From our day-to-day use of EDMX updating (100s of updates over 24 months), I would recommend the following sequence for updating an EDMX.

Delete existing model and then update:

  1. Open the EDMX designer
  2. Ctrl-A to select all
  3. Delete key to delete all models in the designer
  4. IMPORTANT: Do not save the EDMX at this point if you are using TFS for source control!*
  5. Now right-click and select "Update Model from Database" to recreate the entire model again.
  6. Rebuild project to propagate changes

This will obviously lose any manual tweaks you have made to the model, but manual tweaks are to be avoided if possible. This makes the entire process reproducible at any time (which is a good thing).

Important notes:

  • If you have auto-save turned on in Visual Studio, you need to select the update (step 5 above), quickly to avoid an auto-save saving everything.
  • If you are using TFS for source control, and happen to save the EDMX after emptying it, TFS will mark all the generated files as "deleted" and updating the EDMX again can result in disconnected files that are not in source control!.
  • This process will not update any stored procedures. Further, I have found that a refresh of an EDMX will also not update stored procedures where just the return type has changed (still current as of EF 6.1.1).

Additional Recommendation:

Keep your EDMX in a separate library. This also becomes a great place to add additional TT files and partial classes (e.g. to extend function of EDMX models). I also place any extension methods for the database context in this library. The migration files get generated in the library too keeping it all nicely contained.

Update April 2015

The latest Release 4 of Visual Studio 2013 appears to have resolved a lot of the TFS issues. We now see Visual Studio checkout generated files, then revert them if they are unchanged. The above steps still appear to be the safest approach.

Update September 2015

Using latest VS2013 Release 5, we still have issues if a save occurs during EDMX update. You can still wind up in a state where pending deletes causes your tt files to be removed from source control during the update. The secret is to update fast between steps 4 and 5! :)



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow