Fixing Entity Framework Model-First SQL Express Errors

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.

The EDMX SQL script generated by Entity Framework

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:

Apache continues to hand IIS its rear 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

The Database.mdf file in Visual Studio's Solution Explorer

This should open the Database Explorer.

2.  Right-click the MDF and click New Query

Executing a New Query using Visual Studio's Database Explorer

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.

The Add Table wizard for Visual Studio's Query Builder

3.  Copy and paste the Entity Framework SQL script into the “SELECT FROM” textbox:

Pasting the Entity Framework EDMX generate script into Visual Studio's Query Builder

4.  Click the Execute Query icon <- but it won’t work

The Execute Query button in Visual Studio's Query Builder

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

Query Definitions Differ: 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:

Error Message: Incorrect syntax near 'GO'.  Database 'Demerits' does not exist.  Make sure the name is entered correctly.

Generated from EDMX file.  Error Message: Incorrect syntax near 'GO'

Step 2: Fixing the Entity Framework SQL script errors

There are 2 core issues with the Entity Framework generated SQL script.

  1. When using attached database files it is unnecessary to tell the database server which database to use.  Consequently, the USE [Demerits] command is invalid.
  2. 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

Finally!  Query has executed successfully.

  • snorfys

    Can I hug you?

    In all seriousness though, thanks.

  • Anonymous

    Cheers! this was the same problem I was getting.

  • Paulo

    I am doing a triple mortal jump , extreme happy++ tks for solving this problem.

  • Anonymous

    Thanks you so much!! I am having exactly the same problem. Now it has been fixed thanks to your excellent article…

  • Ashkan

    Thx, couldnt find any other solution.

  • Oiseau

    I want to hug you as well! Whole day of searching and finally you… :)