The Entity Framework Designer include a reverse engineer feature called “Generate Database from Model” that enables a “Model first” workflow, and also enable you to persist any model modification in a new database.
The T4 template used for this feature supports both SQL Server and SQL Server Compact, but unfortunately lacks consistency in it’s use of the GO keyword. GO is used after each statement, except FOREIGN KEY constraint creation statements. Apart for being inconsistent, this also prevents SQL Server Compact Toolbox from executing the script, without a number of manual edits.
I have proposed a fix on Codeplex for the next version of the designer, but it will not happen until version 7 (if ever).
So in the meantime, I have updated the template to fix this, you can start using it today as replacement for the current one as follows:
1: Download my updated T4 file from here. (The same file is used in both Visual Studio 2010 and 2012)
2: Copy the downloaded SSDLToSQL10GOFixed.tt file to the
C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen folder
(for VS 2012)
or to
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen
(for VS 2010).
3: With the Entity Framework designer open, go to properties, and select the new file as the DDL Generation template:
4: Generate the script.
Thanks Erik.
ReplyDeleteHello,
ReplyDeleteThat worked the first time I did the procedure you mentioned but now it's not working anymore no matter what I do. I've re-opened VS, rebooted the PC, deleted connection strings, re-created another database, another model etc ... it's not generating anymore !
After some inspection, I've found that the file was 'blocked' by Windows (File properties with right-click on Explorer), I unblocked it but that didn't help either.
Any ideas ?
Thank you.
Aybe: Do you have the corect Tool property selected?
ReplyDeleteYou mean 'DDL Generation Template' ?
ReplyDeleteIf so, yes, it is set to 'SSDLToSQL10GOFixed.tt'.
Aybe: No idea...
ReplyDeleteI tried this in VS2012 Express with a single entity and all the defaults except for changing to SSDLToSQL10GOFixed.tt, no joy. It appears to create an empty database - with no tables. Do I have to execute the .sqlce file, or should it run automatically? Any pointers much appreciated.
ReplyDeleteSquashed: Does you Entity have a primary key?
ReplyDeleteI've lost all hopes on creating them the regular way, there might be issues with my installation, don't know, I've re-installed VS but that didn't do anything (re-installing my whole PC is something I would like to avoid). I am setting up a VM to see whether a fresh install would solve it.
ReplyDeleteThe fix I've found (yours actually !) is running the script in the editor of SQLCE Compact Toolbox. It works fine though it brings the following error :
Error Code: 80040E14
Message : There was an error parsing the query. [ Token line number = 9,Token line offset = 2,Token in error = ]
Minor Err.: 25501
Source : SQL Server Compact ADO.NET Data Provider
Num. Par. : 9
Num. Par. : 2
Not sure what's the cause since line 9 is a blank line.
But tables are created/updated :-)
Source :
http://stackoverflow.com/questions/15187628/how-to-combine-entity-framework-and-sql-compact-in-visual-studio-2012
Thank you !
I've talked too fast actually, while the process works it is incompletely done : I created two entities with a one-to-one association, only the first entity gets the association column, not the second one.
ReplyDeleteError Code: 80040E14
Message : There was an error parsing the query. [ Token line number = 5,Token line offset = 2,Token in error = ]
Minor Err.: 25501
Source : SQL Server Compact ADO.NET Data Provider
Num. Par. : 5
Num. Par. : 2
I'll post my results whether a fresh install fixes it...
Aybe: You need to fix the generated script by hand before running it in the Toolbox, as I state in this post: GO is used after each statement, except FOREIGN KEY constraint creation statements. (add GO and newline)
ReplyDeleteYes,
ReplyDeleteI've just setup a vm and guess what, it's not working... Don't know what to think.
What is your setup ?
I did a full installation of VS, then SQL CE 4 SP1, after that I do add EF5 and the EF package that handles SQL CE from NuGet package manager. I tried pre release version too.
Also I did install VS update 2 as well.
Did you install CE 3.5 too ?
What's boring is that there is absolutely no way to find out what is happening under the hood, script runs as I get a warning about it is going to be run and that's it.
I'll do a few more tests today and see if I can find anything about it.
Aybe: Unblock the .tt file. What is not working? Any error messages?
ReplyDeleteSorry I should have been more clear, that's the procedure on your post that is not working in the VM : using the fixed script. I haven't checked whether copying/pasting the code on the toolbox editor works. Also, no error messages.
ReplyDeleteIn the mean time I've setup a SQL Express instance on my PC and it works flawlessly so at least I know my PC isn't broken. New pre-release package of EF was out today, tried it but it didn't work either.
To be continued :-)
I've tried it with both VS 2010 and 2012. No success. Same result as before.
ReplyDeleteThe I tried Code-First with EF and felt in love with it...
Code-First is awesome.
Tohid: not sure what you have tried, but agree, Code First is nice in many scenarios
ReplyDeleteI've used it successfully in VS2012, NET 4.0, EF4. Now I moved my application to VS2013 RC, NET 4.5, EF5 and I get errors:
ReplyDeleteLine 11: The type of namespace name 'Core' does not exist in System.Data.Entity
Line 12: ... 'SqlServer' ... in System.Data.Entity
Line 13: ... 'SqlServerCompact' ... in System.Data.Entity
Line 34: ... 'DbConfiguration' could not be found
A number of namespaces have changed in EF6, I think you have to modify the template...
ReplyDeleteHey Erik,
ReplyDeletethe sqlce-script (generated with the GOFixed template)first tries to drop existing FKs and tables. In it´s comments it says, that "if the constraint does not exist, an ignorable error will be reported."
When I execute the script within your toolbox, the script seems to be interrupted when trying to alter a non existing table, showing this error:
Error Code: 80040E37
Message : Die angegebene Tabelle ist nicht vorhanden. [ Bestellungen ]
Minor Err.: 0
Source : SQL Server Compact ADO.NET Data Provider
Err. Par. : Bestellungen
Is there a way to really ignore those errors and continue executing the script, or do I have to manually delete those lines?
Blome: use my sqlcecmd Tool in order to ignore errors
ReplyDeleteThanks, I will try that.
ReplyDeleteThis is great, thanks for sharing. Also, thanks for the Compact Toolbox - all in all very useful.
ReplyDelete