none
Column must be mapped although it does have a default value RRS feed

  • Question

  • I am trying to create an Entity Framework 4 model based on a legacy SQL server 2008 database. Since the model is going to be published in a web service, I need to omit an integer column called NewsletterSubscribed from one of the model's entities.

    After removing the column in the EF designer I got the following compilation error:

    Error   59  Error 3023: Problem in mapping fragments starting at line 356:Column Users.NewsletterSubscribed in table Users must be mapped: It has no default value and is not nullable.
        C:\Users\Adrian\Documents\Sites\Zeiterfassung\Zeiterfassung\API\V1\EFModel.edmx 357 15  Zeiterfassung

    But the column seems to have a default value of 0 bound to it. I tried running this SQL statement against the database:

     

    ALTER TABLE [dbo].[Users] ADD DEFAULT ((0)) FOR [NewsletterSubscribed]

     

    But that also fails:

    Msg 1781, Level 16, State 1, Line 3 Column already has a DEFAULT bound to it. Msg 1750, Level 16, State 0, Line 3 Could not create constraint. See previous errors.

    So either the column does not have a default value (in which case I am not sure why I can't create one), or Entity Framework doesn't see it. What is going on?

    Thanks,

    Adrian

    Saturday, June 19, 2010 5:24 PM

Answers

  • Hi Mr. Yossu,

    I worked around the issue with a batch file that uses xmlstarlet to delete the respective properties and database references from the model. For example, when removing the column "NewletterSubscribed" from the "Users" entity, the command looks like this:

     

    Snippet

    "XMLStarlet\xml.exe" ed -d "//*[@Name='User' or @Name='Users' or @StoreEntitySet='Users']/*[@Name='NewsletterSubscribed']" Model.edmx >Model.edmx.xml
    move Model.edmx.xml Model.edmx

    Wednesday, July 7, 2010 4:11 PM

All replies

  • Hello Adrian,

     

    Welcome to EF forum!

     

    The error message indicates there is no certain property in the CSDL (conceptual model) to map the column NewsletterSubscribed in the SSDL (storage model).   EF seems not load the Default Value or Binding settings from the database.  

     

    In such a scenario, we can manually edit the .edmx file to add the DefaultValue to the certain column in SSDL.   Please right click the .edmx file and select “Open With…”, then pick the “XML (Text) Editor”.

     

    In the SSDL section, please add DefaultValue element to the certain column: 

    ======================================================================

            <EntityType Name="User">

              <Key>

                <PropertyRef Name="UserID" />

              </Key>

              <Property Name="UserID" Type="int" Nullable="false" />

             

              <Property Name="NewsletterSubscribed" Type="int" Nullable="false" DefaultValue="0" />

            </EntityType>

    ======================================================================

     

    Besides, if you want to hide the certain properties, we can also set its Getter and Setter properties to Private.  

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, June 21, 2010 3:31 AM
    Moderator
  • Hello Lingzhi Sun,


    Thanks for your reply and your kind welcome!

    Unfortunately making the property getter /setter private results in an exception in the WCF Data service, so that's not an option.

    Also, Isn't the DefaultValue going to be overwritten the next time I update the model from database?

    Anyway, in case someone else has the same problem: I worked around this issue by creating a script that removes all references to the column the property from my edmx file. Now I just have to execute this script whenever after updating my model.
    Monday, June 21, 2010 1:21 PM
  • Hello Adrian,

     

    Yes, as you said, the DefaultValue element will be overwritten if the model is updated.  

     

    Could you please share us with the script you created?   J   It will definitely be beneficial to the other community members.  

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, June 23, 2010 3:56 AM
    Moderator
  • Is there actually a satisfactory answer to this problem? I only just started using EF4, but came across the issue very quickly.

    It seems the only way to avoid the problem is to go through your model and set the default value of every non-nullable field. That could take a long time, result in a lot of mistakes, and all be overwritten next time you need to generate the model.

    Why isn't this done automatically when the model is generated? The framework can see the default values in the tables, so it could easily pull them out and use them.

    Anyone got a good answer for this?

    Thanks

    Wednesday, July 7, 2010 3:51 PM
  • Hi Mr. Yossu,

    I worked around the issue with a batch file that uses xmlstarlet to delete the respective properties and database references from the model. For example, when removing the column "NewletterSubscribed" from the "Users" entity, the command looks like this:

     

    Snippet

    "XMLStarlet\xml.exe" ed -d "//*[@Name='User' or @Name='Users' or @StoreEntitySet='Users']/*[@Name='NewsletterSubscribed']" Model.edmx >Model.edmx.xml
    move Model.edmx.xml Model.edmx

    Wednesday, July 7, 2010 4:11 PM
  • Hi Adrian,

    Thanks for the reply. I saw mention of this utility in another thread here, but it's not really a solution. If you need to regenerate the model (not uncommon during development), your changes are lost and you need to run the utility again.

    Why isn't this sorted out in the model generator itself? Why do we have to resort to a hack like this? This is such a simple issue that could be fixed really easily, but I have seen reports of it going back quite some time. I'm amazed that it's not been fixed yet.

    Thanks for the reply anyway.

    Wednesday, July 7, 2010 6:48 PM
  • Hi Mr. Yossu,

    That was exactly my reaction when I saw the workaround posted on StackOverflow. I continued researching for a better way until I gave up eventually.  It's clumsy and confusing for newcomers, but once you've implemented it it's not that bad. In real life you usually don't have to update your model that often anway.

    Friday, July 9, 2010 1:21 PM
  • Hi Mr. Yossu,

    That was exactly my reaction when I saw the workaround posted on StackOverflow. I continued researching for a better way until I gave up eventually.  It's clumsy and confusing for newcomers, but once you've implemented it it's not that bad. In real life you usually don't have to update your model that often anway.

    Which StackOverflow article did you mean? I can't see one in this thread.

    Thanks

    Sunday, July 11, 2010 4:44 PM
  • I'm afraid I haven't bookmarked it, but it really yields no addional information apart from what I have already posted.
    Friday, July 16, 2010 10:01 AM
  • I am trying to create an Entity Framework 4 model based on a legacy SQL server 2008 database. Since the model is going to be published in a web service, I need to omit an integer column called NewsletterSubscribed from one of the model's entities.

    Hi Adrian,

    Here's an alternative (3rd party) workaround: in the latest release of my add-in package for Visual Studio, Huagati DBML/EDMX Tools, there is a new feature for EFv4 models called the Model Comparer.

    The model comparer shows a breakdown of the differences between your database and the model, and allows you to select individual differences to bring across. Differences detected at column/member level for entities includes data type changes, maxlength, scale/precision, default values, nullability etc.

    Differences that you don't want to bring across can be added to an 'ignore list' so they will not show up in the future, and any changes you bring across will only update the part of the model directly associated with them (unchanged entities remain untouched etc).

    Model Comparer for EFv4 in Huagati EDMX Tools

    You can download the add-in and get a trial license at http://www.huagati.com/dbmltools if you want to try it out. There's also a short intro video showing how it works at http://huagati.blogspot.com/2010/07/introducing-model-comparer-for-entity.html

     


       Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools - add-in with new useful features for the L2S and EF designers in VS2008 and VS2010
    huagati.com/L2SProfiler - Query profiler for Linq-to-SQL, Entity Framework v4, and LLBLGen Pro
    • Edited by KristoferA Monday, August 9, 2010 12:09 PM
    Friday, July 30, 2010 2:09 AM
  • None of these responses really solve the issue.  From my point of view this is a BUG with the Entity Framework v4 that it does NOT save default values from columns in SQL tables into the entities it generates in the model.

    I hope the Entity Framework team will prioritize this as a bug that must be fixed in the next release of Entity Framework.

    Sunday, August 8, 2010 11:02 AM
  • None of these responses really solve the issue.  From my point of view this is a BUG with the Entity Framework v4 that it does NOT save default values from columns in SQL tables into the entities it generates in the model.

    I hope the Entity Framework team will prioritize this as a bug that must be fixed in the next release of Entity Framework.


    The model explorer described in my reply above does provide you with a way to detect differences in default values; if a db column has a constant default constraint that is not present in the SSDL it will show that and allow it to be brought across. Likewise, if the default value for a member differs between the CSDL and SSDL it will show that and allow you to bring them in sync. (and the same goes for a whole pile of other attributes on members/columns, entities/tables etc)
       Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools - add-in with new useful features for the L2S and EF designers in VS2008 and VS2010
    huagati.com/L2SProfiler - Query profiler for Linq-to-SQL, Entity Framework v4, and LLBLGen Pro
    Monday, August 9, 2010 12:12 PM

  • The model explorer described in my reply above does provide you with a way to detect differences in default values; if a db column has a constant default constraint that is not present in the SSDL it will show that and allow it to be brought across. Likewise, if the default value for a member differs between the CSDL and SSDL it will show that and allow you to bring them in sync. (and the same goes for a whole pile of other attributes on members/columns, entities/tables etc)

    Sure it provides a way around the bug, but that doesn't take away from the fact that it's a bug.

    And anyway, proposing a solution that requires us to spend money on a third-party product in order to get around an obvious and basic bug is hardly a solution.

    Tuesday, August 10, 2010 1:42 PM
  • Sure it provides a way around the bug, but that doesn't take away from the fact that it's a bug.

    And anyway, proposing a solution that requires us to spend money on a third-party product in order to get around an obvious and basic bug is hardly a solution.

    Sorry, I was just trying to provide a workaround. Please keep in mind that it is totally voluntary to try it out and/or actually use it.

    I'm sure Microsoft will provide a cheaper and better fix or workaround to this issue at some point in time.

    As for bugs, to draw Microsoft's attention to the issue you may want to check if there is a Connect item filed for it (connect.microsoft.com). If not, file one, and/or if there already is a connect item then upvoting it should help them judge the importance of it and in turn give it the right priority.


       Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools - add-in with new useful features for the L2S and EF designers in VS2008 and VS2010
    huagati.com/L2SProfiler - Query profiler for Linq-to-SQL, Entity Framework v4, and LLBLGen Pro
    Tuesday, August 10, 2010 3:11 PM
  • Hello Kristofer,

    I hope I didn't sound rude, it wasn't meant that way. I appreciate your replies. I guess it's just a frustrating issue when there's such an obvious bug in the framework.

    Anyway, I had a look at connect, but couldn't find a report of this bug, so I submitted one. Maybe if people here can go in and vote on it, it might bring it to MS's attention.

    The URL for the bug report is https://connect.microsoft.com/data/feedback/details/585163/column-must-be-mapped-although-it-does-have-a-default-value

    Thanks

    Tuesday, August 10, 2010 5:03 PM
  • I hope I didn't sound rude, it wasn't meant that way. I appreciate your replies. I guess it's just a frustrating issue when there's such an obvious bug in the framework.


    No, you didn't sound rude. I just wanted to clarify why I provide these add-in workarounds. :)

    To elaborate a bit more on it: Microsoft tend to have very long product cycles, so even if they're aware of issues like this it can sometimes take a bit of time for them to provide solutions/fixes. (The behavior described above where the designer only updates the CSDL and update-model only updates the SSDL without providing any user feedback that there are inconsistencies between the two layers has been around since the VS2008 EF designer.)

    In the meantime I hope my add-in makes it easier for developers to use EF and L2S. Unfortunately I can't make it available for free; I have spent too much time on it to be able to distribute it for free although people sometimes suggest I should do that.

     


       Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools - add-in with new useful features for the L2S and EF designers in VS2008 and VS2010
    huagati.com/L2SProfiler - Query profiler for Linq-to-SQL, Entity Framework v4, and LLBLGen Pro

    Wednesday, August 11, 2010 2:21 AM