Home Dashboard Directory Help

Entity Designer ignores DEFAULT CONSTRAINT on columns in SQL Server by SAinCA


 as By Design Help for as By Design

Sign in
to vote
Type: Bug
ID: 699197
Opened: 11/4/2011 5:05:11 PM
Access Restriction: Public
User(s) can reproduce this bug


Standard "who" columns typically include a datetime of "when" the change occurred. For multi-time-zone applications, the decision is frequently made to standardize the values relative to a time-zone or UTC, so that if a user on the East Coast of the USA records an update and one on the West Coast updates at the same UTC time, both record the same updated_when datetime value. In my case, the DB server is under EST and Users are anywhere from East to West plus AK and HI, so we use the server-time. Knowing the User's "home" UTC Offset, the data can be displayed in local time when needed.

The problem lies in the fact that according to the DB schema, the "updated_when" column is NOT NULL. It also has a DEFAULT CONSTRAINT of (GETDATE()) to _always_ assign a value. LightSwitch gives NO OPTION but to supply a valid DateTime value for every INSERT and UPDATE. The presence of the DEFAULT CONSTRAINT clearly renders this unnecessary but there is NO facility in LS to override this behavior.

Please modify the Entity Designer so that selected columns can be changed from Mandatory to Optional WHERE A DEFAULT CONSTRAINT EXISTS IN THE DATABASE. Provisions made by the Data Architect/DBA should be recognized by the Designer Tool and overrides permitted.
Sign in to post a comment.
Posted by SAinCA on 11/29/2011 at 10:55 AM
Thanks, Dan, good to know there's a potential alternative way. Your comment about inferring the model vs. the Developer customizing the model might help in resolving the VIEW issue I posted at https://connect.microsoft.com/site1231/feedback/details/699163/entity-designer-mishandles-view-inclusion-re-key-and-read-only - at least that's my hope...

I'll keep watching with interest.


Posted by Microsoft on 11/29/2011 at 10:42 AM
Hi SAinCA,
Admittedly we (LightSwitch) don't have direct control as to what EF will support but your feedback points out a possible experience in which LightSwitch would allow you to attach to customized EF EDMX model rather than the current way where we infer the model from the DB. This is something that is in our backlog and would go someway in supporting your scenario in that it would allow you would be able to set NOT NULL in the model for your DEFAULT CONSTRAINT field.

So your input definitely adds more weight to this backlog item.

Best regards,
Dan Leeaphon
Visual Studio LightSwitch
Posted by SAinCA on 11/8/2011 at 12:42 PM
Thanks, Dan.

How will I find out if the EF Team have deigned to support common SQL Server artifacts such as DEFAULT CONSTRAINTs? As this is "Resolved", there seems no likelihood this item will get an update... I could be wrong...

I'd like to reinstate the NOT NULL property I just removed from all my "Who" columns for the new tables I've just defined.

Thanks again. Your efforts to increase the functionality in, particularly, the Data Designer are greatly appreciated. We're looking to do as much as we can with LightSwitch to save enormous amounts of software spend, so every additional feature is particularly welcomed.
Posted by Microsoft on 11/8/2011 at 11:11 AM

Thank you for your feedback.

Unfortunately, the Entity Framework metadata that we use to derive our schema from the database does not allow for a description of DEFAULT CONSTRAINT. It is a known limitation of our dependent subsystem. Nevertheless we will continue to pursue this issue with EF.

Dan Leeaphon
VS LightSwitch
Sign in to post a workaround.