Model-First Design using ADO.NET Entity Framework 4.0

No.of Views9301
Bookmarked1 times
Downloads 
Votes1
By  Chinna Srihari   On  20 Jul 2010 12:07:44
Tag : ADO.NET Entity Framework , How to
In Model first approach, we will be creating the EDM, conceptual model, first with respect to the business, and keep evolute the EDM till it fits for the business and then generate the database schema based on the EDM. At least it makes easier to limit the huge changes.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

In this article we are going to see the second most impartment part of the ADO.NET entity framework 4.0. As the first part is traditional way of developing applications like, create Database schema and then creation of entity class follows. In this approach any changes to the schema after developing the conceptual entity classes needs to modified, even though it is quite simple to update the entity class from the schema, in case of big modifications, due to business change, it is time consuming process to synchronize the UX code and the entity class code.

In Model first approach, we will be creating the EDM, conceptual model, first with respect to the business, and keep evolute the EDM till it fits for the business and then generate the database schema based on the EDM. At least it makes easier to limit the huge changes. We will see the what limitations we have in this approach, everything comes with limitation

We will be learning following

1.  Create Empty Model.
2.  EDM Designer.
3.  Creating Entities.
4.  Creating Associations and Relationships between Entities.
5.  Generating Schema SQL and Creating Database Schema.
6.  Performing CRUD Operations on Bands Entity

Create Empty Model

A.  Create a solution called ModelFirstDesign.sln. I am using ASP.NET Web application template for this sample, VSTS 2010 Ultimate trail version.
 
B.  Add a new class library project to the solution and name it as ProjectTeam. As of now our primary focus is on creating entity class and the database for the same.

C.  Add an ADO.NET Entity model item and name it as ProjectTeam.edm

D.  In the wizard select the Empty model and click on finish.

EDM Designer

E.  We will examine the empty mode and its tool box that helps us in creating the EDM entities, entity associations and Inheritance.

a.  Entity: Allows you to design and create an Entity.
b.  Association: Lets you create an association (or relationship) between two entities.
c.  Inheritance: Lets you create an Inheritance relationship between two entities.

Creating Entities

F.  Now we will create the entities in the model.

A.  Following are the entities we will be creating in the EDM.

a.  ServiceLine: This is contains the service line details that a resource can belong to.
b.  ProjectTeam: This entity will contain the individual team types. Like Dev, Test, DB and Support teams
c.  ResourceDetails: This entity will contains the individual member details and which ServiceLine he/she belongs to and ProjectTeam
d.  Brand: This entity tracks the resource brand.

B.  Creating individual entities. Drag an entity instance from the tool box. By default each entity should have an identity column to identity uniquely in the object cache. Try to learn about the ObjectStateManager, ObjectStateEntity and EntityState which is currently out of scope here.
 
C.  By selecting the Entity1 properties change the name of the Entity to ServiceLine
 
D.  By selecting the Id property change the name to ServiceLineID. Since this id we need as primary key make sure that the following properties set

E.  By right clicking on the Entity add the following scalar properties. Also repeat the same steps for all the Entities in the table given bellow.

 

Image Loading

So for your model should look like bellow

Creating Associations and Relationships between Entities

F.  To create the relationship with between the table. Click on the Association in the tool box. Drag the ServiceLineID from ServiceLine to ResourceDetail entity and repeat the same for all of them.

Generating Schema SQL and Creating Database Schema

Before actually we create a Database Script from the model. We will need to create an empty database where we need to create our schema. Create a database called ProjectResource in the SQL Server 2008 (which I am using is EXPRESS edition)

G.  Right click on the designer and choose ‘Generate Database form Model’.

H.  Create a connection to the project that we created, and click next.
 
I.  In the next step the wizard created the DDL for the schema. Click on the finish button which will create a ProjectTeam.edmx.sql file to the project.

J.  Open the ProjectTeam.edmx.sql file examine. The file will have the following sections

a.  Create all tables
b.  Creating all primary key constraints
c.  Creating all FOREIGN KEY constraints

-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 07/16/2010 16:25:10
-- Generated from EDMX file: D:\3. Research\EF4\ModelFirstDesign\ProjectTeam\ProjectTeam\ProjectTeam.edmx
-- --------------------------------------------------
SET quoted_identifier OFF;

GO

USE [ProjectResource];

GO

IF Schema_id(N'dbo') IS NULL
  EXECUTE(N'CREATE SCHEMA [dbo]');

GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------
-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------
-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------
-- Creating table 'ServiceLines'
CREATE TABLE [dbo].[ServiceLines]
  (
     [ServiceLineId]   INT IDENTITY(1, 1) NOT NULL,
     [ServiceLineName] NVARCHAR(MAX) NOT NULL
  );

GO

