How to Exclude Certain Columns in EF6 Using Database First?

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

Question

The database for the project I'm working on is pretty sophisticated, and the code I'm using is only a stopgap until another system redesign is taking place.

I have some old code that has to be fixed, so I need to move rapidly and use workarounds wherever I can.

There is a database already in place, and I am successfully utilising EF6 Datbase First.

However, I am seeing more and more instances (I'm on my fourth one now) where my code does not need a specific column, and EF is having trouble with type conversion.

The quickest approach, according to my customer, is to delete these columns from the EF model, which I have been doing manually after each "update from database" operation I need to do in order to repair a view, table, or other object.

Is it possible for me to tell the EF "Update from Datbase" action to ignore certain columns?

I've seen this with Code First, where OnModelCreating may be modified to set an ignore property, or when a related attribute is added to an existing property to tell EF when to ignore a certain value.

I had considered creating a partial class that was defined in a.cs file in a subfolder where I could insert code to make this happen, but I am unable to annotate the property since it would be declared twice—once in my partial and once in the.cs file created by the T4 template.

I looked online for a method to handle this, but I couldn't find anything.

For instance:

The database contains Table X, Columns 1, 2, and 3.

Entity model with all 3 columns will be created for Table X by an update from the database.

There is no Column2 field on the model class, and my code never refers to any data in Column2 after an Update From Database, therefore everything is OK.

What I'm looking for is something that I can insert anywhere, such as the EF model, the database, or a partial class, that won't be overwritten each time I do an Update from Database.

Any recommendations?

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

Accepted Answer

Knowing precisely what occurs when you utilize the update model wizard is a crucial first step.

Library MSDN from the:

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.

The storage model in the.edmx file, not the conceptual model, was modified when you upgraded the database using the update model wizard. The conceptual model is not changed when definitional changes are made to existing items; only the storage model is. Please refer to the link above under "Changes Made to an.edmx File by the Update Model Wizard" for a detailed explanation of the modifications that the update model wizard makes.

If the column definition was changed in your scenario, here are several possibilities for updating objects that are not updated by the update model wizard:

  1. To change the storage model, use the update model wizard, open the.edmx file in the default designer, identify the required scalar property, and adjust the desired properties in the Properties windows.
  2. To update the storage model, use the update model wizard, open the.edmx file in an XML editor, locate the needed property in the CSDL (conceptual model) section, and make the necessary changes to the relevant attributes. The main difference between this and option 1 is that you are directly modifying the XML (a find and replace might be useful here).
  3. Delete the chosen table from the Tables / Views part of the storage model and the relevant entity from the Entity Types section of the conceptual model from the Model Browser. Then, add it back using the update model wizard.

The situation would determine the optimal course of action. For instance, option 1 is probably your best option if you just changed the definition of one column. Option 3 could be your best option if you changed the definition of many columns in one table. Editing the.edmx XML directly would be your best choice if you changed a column that is utilized by several tables (such a primary or foreign key).

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

Popular Answer

An EDMX update done the safe way:

As you have seen, the database update does not always appropriately alter existing attributes.

I would suggest upgrading an EDMX in the order shown below based on our daily usage (hundreds of updates over 24 months).

Update after erasing the current model:

  1. Activate the EDMX designer
  2. Using Ctrl-A, select all
  3. Press the Delete key to remove all models from the designer
  4. Important: If you are using TFS for source control, do not save the EDMX at this time!
  5. To reconstruct the complete model from scratch, right-click and choose "Update Model from Database."
  6. Rebuild the project to spread the modifications

Manual adjustments to the model will be lost as a result, but they should be avoided at all costs. As a result, the whole procedure may be repeated whenever necessary (which is a good thing).

Important details

  • To prevent an auto-save from storing everything if auto-save is enabled in Visual Studio, rapidly choose the update (step 5 above).
  • The produced files will all be marked as "deleted" by TFS if you use it for source control and save the EDMX after emptying it. Updating the EDMX again may lead to disconnected files that don't have a source control system!.
  • No saved processes will be updated by this operation. A refresh of an EDMX, I've discovered, also fails to update stored procedures when just the return type has changed (still current as of EF 6.1.1).

A further recommendation

Maintain a separate library for your EDMX. Additionally, this serves as a fantastic location to upload extra TT files and incomplete courses (e.g. to extend function of EDMX models). I also provide any database context extension methods in this package. Themigration Additionally, files are created in the library, keeping everything neatly contained.

Revision: April 2015

A significant number of the TFS problems seem to have been rectified in Visual Studio 2013's most recent Release 4. We now see the files created by Visual Studio checkout and, if necessary, rollback them. Still, it seems safer to follow the preceding measures.

September 2015 update

If a save happens during an EDMX upgrade, we still see problems with the most recent VS2013 Release 5. You might still find yourself in a situation where pending deletes affect your ability tott files that will be updated will be taken out of source control. To succeed, update quickly 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