How to Create a Database from a Model using Entity Framework Model First Approach

This article elaborate the steps required to create Entities, relationships, and inheritance hierarchies on the design surface of EDMX and generate a database from it.

Here you can find what is model first approach and what is the advantages of using model first approach.

Below are the steps to create a model and generate a database from the model:

Step 1: Create a class library

Open Visual Studio 2012 > File > New Project > Windows Template > Class Library > Give it a name 'EFModelFirst' > OK 

Delete the default Class1.cs that gets created in the project. 

 

Step 2: Add an Entity Data Mode(Empty model)

Right click on the project in Solution Explorer > Add > New Item Select the Data Template > ADO.NET Entity Data Model and click Add. 

 

In the Entity Data Model Wizard, choose 'Empty model' and click Finish.

 

Now we can able to see the designer surface.

Step 3: Add Entities

We can add Entities, there associations or inheritance either by drag and drop them to the design surface from the toolbox or from the context menu.

 

Here I am adding it using context menu. Right click on Designer > Add New > Entity , Then a add Entity Pop up will open. Add the Entity name and Entity Set will automatically pluralize the entity Name. Property name will describe the primary key and its default name is Id. We can change the name also. Property type will show the data type of the property. Here i am changing the Entity and Property name to Employee and Employee Id and click OK. 

Then you can see the Employee entity in the designer.

Step 4: Add scalar property (Fields)

Adding new fields to employee entity by right click on entity > Add New > Scalar Property. We can also rename the field by right click on field > rename. In property window we can find the new field has Default type as string and null able as false.

Here I have added few fields (FirstName, LastName, Gender, phoneNumber, HireDate) to the employee Entity. Similarly I have created another entity with name "Department" (Using Step 3) and property name "Department Id". Added a new scalar property Department Name.

Step 5: Add Association

We can establish relationship among different entities. Here I am going to establish a 1 to many relationship between Department and Employee.(Here 1 department has many employee so departmentId should be the foreign key in the employee table) We can achieve this by right click on any entity > Add New > Association. Then a Add association popup will open. We can define the type of relationship (1 to 1, 1 to many,many to many) on it and click OK.

 

Now our Model is ready, Now we can generate a database from the Model.Before going to generate a Database from a model we must make sure that a empty database should be exist.

Step 6: Create a empty database and connect Model to that database

Create an empty database with the name "EFModelFirstApproach" in SQL server by using this SQL query

                            CREATE DATABASE EFModelFirstApproach;

To create the database we need to go to the design surface Right click > Generate database from model. It will not generate the database rather it will generate the schema. Database need to be preexist (Here we have created EFModelFirstApproach).

If the selected database is correct then Click Next (else go for a new connection option. Select the Server name. You can either choose windows authentication or SQL server authentication.In SQL server authentication you need to provide the username and password. Then select the database name you want to connect and click on test connection. If it will show a successful message then click on OK button and proceed. In generate Database Wizard click next.) Then we will see the Script generated for creating the database. Click on Finish. We will see a file with .sql extension. "DataModel.edmx.sql" name is generated.

In the app.config we will see the connection string.

We can also see the raw metadata from the .edmx file by Right click on datamodel.edmx > Open with > XML (text) Editor > Ok > yes. We will able to see the metadata.

Step 7: Execute schema to create tables

Open DataModel.edmx.sql file. Now we can able to see the database schema. Now we will generate the database from the schema. Right click on the schema > Execute > Connect to the DB and its done.

Step 8: Database created successfully

Now go to the database refresh database Open table and you can able to see the Employee and Department table along with the fields in the Database.

 

Thats it.. Now you have successfully created a database from a model using Model first approach.

Happy coding.. :)

Choosing the Right Entity Framework Workflow

In this article we will learn what is the advantages and disadvantages of each domain modelling approaches and when to use which approach. Here you will find what are the different approaches we use in Entity Framework.

When to use which Model: 

It is very important to know which work flow or approach we need to use for our application development. Some times it depends on developers interest but most of the times it depend on the project structure. Let's discuss which should be our preferable approach in different scenarios. 

