WAQS: Edmx Update Model from Database

7 reasons to use WAQS

WAQS documentation

 

Sometimes, particularly when you want to use many small models, you have to change edmx ssdl (DB description) yourself modifying the xml.

The issue with this appends when you want to update your model from DB after inserting a new column in DB for example.

Indeed, in this case, you can change yourself the edmx which is a big big pain, particularly when you have a lot of new tables / FKs or you can use the designer feature. In the other hand, the issue with the designer feature is the fact that you lose all your ssdl changes which is very boring!

In 2008, I asked for a fourth part in edmx for importing ssdl to be able to know what really changed in DB from last import but, sadly, we still not have it yet and we still have this issue.

So I wanted to fix it.

 

Imagine that your application / module is managing stocks. In this case, OrderDetail UnitPrice and Discount properties are probably useless. Only Quantity is useful.

 

But, to have relationships, we need OrderDetail instances in the EF context. So, if you know that you won’t add new OrderDetail in your context, you may remove them in order to simplify your model and not get it from DB when you want to load OrderDetail instances.

But then, you have some edmx errors like this:

image We have this error because we will have an issue if we want to add new OrderDetail. So EF refuses it but, if we know that we will never do it, it’s a pity that we could not do it.

 

So, in this case, the way to do what we expect is to also remove column in SSDL.

But, sadly, we can’t do it using current edmx designer.

So we have to open the designer using XML editor and we can remove them in the xml:

image

 

Now the issue is the fact that, as I explained previously, we have to do this changes any time we use Update Model from Database because ssdl is erased for this operation.

 

So WAQS defines a way to avoid it.

With WAQS, as I explained here, we don’t use the edmx we created but an edmx generated from this one.

So the idea is to delete nothing from your edmx but to specify to generation process to exclude some xml elements on generated one.

To do it, we will define an xml file in the same directory than the edmx with the edmx name + ".changes.xml"

In it, you can add, remove, replace or complete any xml elements.

For this we have to use the edmx structure and use one of these instructions:

  • add
  • remove
  • replace

If you want to add or update an XML attribute on a xml element, you just have to add it without any instruction.

In our sample, we define this file:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
  <edmx:Runtime>
    <edmx:StorageModels>
      <Schema xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
        <EntityType Name="OrderDetails">
          <remove>
            <Property Name="UnitPrice" />
            <Property Name="Discount" />
          </remove>
        </EntityType>       </Schema>
    </edmx:StorageModels>
    <edmx:ConceptualModels>       <Schema xmlns="http://schemas.microsoft.com/ado/2009/11/edm">         <EntityType Name="OrderDetail">
          <remove>             <Property Name="UnitPrice" />             <Property Name="Discount" />
          </remove>         </EntityType>       </Schema>     </edmx:ConceptualModels>     <edmx:Mappings>       <Mapping xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">         <EntityContainerMapping>           <EntitySetMapping Name="OrderDetails">             <EntityTypeMapping TypeName="NorthwindWAQSModel.OrderDetail">               <MappingFragment StoreEntitySet="OrderDetails">                 <remove>                   <ScalarProperty Name="UnitPrice" />                   <ScalarProperty Name="Discount" />                 </remove>
              </MappingFragment>             </EntityTypeMapping>           </EntitySetMapping>         </EntityContainerMapping>       </Mapping>     </edmx:Mappings>   </edmx:Runtime> </edmx:Edmx>


With this way, we can continue updating our model from DB anytime we want without any pain.

This entry was posted in 16868, 7674, 7675. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>