Create an Entity Data Model From a Database – Entity Framework 4.0

Posted by: Suprotim Agarwal , on 11/3/2010, in Category Entity Framework
Views: 144553
Abstract: In this article, we will create our first Entity Framework application using Visual Studio 2010. We will create an Entity Data Model (EDM) from an existing database.

In my previous article, Entity Framework 4.0 FAQ – Getting Started Guide we looked at some of the most frequently asked questions on Entity Framework 4.0. Make sure you read it before you move ahead.

In this article, we will quickly jump to create our first Entity Framework application using Visual Studio 2010. Before that, I want to introduce you to a core component of the Entity Framework, the Entity Data Model (EDM). The EDM describes the structure of your Business objects, which includes the data types, relationship types, schema mapping and so on. In simple words, EDM is a bridge between your application and data store and lets you work at a conceptual level with your data, rather than the actual schema.

With Entity Framework 4.0, you can do the following:

• Generate a Model from the Database (Database First approach)
• Generate a Database from a Model (Model First approach – new to EF 4.0)
• Code-Generation using T4 Templates and POCO (Code First approach – new to EF 4.0)

Let us create an Entity Data Model (EDM) from an existing database, say Northwind. I assume you have either downloaded Visual Studio 2010 Ultimate Trial Edition or the free Visual Studio 2010 Express Edition. Let’s get started:

Step 1: Open Visual Studio 2010 > File > New Project. In the templates, select C# or VB, Windows > Console Application. Make sure the target framework is ‘.NET Framework 4.0’. I have called the application ‘EFConsole’. Click Ok.

Step 2: Right click on the ‘EFConsole’ project in Solution Explorer > Add > New Item. Select the Data Template > ADO.NET Entity Data Model and click Add.

Note: If you plan to use the same Entity Model in different projects, then make sure that you create your Entity Model as a separate class project. This way, you will be able to add its reference in multiple projects.

Add Entity Data Model

Step 3: The Entity Data Model Wizard appears. Choose ‘Generate From Database’ and click Next

DataModel Wizard Choose Model

You have to now choose your Data Connection. If you have created connections previously, then they will show up in the list. I assume you have not, so click on the ‘New Connection…’ button and set up the connection properties to the Northwind database, as shown below:

EF Northwind Connection

Click OK to return to the wizard and you will see that the new connection is visible in the data connection dropdown.

Entity Framework Data Connection

Our Entity Connection Settings will be stored in the App.Config file as ‘NorthwindEntities’. You can change the name to anything else you want. Click Next

Step 4: The next step is to choose the Database Objects you want to include in your Entity Data Model (EDM). To keep it simple, I am choosing two tables ‘Customers’ and ‘Orders’. Call the model namespace as ‘NorthwindModel’

EF Nwind Model

The checkbox ‘Pluralize or singularize generated object names’ gives us the ability to pluralize or singularize object names. This is new to EF 4.0 and is a welcome change, as in EF 3.5, not being able to rename/pluralize objects in the model, caused a great deal of confusion.

Click Finish to complete the wizard.

EF Model edmx
Congratulations, our EDM is ready! You have just generated a Model defined by EDM, from the Northwind database. The screenshot shown above is the representation of your Entity Data Model in the designer.


Query against the Entity Data Model

If you recall, I had mentioned in my previous article Entity Framework 4.0 FAQ – Getting Started Guide that we will be programming against a logical model rather than a physical database. You can use ‘LINQ to Entities’ or ‘Entity SQL’ to write queries against your model defined by EDM. Entity Framework behind the scenes uses ADO.NET provider to translate these queries to what SQL Server understands and bring back the results.

Note: Since we are connecting to SQL Server, we will not need any additional providers to make a connection. If you plan to use Oracle, DB2, MySQL etc, make sure you look at the additional 3rd party providers for these databases.

In this step, I will show you how to query against the model you have just created. We will write code that displays the CustomerId and CompanyName of customers who have placed orders after the 1st of January, 1998.

Open Program.cs and write the following query in your Main method