(a)Database First approach shines when managing a large, complex database schema with numerous unconventional mapping requirements or if we are working with an existing project (maintenance project) and need to mapping to an existing database. Here we generate a model from that existing database.

(b)If we are working with a new project which don’t have a database and want to use a designer to define the shape of the classes and define a structure to map to the database then its preferable to use Model First approach. So using this approach we can create the database based on the model we defined. Here powerful features of the ORM designer help in creating a model very easily.

(c)Then coming to the last one i.e Code First approach. If we want to write our own POCO classes and use code to define how they map to a database then its preferable to use Code First approach. Code First can generate a database for us or we can use it to map to an existing database. Hard code coders love to use this approach.

(d)Finally, situation may comes where we have existing classes and we want those classes to be used with EF,then its always preferable to use the code first approach even if our first preference would be designer-based modelling because if we will choose to use the designer, then for any model changes in the designer we need to change our classes also. Which is inefficient and error-prone, so we will probably be happier in the long run if we use Code First. In Code First, our classes are our model, so model changes only need to be made in one place and there is no opportunity for things to get out of sync.

Before committing to a particular work flow a developer/system designer must go through the mentioned decisions within the above decision tree.(By Julie Lerman the author of "Programming Entity Framework")

Now Coming to the Advantages and disadvantages of each approaches:

Database First approach:

Advantages:

1. Famous if we have Database which is developed separately or designed by a good database designer or if we have an existing Database. 
2. EDM wizard creates entities, relationships, and inheritance hierarchies for us. As code is auto generated so it decreases the overhead of writing code. 
3. Manual changes to the database is possible here as we generate model from the database. We can always update the model from database. 
4. We can visualize the database structure in EDMX designer. 
5. We can add extra features in POCO entities by using either T4 template or partial classes.

Disadvantages:

1. If we will change anything in model then it won't be reflected in Database.  
2. In case if there is a problem while sync the Database with the model then there may be a chance of deletion of the database and fear of losing data.

Model First Approach: 

Advantages:

1. It is good if we like to visualize the structure of the data in the application or we don't like writing code or SQL . 
2. Good support with EDMX designer. 
3. If we want to add some extra features in POCO entities we can achieve this using partial classes. 
4. We can modify the model and update the generated database. 
5. For small easy projects this approach is very productive.

Disadvantages:

1. In this approach you have no much control over your entities (auto generated code which is hard to modify) and database. So in this case it is rarely used. 
2. Manual changes to database schema is not preferable as entityFramework model defines the database. 
3. When working on a team Model First can sometimes be a pain. This is true in initial development when changes are frequent to the database.

Code First: 

Advantages:

1. It is very popular approach since it allow us to make a more logically and flexible application. 
2. It provides full control over the code as there is no auto generated code.
3. No manual intervention to DB is required as Entity Framework will handle creation of database with its relations. (If DB is not exist previously)
4. Same class is used for defining the database table structure and business object. So any changes in the class affect both the business object and the database table.
5.  We can also use Code first to map our model to an existing database.  

Disadvantages:

1. Manual changes to database schema is not preferable because our code defines the database. 
2. Generated code is not always easy to understand.

 
Hope this will help you. :)

Various Domain modelling approaches in Entity Framework

Every business application has a conceptual data model (explicit or implicit) which describes the various elements of the problem domain as well as each element's structure, the relationships between each element and their constraints.We follow different approaches to deal with these models. Entity Framework also provides three different approaches to deal with the model.

1. Database First 
2. Model First 
3. Code First

Let's discuss these approaches one by one.

Database first approach: 
This is a approach where we created the model codes (classes, properties, DbContext etc.) from an existing database. We achieve this by using Visual Studio’s Entity Data Model Wizard . This approach first comes with Entity frame work 3.5 (Visual Studio 2008 ).

Model First Approach: 
This is a approach where we create Entities, relationships and inheritance hierarchies directly on the design surface of empty model by using entity designer (.edmx file) and then generate database from it. This approach first comes with Entity frame work 4.0. (Visual Studio 2010 ).

