Microsoftâ€™s Entity Framework 4.0 includes the ability to generate databases from the models being defined.Â This feature is also sometimes known as forward-mapping (Telerikâ€™s OpenAccess ORM has supported forward-mapping for awhile).Â This enables developers to start with models, instead of starting with the database.
If youâ€™re interested in how to use this feature, Gil Fink wrote a detailed blog post that describes how to use the model first feature in Entity Framework 4.0.Â After following these instructions, Entity Framework will generate a SQL script that must be manually executed to create the database and its tables.
Simply execute this SQL script and the database will be created.Â Easy right?
And thatâ€™s when everything went downhillâ€¦
I was running this little test project from my home computer.Â On this computer, Iâ€™m using Visual Studio Web Developer Express and SQL Express.Â Iâ€™m also using attached databases (AttachDbFilename).
These are free tools that Microsoft is offering to small & casual developers.Â These tools are part of an attempt by Microsoft to entice the PHP audience.Â Hopefully these tools will help halt the severe butt kicking IIS is getting in adoption:
There are tons of online examples showing how to use these free tools to accomplish simple â€˜Hello Worldâ€™ style examples.Â However, I am continually astounded at how quickly these tools fall short in a real projects.Â Then youâ€™re left researching errors and reading how to guides that assume youâ€™re using the Visual Studio Ultimate Gold Edition.Â I guess itâ€™s back to PHPâ€¦
But donâ€™t leave yet, I can help you past this issue.
Step 1:Â Running the Entity Framework SQL Script
As mentioned, Iâ€™m using Visual Studio Web Developer Express.Â I donâ€™t have SQL Server Management Studio installed (there is an express version available).Â And even if I did, itâ€™s a giant pain (because of file locks) to add, and then remove, an attached database file using SQL Server Management Studio.
Here is what I did to execute this generated SQL script inside Visual Studio Web Developer Express:
1.Â Double-click the MDF database file in App_Data in the Solution Explorer
This should open the Database Explorer.
2.Â Right-click the MDF and click New Query
This query editor is not meant for executing a large SQL scripts.Â Consequently, Visual Studio will throw up a barrage of user-friendly dialogs that have nothing to do with our intent.Â Just keep clicking close.
3.Â Copy and paste the Entity Framework SQL script into the â€œSELECT FROMâ€ textbox:
4.Â Click the Execute Query icon <- but it wonâ€™t work
Tons and tons of errors
Visual Studio will attempt to execute the SQL script, however there are lots of problems with the SQL script that will prevent the SQL script from being executed successfully.
After clicking the Execute Query button, Visual Studio will display a warning: The Set SQL construct or statement is not supported
This warning is displayed because Visual Studio is attempting to parse and display a graphical representation of this query.Â Because of the complexity of this SQL script, Visual Studio is choking in the attempt.
Click Continue to say whatever, and continue on.
Then comes a barrage of other errors:
Step 2: Fixing the Entity Framework SQL script errors
There are 2 core issues with the Entity Framework generated SQL script.
- When using attached database files it is unnecessary to tell the database server which database to use.Â Consequently, the USE [Demerits] command is invalid.
- The GO command is not valid SQL.Â This is a made up command that is used by SQL Server Management Studio.Â Visual Studioâ€™s query builder (and most other SQL clients) do not recognize this command.Â All GO references need removed.
To fix these errors:
1.Â Remove the USE [Database] command from the SQL script.
2.Â Do a search & replace (you canâ€™t do this using the Query Builder, use Notepad instead) and replace all the GO keywords with nothing.Â This will remove all the GO keywords.
3.Â Click the Execute SQL button to re-run the SQL script