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: 234236
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

This article has been editorially reviewed by Suprotim Agarwal.

Absolutely Awesome Book on C# and .NET

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Author
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigious Microsoft MVP award for Sixteen consecutive years. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that offers Digital Marketing and Branding services to businesses, both in a start-up and enterprise environment.

Get in touch with him on Twitter @suprotimagarwal or at LinkedIn



Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
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
Comment posted by bn on Wednesday, February 11, 2015 4:46 AM
ggjk
Comment posted by chaaru.v on Wednesday, May 20, 2015 6:50 AM
Thanks for sharing the details!i would like to more information from your side!please added more then tips!Am working in <a href="http://www.jklaptopcare.com">Hp used laptops sale in chennai</a>should you need for any further clarification do not hesitate to reach us back.044-421 27512