static void Main(string[] args)
{
    using(var context = new NorthwindEntities())
    {
        var custWithOrders = context.Orders
            .Where(o => o.OrderDate > new DateTime(1998, 1, 1));
        foreach (var cust in custWithOrders)
        {
            Console.WriteLine("{0} from {1} Ordered Item on {2}",
                cust.Customer.CustomerID,
                cust.Customer.CompanyName,
                cust.OrderDate
                );
        }
        Console.ReadLine();
    }

} 

The following output should be generated on your console window

EF Query Results

There you go! You have written your first query against your model. I hope you now see how EF provides a shift from Database-oriented (DataReader, DataSet) to Model-oriented development. So instead of focusing on a Database, a developer starts focusing on the Entities that represents the Business Model of the application.

I know there are a lot of questions running in your mind about the query we just wrote. Just hold on to these queries for now. They will soon get cleared in the forthcoming articles.

Update: If you are curious to know how EDM generated the code and translated the queries to something that SQL Server understands, read my article Exploring how the Entity Data Model (EDM) Generates Code and Executes Queries – Entity Framework 4.0

I hope you liked this article and I thank you for viewing it.

The entire source code of this article can be download over here

Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles
Suprotim Agarwal, ASP.NET Architecture MVP, MCSD, MCAD, MCDBA, MCSE, is the CEO of A2Z Knowledge Visuals Pvt. He primarily works as an Architect Consultant and provides consultancy on how to design and develop .NET centric database solutions.

Suprotim is the founder and primary contributor to DotNetCurry, DNC .NET Magazine, SQLServerCurry and DevCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls. and is authoring another one at The Absolutely Awesome jQuery CookBook.

Follow him on twitter @suprotimagarwal


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by Jatin Kacha on Wednesday, November 3, 2010 5:58 AM
Good article,

Suprotim, good move from you that you have started series of articles for the EF 4.0.
Till now, its ok. I will be looking for some real example that really separate it from Linq To SQL.

Waiting for further read.

Jatin

http://jatinkacha.wordpress.com/
Comment posted by Suprotim Agarwal on Wednesday, November 3, 2010 6:15 AM
Jatin: Good to hear that you like the series so far. Since it is a step-by-step series, it will take a couple of more articles before me jump and explore the real power of EF. Nevertheless, I am sure even experienced developers like you will learn something new from this series.
Comment posted by Mostafa on Wednesday, November 3, 2010 2:44 PM
Thank you , I'm looking for next article , specially Generate a Database from a Model
Comment posted by Phillip G on Friday, December 3, 2010 11:15 PM
Once I have created the model, can i use it in different projects, like bind a gridview with this model?
Comment posted by Suprotim Agarwal on Sunday, December 5, 2010 2:14 AM
Phillip: Great question. To use a model in multiple projects, you have to create it as a class project. Give me a couple of days and I will create an article on how to do it.
Comment posted by Thirumaleshwar Reddy on Thursday, February 10, 2011 6:06 AM
This example is not working. I am getting error as : Object reference not set to an instance of an object.
Comment posted by Suprotim Agarwal on Friday, February 11, 2011 6:27 AM
Thirumaleshwar: Which line throws that error? Did you debug the application?
Comment posted by Dadasaheb on Thursday, March 3, 2011 4:51 AM
Very good Series on EF 4.0
if i want to use this EF 4.0 for a big Enterprise application... then i will need to select all the table suppose 200 to 300 tables in Entity Data Model.
will this work ?
if it works and later on if i have to add or remove some columns from some tables then how it will work?
it will be great help if you clarify the mentioned scenarios.

