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:
[ Chart courtesy of Netcraft ]
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
