Entity Data Modeling with Visual Studio

While there are a lot of tools out there for data modeling, there is one option that is free if you have Visual Studio.  It’s called the Entity Data Model Designer.  It works well if you need a quick-and-dirty way to create a logical model of tables and have the DDL automatically created to build the physical tables in one step.  Or if you want to create a logical model from existing physical tables.

This solution may also solve your needs if you have been using Visio for your data modeling.  With Visio 2010 it’s possible to extract a database schema from an existing SQL Server database (“reverse engineer”) as well as a way to build CREATE TABLE scripts from your resulting diagram (via the workaround Create SQL Server scripts with Visio 2010).  But with Visio 2013 these options are gone (see the Deprecations section at UML and Database diagrams in the new Visio).

For the steps below I am using Visual Studio 2012, but other versions should be similar.  The purpose of the Entity Data Model Designer is to allow you to build logical tables from scratch, then create the DDL that can be run in SQL Server to build the physical tables.  Or, you can generate the logical tables from an existing SQL Server database, modify them, and update the physical tables.  One other feature is that it creates the .NET code to interact with the tables if you are building an application (hence the template name “ADO.NET Entity Data Model”).  In our case, we do not need that so well skip it.

When installing Visual Studio 2012, make sure to choose to install either Microsoft SQL Server Data Tools or Microsoft Web Developer Tools in order to get the Entity Data Model Designer (info).

  1. Open Visual Studio
  2. Create a project or open an existing project (must be Visual Basic, Visual C# project, or Console Application)
  3. Right-click the project and choose Add -> New Item…
  4. Under Visual C# Items select “Data”
  5. Select the template “ADO.NET Entity Data Model”
  6. Give it a name and click “Add”
  7. Select “Generate from database” or “Empty model”
  8. If “Generate from database” selected enter connection info, choose the database objects and done!

The model is stored as a “.edmx” file.

When you complete the model, right-click and choose “Validate” to make sure there are no errors.

If you want to create the DDL, right-click and choose “Generate Database from Model…”.

If there were changes to the physical tables after you created the model, right-click and choose “Update Model from Database…”.

If this solution is not to your liking, the other free option is to use the built-in Database Diagram tool in SSMS (see Getting started with SQL Server database diagrams and video Creating a Database Diagram in SSMS – SQL School Video).

If you don’t mind paying for a tool, some of the more popular one’s are: CA ERwin, ER/Studio, SAP Sybase PowerDesignerDeZign for Databases, and dbForge Studio.

More info:

ADO.NET Entity Data Model Designer

Creating ADO.NET Entity Data Model in VS 2012

Introduction to Model First development using Entity Framework

Video Creating an Entity Data Model from a Database

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQLServerPedia Syndication, Visio, Visual Studio 2012. Bookmark the permalink.

5 Responses to Entity Data Modeling with Visual Studio

  1. Shyam Viking says:

    I am just adding another free data modeling tool Oracle SQL Developer Data Modeler. This has good support from Oracle and has regular releases.

  2. Pingback: My links of the week, 29 December, 2013 | R4

  3. Camillo Gioè says:

    Does anyone know any free data modeling case software?

  4. Kevin Stern says:

    Another option is Toad Data Modeler. There is a freeware version available and the full version is $495 per seat.

  5. Bart Edgerton says:

    Finally got this working for Visual Studio 2013.. YOU MUST create a project of “console application” type to get the entity framework tools, then install the NuGet add-in, and install 3 or 4 other packages, finally, you get to see what a bag of rocks VS is for data modeling.. It completely fubarred even the simplest data model (reverse engineering), and is so limited that a simple cross-reference table is intolerably difficult.

    I guess I’ll just reinstall Visio 2010. Thanks very little, Microsoft..