Thanks in advance
Dadasaheb
Comment posted by Malcolm Dixon on Tuesday, April 19, 2011 5:17 AM
You said to choose c# or vb but then only show how to do query in c#! Being completely new to EF and LINQ I'm lost. I've just started Java so understand C# to an extent, but the LINQ or Entity SQL, which ever it is, I don't understand. For example where does the o alias come from in the Where command? Why are the variables declared as var, is there not a proper type? Any advice appreciated, thanks. Malc.
Comment posted by Malcolm Dixon on Tuesday, April 19, 2011 6:20 AM
I've just watched a video by Julie Lerman http://msdn.microsoft.com/en-us/data/ff628210, which helped me. I'm not using Northwind but AdventureWorks, here's my code to help any newbie VB codes like me.

    Sub Main()
        Dim context As AdventureWorksEntities

        context = New AdventureWorksEntities

        Dim custWithOrders As IQueryable(Of SalesOrderHeader)

        custWithOrders = From o In context.SalesOrderHeaders Select o Where o.OrderDate > New DateTime(2004, 7, 30)

        For Each o As SalesOrderHeader In custWithOrders
            Console.WriteLine("Customer ID: {0} Account: {1} placed an order on {2}", o.CustomerID, o.Customer.AccountNumber, o.OrderDate)
        Next
        Console.ReadLine()
    End Sub

Malc.
Comment posted by Suprotim Agarwal on Wednesday, April 20, 2011 3:10 AM
Malcolm: Yes this article series assumes you have some basic knowledge of C# and LINQ. Microsoft has created a Getting Started in LINQ in C# (and VB) series here http://msdn.microsoft.com/en-us/library/bb397933.aspx

Comment posted by spin on Wednesday, January 4, 2012 2:44 AM
I've tried to use northwind.sdf as a database and have done all the steps from your article but console didn't generate any output but black window. Where could be a problem?
Comment posted by Pankaj Kumar on Wednesday, May 9, 2012 6:42 AM
Great Article..Loved it..Look forward some new articles..:)
Comment posted by rakesh on Thursday, May 17, 2012 2:42 AM
how can i query the database in my website applications???
Comment posted by Rohan on Thursday, November 29, 2012 12:32 AM
Good article bro.
it is really very helpfull
Comment posted by Jewel on Wednesday, January 16, 2013 10:27 AM
Nice Article For The Beginners Like Me.
Thanks a Lot...
Comment posted by ST on Wednesday, May 8, 2013 4:24 AM
You saved my day.
Can't thank you enough for putting this code online.
Comment posted by Pradip Daware on Saturday, May 18, 2013 3:23 AM
no comments
Comment posted by Stanley Subi on Thursday, November 14, 2013 10:27 AM
Hello Ill be working with linq to sql and I have the Buttom y the code for save delete, only miss Update. I need a Example if you have or create a example with Linq to Sql ASp.Net C# with each buttom Delete, Update, and Save and Respective Code, and we the users .net we goin to learn better, I'm Junior Developer in Asp.Net, and I need the code for the Buttom Update... From Dominican Republic Be Waiting Answer.....
Comment posted by JOE WAZEN on Thursday, November 21, 2013 7:35 AM
Hello,

thank you for this post

i used your post to generate an SDF DB from an SQL DB, however the generated SDF is not holding the Default values of the fields in tables while default values are found in the original SQL.

Please Help
Comment posted by DeiverCM on Thursday, June 19, 2014 10:34 AM
¿Cómo se hace en el caso de MySQL?. No aparace como proveedor de datos. Solo parace MySQL Server, Conpat Server y Access. Tengo versión express.
Comment posted by EMMANUEL on Tuesday, September 16, 2014 6:21 PM
You have been sub-contracted to design the database system supporting the Human
Resources department for the “STARTUP” company. The initial analysis of
the HR Department has shown the following:
1 - The company is organized into departments.
2 - Each department has a unique name, a unique number, and a particular employee who manages the department.
3 - We keep track of the start date when that employee began managing the department.
4 - Only supervisor can manage the department.
5 - A department may have several locations.
6 - A department controls a number of projects, each of which has a unique name, a unique number, and a single
location.
7 - We store each employee’s name, Social Security number, address, salary, sex(gender),
and birth date. An employee is assigned to one department, but may work on several projects, which are not
necessarily controlled by the same department.
8 - The employee cannot be assigned to more than three projects at a time.
9 - We keep track of the current number of hours per week that an employee works on each project.
10 -We also keep track of the direct supervisor of each employee (who is another employee).
11 - We want to keep track of the dependents of each employee for insurance purposes.
12 - We keep each dependent’s first name, sex, birth date, and relationship to the employee.
Comment posted by emm on Tuesday, September 16, 2014 6:23 PM
i need the answer ungently
with datamodeler

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel