Ok this time .NET has failed me big time! After googling for almost two hours to find a solution which allows me to find out all of the constraints I have in a table, drop the constraints I want, drop the column that misses the Identity property, recreate it with the Identity property set to true, I gave up, deleted my .sdf database and re-created all over again. All good so far, eh?
Then I try to add new categories and products using the application I've been working on, and I get an exception saying:
Server-generated keys and server-generated values are not supported by SQL Server Compact
I googled a little bit and found out that SQL CE and the Entity Framework have some issues with auto generated keys. Therefore I need to find "work-arounds" to over come this limitation, such as generating a GUID to fill in the ID value. I mean seriously, why couldn't Microsoft spend a little more time fixing its SQL CE before releasing version 3.5?! It's not just the issue I've mentioned above, but there also isn't designer support to create diagrams out of a .sdf database, hence you have to create the constraints (Foreign Keys) strictly using SQL queries!
Ok enough nagging but that's seriously an epic
FAIL!