Code First Approach: 
This is a approach where we create the classes first and then create database directly from these classes. These classes are called POCO (Plain old CLR objects) classes. In code first, we never use the Entity Designer (for editing .edmx files) rather what we use is the POCO classes. This approach allow us to make a more logically and flexible application that focuses on the behavior of the application rather than the database generated by it. We can also use Code first to map our model to an existing database. This approach first comes with Entity frame work 4.1 (Visual Studio 2010 ).

If you want to know when to use which approach and what is the advantage and disadvantages of the above approaches then you will find it here.

Hope this is helpful. :)

How to create a entity data model using database first approach.

Here we will learn how you can create a entity data model from an existing database (Known as database first approach). Please refer this to know what is a database first approach.

Create an Entity Data Model In a Separate project : 
Below are the steps to create a data model using database first approach (EF 4.5 VS 2012):

Step 1: Open Visual Studio 2012 > File > New Project > Windows Template > Class Library > Give it a name 'EntityFrameworkModel' > OK

Delete the default Class1.cs that gets created in the project. 

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

In the Entity Data Model Wizard, choose 'Generate From Database' and click Next.

Create a new Data Connection or choose from an existing one. If you want to change the database connection then click on New Connection. A popup will open.

Select the Server name.You can either choose windows authentication or SQL server authentication.In SQL server authentication you need to provide the username and password. Then select the database name you want to connect and click on test connection. If it will show a successful message then click on OK button and proceed.

Step 3: Now choose the Database Objects you want to include in your Entity Data Model (EDM). Here I choose all the four tables 'Department' ,'Employee' ,'LogInDetails' and 'Salary'. As i dont have any stored procedure and view in my database so am not going to select any SP and view.If you want to change the name of the model you can change it under model namespace textbox. Click Finish to complete the wizard.

Then you will able to see the .edmx file as below.

You will find the connection string in the app.config file where you will find find the schema files (.csdl, .mls, .ssdl) :

Build the project in Release mode (Go to Build > Build EntityFrameworkModel) and make sure that there are no errors. A model defined by the Entity Data Model is now ready.

If you want to know how to use this entity data model in another .Net application to communicate with the database, then here you will find the link which will help you to achieve this.
 

Hope this is helpful. :)

An Introduction to Entity Framework

What is Entity Framework :

 

Entity Framework (EF) is an object-relational mapper (ORM) that enables .NET developers to work with relational data using domain-specific objects. It basically generates business objects and entities according to the database tables and allow us to perferm basic CRUD (Create, Read, Update, Delete) operations,maintaining relationship (1 to 1, 1 to many, many to many) and also provides ability to have inheritance relationships between entities. 

What is ORM :

ORM is a tool for storing data from domain objects to relational database like MS SQL Server.ORM includes three main parts: 

                1.Domain class objects
                2.Relational database objects
                3.Mapping information on how domain objects map to relational database objects
                  (tables, views & stored procedures).

ORM allows us to keep our database design separate from our domain class design.It also automates standard CRUD operation (Create, Read, Update & Delete) so that the developer doesn't need to write it manually.

Why to use EF  OR What is the Advantage of using it :

• It increases the productivity by reducing the development time. It eliminates the need of writing the data-access code as the framework itself provides the core data access capabilities.So developers can only concentrate on application logic.

• Decreases the maintainability as we have the fewer lines of code to fetch data from database,so fewer lines of code we have to maintain. This is very effective for big projects.

• Applications are free from hard-coded dependencies on a particular data engine or storage schema. Its is a conceptual model which is independent of physical model. We can easily run our application using different RDBMSs (MySql, MS SQL,SQLite ,Oracle etc.), only by changing configurations.

Is it an alternative to ADO.NET :

 

The answer would be "yes and no". Yes because the developer will not be writing ADO.NET methods and classes for performing data operations and No because this model is actually written on top of ADO.NET, which means this framework still uses ADO.NET. So EF can neither replaces nor succeeds ADO.NET.

Important versions history of EntityFramework:

EntityFramework Version	Introduced Features
EF 3.5	Basic O/RM support with Database First approach.
EF 4.0	POCO Support, Lazy loading, testability improvements, customizable code generation and the Model First approach.
EF 4.1	First to available of NuGet package, Simplified DBContext API over ObjectContext, Code First workflow.
EF 4.1.1	In addition to bug fixes for EF 4.1 this patch release introduced some components to make it easier for design time tooling to work with a Code First model.
EF 4.2	This release includes bug fixes to the EF 4.1.1 release.
EF 4.3	Code First Migrations feature that allows a database created by Code First to be incrementally changed as your Code First model evolves.
EF 4.3.1	patch released with bug fixing of EF 4.3.
EF 5.0	Announced EF as Open Source. Introduced Enum support, table-valued functions, spatial data types, multiple-diagrams per model, coloring of shapes on the design surface and batch import of stored procedures, EF Power Tools and various performance improvements.
EF 6.0	It includes many new features related to Code First & EF designer like asynchronous query & save, connection Resiliency, dependency resolution,Improved transaction support etc.
EF 6.0.1	The 6.0.1 patch release is limited to fixing issues that were introduced in the EF6 release (regressions in performance/behavior since EF5).
EF 6.0.2	patch released with bug fixing some performance and functional issues of EF 6.
EF 6.1	This minor release adds few new features to EF6.It includes Tooling consolidation,handling of transaction commit failure and Index attribute

Hope this will help you.. :)

How to Create a Entity Framework model and Use it in a separate .Net Project

This article elaborate the steps required to create an Entity Data Model and re-use it in a separate .Net Project like a Console Application, a Windows Forms project, ASP.NET Project or a WPF Project.

Consider a situation where you have created a entity data model using an existing database and you want that to use in another projects. Then a question arises is this possible ,If yes then how i can achieve this? Below are the steps which will guide you to create a Entity data model and use it in another .Net project. 

Here is my previous link which will describes you how to create a entity data model from an existing database.Once it is created then the below steps will describe you how to use this model in a Console Application.

Using the Entity Model in a Console Application 
 

We will now use the model we created above, in a Console Application.

Step 1: RightClick on solution > Add > New Project > In the templates, select Windows > Console Application. Make sure the target framework is '.NET Framework 4.5'. Give it a name. We are using 'ConsoleAppUsingEFModel'. Click Ok.  
 

Step 2: Let us now add a reference to the model we created earlier, in this console application. Right click on the project > Add Reference > Go to solution folder > Projects > select the EntityFrameworkModel.dll as shown below. Click OK.

Step 3: Add a reference to the System.Data.Entity. Right click on the project > Add Reference > Go to Assemblied folder > Framework > select the System.data.entity.dll as shown below. Click OK.

   

Step 4: Then we need to provide the connection string information to the new console application. You can achieve this in two ways:
 

(a)By adding the connectionstring entry of 'EntityFrameworkModel' into the app.config file of 'consoleAppUsingEFModel' project.  

(b)By copying the App.config file of EntityFrameworkModel project in 'ConsoleAppUsingEFModel' project.(Right click the project > Add Existing Item > Go to the EntityFrameworkModel project and add the App.config file.)
 

Step 5: In this final step you need to add the Entityframework dll from the NuGet Packages. Right Click on Solution explore > Manage Nuget Packages for Solution > Go to Online > NuGet offical package source > In the search box search for EntityFramework > Click on install. Select Project Popup will open. Select the two projects > Click on Ok > License Acceptance popup will open > Click on I Accept > Click on close to close the NugetPackage.

 

Step 6: Build the solution and make sure that it is erorr free.Once the steps shown above are completed, you now need to add data in database (LogInDetail Table) using the below codes. Open Program.cs and add two name spaces :

using System.Data.Entity;

using EntityFrameworkModel;

Write the following query in your Main method.

static void Main(string[] args)

{

        using (var context = new EFDatabaseFirstEntities())

        {

              try

             {

                     var loginDetails = new LogInDeatil

                     {

                            UserName = "pallavipraharaj",

                            Password = "password",

                            DateCreated = DateTime.Now

                     };

                     context.LogInDeatils.Add(loginDetails);

                     context.SaveChanges();

                     Console.WriteLine("EFModel is successfully used in another .net project.");

                     Console.ReadKey();

             }

             catch (Exception ex)

             {

                   throw ex;

              }

        }

}

That’s all! You have now created a model and used that one in another .NET project. 
Here is the demo.Have a look.