-- Creating table 'ResourceDetails'
CREATE TABLE [dbo].[ResourceDetails]
  (
     [ResourceId]                INT IDENTITY(1, 1) NOT NULL,
     [FirestName]                NVARCHAR(MAX) NOT NULL,
     [LasteName]                 NVARCHAR(MAX) NOT NULL,
     [Experience]                INT NOT NULL,
     [MiddleName]                NVARCHAR(MAX) NOT NULL,
     [ServiceLine_ServiceLineId] INT NOT NULL,
     [ProjectTeam_TeamId]        INT NOT NULL,
     [Band_BandId]               INT NOT NULL
  );

GO

-- Creating table 'ProjectTeams'
CREATE TABLE [dbo].[ProjectTeams]
  (
     [TeamId]   INT IDENTITY(1, 1) NOT NULL,
     [TeamName] NVARCHAR(MAX) NOT NULL
  );

GO

-- Creating table 'Bands'
CREATE TABLE [dbo].[Bands]
  (
     [BandId]   INT IDENTITY(1, 1) NOT NULL,
     [BandName] NVARCHAR(MAX) NOT NULL
  );

GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------
-- Creating primary key on [ServiceLineId] in table 'ServiceLines'
ALTER TABLE [dbo].[ServiceLines] ADD CONSTRAINT [PK_ServiceLines] PRIMARY KEY
CLUSTERED ([ServiceLineId] ASC);

GO

-- Creating primary key on [ResourceId] in table 'ResourceDetails'
ALTER TABLE [dbo].[ResourceDetails] ADD CONSTRAINT [PK_ResourceDetails] PRIMARY
KEY CLUSTERED ([ResourceId] ASC);

GO

-- Creating primary key on [TeamId] in table 'ProjectTeams'
ALTER TABLE [dbo].[ProjectTeams] ADD CONSTRAINT [PK_ProjectTeams] PRIMARY KEY
CLUSTERED ([TeamId] ASC);

GO

-- Creating primary key on [BandId] in table 'Bands'
ALTER TABLE [dbo].[Bands] ADD CONSTRAINT [PK_Bands] PRIMARY KEY CLUSTERED (
[BandId] ASC);

GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------
-- Creating foreign key on [ServiceLine_ServiceLineId] in table 'ResourceDetails'
ALTER TABLE [dbo].[ResourceDetails] ADD CONSTRAINT
[FK_ServiceLineResourceDetail] FOREIGN KEY ([ServiceLine_ServiceLineId])
REFERENCES [dbo].[ServiceLines] ([ServiceLineId]) ON DELETE NO ACTION ON UPDATE
NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ServiceLineResourceDetail'
CREATE INDEX [IX_FK_ServiceLineResourceDetail]
  ON [dbo].[ResourceDetails] ([ServiceLine_ServiceLineId]);

GO

-- Creating foreign key on [ProjectTeam_TeamId] in table 'ResourceDetails'
ALTER TABLE [dbo].[ResourceDetails] ADD CONSTRAINT
[FK_ProjectTeamResourceDetail] FOREIGN KEY ([ProjectTeam_TeamId]) REFERENCES
[dbo].[ProjectTeams] ([TeamId]) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectTeamResourceDetail'
CREATE INDEX [IX_FK_ProjectTeamResourceDetail]
  ON [dbo].[ResourceDetails] ([ProjectTeam_TeamId]);

GO

-- Creating foreign key on [Band_BandId] in table 'ResourceDetails'
ALTER TABLE [dbo].[ResourceDetails] ADD CONSTRAINT [FK_BandResourceDetail]
FOREIGN KEY ([Band_BandId]) REFERENCES [dbo].[Bands] ([BandId]) ON DELETE NO
ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BandResourceDetail'
CREATE INDEX [IX_FK_BandResourceDetail]
  ON [dbo].[ResourceDetails] ([Band_BandId]);

GO 

 K.  Open the file and right click on the file and click on the Connection à Connect, which will open the SQL Server “Connect to Database Engine “window and select the server instance and click on connect.
 
L.  Here we need to perform two steps. First verify the syntax and execute SQL

a.  Right click on the opened file and select Validate SQL Syntax. Make sure that syntax validation successful.
b.  Now click on the Execute SQL.
c.  Go back to your SQL Server database and verify the all tables are created.
d.  One more thing that we have to confirm is that the relationships. Create a new database diagram and add all the tables to verify the same.

M.  By default EDM provides only Create Operation. Select Band entity, right click and select ‘Table Mapping’notice that what is default.

Performing CRUD Operations on Bands Entity

A.  Create CreateBands, SelectBands, UpdateBands and DeleteBands Procedures in the Prject Resources DataBase.

a.  CreateBands

