ADO.net to Lightswitch issue

Question

I've deploy my database through ADO.net Entity Data Model by executing its diagram output query against my pre-made online database.

My database contains Only Lightswitch supported relations, One-Many, ZeroOne-Many & One-ZeroOne.

Finally, Attaching my database to my Lightswitch application as an external database and I was shocked by its result.

Lightswitch converts ALL One-ZeroOne relations to One-Many relations!

I suspect everything, retry tens of times trying to figure this strange issue out with no good news!

Question:

Why Lightswitch converts ALL One-ZeroOne relations to One-Many relations? Inconsistency!

score 0 · Answer 1 · answered Dec 24 '12 at 08:15

0

Unless this is a bug, it would depend on the nullablility of your PK/FK. LightSwitch will not allow 0-1 for any key property that is not nullable.

answered Dec 24 '12 at 08:15

Yann Duran

ADO.net doesn't have foreign keys for 0-1 relations, it's just the navigation property, also navigation properties don't have a null-able option. – Ahmed Ghoneim Dec 24 '12 at 11:41
ALL relationships must have a FK property in the "main" table, & a PK property in the "related" table. Navigation properties just hide these from normal use, but they're still there (you can still read the ID value of a navigation property, you just can't set the ID directly), or there'd be nothing to relate the two tables on. If the FK property is NOT nullable, LightSwitch will NOT allow a 0-1 relationship. It's that simple. – Yann Duran Dec 24 '12 at 16:13

score 0 · Accepted Answer · answered Jan 01 '13 at 16:19

Finally, I've solved it by a brute-force-like trials :S

Solution : ( Please don't ask me a WHY question as IDK will be my answer )

After adding a One-to-ZeroOne Relation you might have one or two Navigation Properties added to associated tables, but

You MUST add a Foreign Key as Scalar Property to the ZeroOne Table with Same type & true Nullable properties.
You MUST adjust the Residential Constrain for the association by double clicking the association line itself and make the FK in the ZeroOne Table dependent on the One table Key.
Generate the database script, execute it and Don't Update LightSwitch data source, Delete data source & Attach it again instead as it won't update relations correctly!

2 Answers2