CREATE PROCEDURE CreateBands@BandName nvarchar(max)ASBEGINBEGIN TRYBEGIN TRANSACTION;INSERT INTO ProjectResource.dbo.Bands([BandName])VALUES(@BandName)COMMIT TRANSACTION;END TRYBEGIN CATCHIF @@TRANCOUNT > 0BEGINROLLBACK TRANSACTION;ENDEND CATCH;ENDGO

 b.  SelectBands

 

CREATE PROCEDURE [dbo].[SelectBand]ASBEGINSELECT Bands.BandId, Bands.BandNameFROM Bands
ENDGo

 c.  UpdateBands

CREATE PROCEDURE [dbo].[UpdateBands]@BandId int,@BandName nvarchar(max)ASBEGINSET NOCOUNT ON;BEGIN TRYBEGIN TRANSACTION;UPDATE ProjectResource.dbo.Bands SET[BandName] = @BandNameWHERE [BandId] = @BandIdCOMMIT TRANSACTION;END TRYBEGIN CATCHIF @@TRANCOUNT > 0BEGINROLLBACK TRANSACTION;ENDEND CATCH;ENDGO

 d.  DeleteBands

CREATE PROCEDURE DeleteBands@BandID intASBEGINBEGIN TRYBEGIN TRANSACTION;DELETE FROM ProjectResource.dbo.BandsWHERE [BandId] = @BandIdCOMMIT TRANSACTION;END TRYBEGIN CATCHIF @@TRANCOUNT > 0BEGINROLLBACK TRANSACTION;ENDEND CATCH;ENDGO

B.  Go back to EDM and right click on the EDM and select Update Model from Database.

C.  Select the newly added stored procedures and click on finish. Go to Model bowers and expand the stored procedure node. You can see the procedures added there. Do you know? Adding SP to the EDM will not change anything to our Entities until we map them to the EDM functions!!
 
D.  Select the Band Entity in the EDM; select the Stored Procedure Mapping by right clicking on the Bands entity. Expand the <Select Insert Function> you can notice that the SP’s are available for mapping.
 
E.  Now map the respective procedures to the Sp’s. Make sure that DeleteBands BandId mapped to BandId under Property column.
 
F.  Notice that we don’t have provision for mapping SelectBands procedure. For the same we need to create Import function. So that the SelectBands function will be available through a method for querying. To do the same, right click on the SelectBands procedure and click on Add Import Function.

G.  In the Add Import Function window set the values as follows and click on Ok. Notice that SelectBands function added under Import Function.
 
H.  Compile the Solution

I.  Now we will query the EDM for Bands. I have inserted some sample data from the backend.
 
J.  Add the following code to query the EDM. You can download the source code.
Since we have created our EDM with name ProjectTeam, EDM created a class ProjectTeamContainer that derives for ObjectContext. So our context becomes ProjectTeamContainer. The Import function that we have add to our solution, SelectBands, is called from the context.

private void LoadBands()
        {
            var context = new ProjectTeamContainer();
            var bands = context.SelectBands();
            gvBandDetails.DataSource = bands;
            gvBandDetails.DataBind();
        }

 K.  Add the following code to insert new Band.

var context = new ProjectTeamContainer();
       context.AddToBands(new Band()
              {
                     BandName = “Some Band”
              }
              );
 
              context.SaveChanges();

 L.  Add the following code to delete the Band.

var context = new ProjectTeamContainer();
       var band = (from mybands in context.SelectBands()
              where mybands.BandId == selectedBandId
              select mybands).First();
              context.Bands.DeleteObject(band);
              context.SaveChanges();

 M.  Add the following code to update the bands.

var context = new ProjectTeamContainer();
       var band = (from mybands in context.SelectBands()
              where mybands.BandId == selectedBandId
              select mybands).First();
              band.BandName = txtBandName.Text;
context.SaveChanges();

That's all, Now you can try with the other entities.

Sample Project Source

Download source files -249 kb

 
Sign Up to vote for this article
 
About Author
 
Chinna Srihari
Occupation-Software Engineer
Company-
Member Type-Junior
Location-India
Joined date-17 Jun 2010
Home Page-
Blog Page-
Having 12 + years of IT experience, 2.5 years as Technical Management experience and 1.5 year of onsite coordinator experience and rest 8+ years of technical activities including Project Leader, Tech Lead, Designer, PQL, and Developer experience in Quality Management System (CMM-level 5, SEI-CMM-level 5).
 
 
Other popularSectionarticles
Comments
By:getsriramsDate Of Posted:10/23/2010 9:45:58 PM
Entity Framework support for SQL CE 4.0
Hi Guys, Below is a link to Tutorial for using Entity Framework with SQL Ce 4.0 as there is no design time support for SQL CE in VS2010 http://getsrirams.blogspot.com/2010/10/adonet-entity-data-model-for-sqlserver.html Identity column issue is fixed for SQL CE 4.0 in EF 4.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top
</