Model-First Design using ADO.NET Entity Framework 4.0

Introduction

 
In this article, we are going to see the second most impartment part of the ADO.NET entity framework 4.0. The first part is the traditional way of developing applications like, create Database schema and then the creation of entity class follows. In this approach, any changes to the schema after developing the conceptual entity classes need to modify, 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 a time-consuming process to synchronize the UX code and the entity class code.
 
In the 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 it easier to limit the huge changes. We will see what limitations we have in this approach, everything comes with a limitation.
 
We will be learning the following
  1. Create an 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

 
1. Create a solution called ModelFirstDesign.sln. I am using ASP.NET Web application template for this sample, VSTS 2010 Ultimate trail version.
 
EntityFramework1.gif
 
2. Add a new class library project to the solution and name it ProjectTeam. As of now, our primary focus is on creating entity class and the database for the same.
 
3. Add an ADO.NET Entity model item and name it as ProjectTeam.edm
 
EntityFramework2.gif
 
4. In the wizard select the Empty model and click on finish.
 
EntityFramework3.gif
 

EDM Designer

 
5. We will examine the empty mode and its toolbox that helps us in creating the EDM entities, entity associations, and Inheritance.
  • Entity: Allows you to design and create an Entity.
  • Association: Lets you create an association (or relationship) between two entities.
  • Inheritance: Lets you create an Inheritance relationship between two entities.
     
    EntityFramework4.gif
     

Creating Entities

 
6. Now we will create the entities in the model.
  1. The following are the entities we will be creating in the EDM.
  2.  
    • ServiceLine: This contains the service line details that a resource can belong to.
    • ProjectTeam: This entity will contain individual team types. Like Dev, Test, DB, and Support teams
    • ResourceDetails: This entity will contain the individual member details and which ServiceLine he/she belongs to and ProjectTeam
    • Brand: This entity tracks the resource brand.
     
  3. Creating individual entities. Drag an entity instance from the toolbox. 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.
  4.  
    EntityFramework5.gif
     
  5. By selecting the Entity1 properties change the name of the Entity to ServiceLine
     
  6. EntityFramework6.gif
     
  7. 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
  8.  
    EntityFramework7.gif
     
  9. By right-clicking on the Entity add the following scalar properties. Also, repeat the same steps for all the Entities in the table given below. Make sure you are not creating the for now.
     
    EntityFramework8.gif
     
    ServiceLine Entity
  10. Column Name Data Type Description
    ServiceLineID Int32 Unique identifier
    SeviceLineName String The Service line name
     
    Band Entity
    Column Name Data Type Description
    BrandID Int32 Unique identifier
    BrandName String The brand name of the member
     
    ProjectTeam Entity
    Column Name Data Type Description
    TeamID Int32 Unique identifier
    TeamName String Name project team
     
    ResourceDetail Entity
    Column Name Data Type Description
    ResourceID Int32 Unique identifier
    FirstName String Resource First Name
    LastName String Resource Last Name
    MiddleName String Resource Middle Name
    Experience Int32 The resource experience
     
    So for your model should look like bellow
     
    EntityFramework9.gif
     

    Creating Associations and Relationships between Entities

     
  11. To create a relationship with between the table. Click on the Association in the toolbox. Drag the ServiceLineID from ServiceLine to ResourceDetail entity and repeat the same for all of them.
     
    EntityFramework10.gif
     

    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)
  12.  
  13. Right-click on the designer and choose 'Generate Database from Model'.
  14.  
    EntityFramework11.gif
     
  15. Create a connection to the project that we created, and click next.
  16.  
    EntFram12.gif
     
  17. 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.
  18.  
    EntFram13.gif
     
  19. Open the ProjectTeam.edmx.sql file, examine the file will have the following sections
     
    • Create all tables
    • Creating all primary key constraints
    • Creating all FOREIGN KEY constraints
    1. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    2. --Entity Designer DDL Script  
    3. for SQL Server 2005, 2008, and Azure  
    4. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    5. --Date Created: 07 / 16 / 2010 16: 25: 10  
    6.       --Generated from EDMX file: D: \3. Research\ EF4\ ModelFirstDesign\ ProjectTeam\ ProjectTeam\ ProjectTeam.edmx  
    7.       -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    8.   
    9. SET QUOTED_IDENTIFIER OFF;  
    10. GO  
    11. USE[ProjectResource];  
    12. GO  
    13. IF SCHEMA_ID(N 'dbo'IS NULL EXECUTE(N 'CREATE SCHEMA [dbo]');  
    14. GO  
    15.   
    16. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    17. --Dropping existing FOREIGN KEY constraints  
    18. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    19.   
    20. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    21. --Dropping existing tables  
    22. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    23.   
    24. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    25. --Creating all tables  
    26. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    27.   
    28. --Creating table 'ServiceLines'  
    29. CREATE TABLE[dbo]. [ServiceLines](  
    30.       [ServiceLineId] int IDENTITY(1, 1) NOT NULL,  
    31.       [ServiceLineName] nvarchar(maxNOT NULL  
    32. );  
    33. GO  
    34.   
    35. --Creating table 'ResourceDetails'  
    36. CREATE TABLE[dbo]. [ResourceDetails](  
    37.       [ResourceId] int IDENTITY(1, 1) NOT NULL,  
    38.       [FirestName] nvarchar(maxNOT NULL,  
    39.       [LasteName] nvarchar(maxNOT NULL,  
    40.       [Experience] int NOT NULL,  
    41.       [MiddleName] nvarchar(maxNOT NULL,  
    42.       [ServiceLine_ServiceLineId] int NOT NULL,  
    43.       [ProjectTeam_TeamId] int NOT NULL,  
    44.       [Band_BandId] int NOT NULL  
    45. );  
    46. GO  
    47.   
    48. --Creating table 'ProjectTeams'  
    49. CREATE TABLE[dbo]. [ProjectTeams](  
    50.       [TeamId] int IDENTITY(1, 1) NOT NULL,  
    51.       [TeamName] nvarchar(maxNOT NULL  
    52. );  
    53. GO  
    54.   
    55. --Creating table 'Bands'  
    56. CREATE TABLE[dbo]. [Bands](  
    57.       [BandId] int IDENTITY(1, 1) NOT NULL,  
    58.       [BandName] nvarchar(maxNOT NULL  
    59. );  
    60. GO  
    61.   
    62. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    63. --Creating all PRIMARY KEY constraints  
    64. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    65.   
    66. --Creating primary key on[ServiceLineId] in table 'ServiceLines'  
    67. ALTER TABLE[dbo]. [ServiceLines]  
    68. ADD CONSTRAINT[PK_ServiceLines]  
    69. PRIMARY KEY CLUSTERED([ServiceLineId] ASC);  
    70. GO  
    71.   
    72. --Creating primary key on[ResourceId] in table 'ResourceDetails'  
    73. ALTER TABLE[dbo]. [ResourceDetails]  
    74. ADD CONSTRAINT[PK_ResourceDetails]  
    75. PRIMARY KEY CLUSTERED([ResourceId] ASC);  
    76. GO  
    77.   
    78. --Creating primary key on[TeamId] in table 'ProjectTeams'  
    79. ALTER TABLE[dbo]. [ProjectTeams]  
    80. ADD CONSTRAINT[PK_ProjectTeams]  
    81. PRIMARY KEY CLUSTERED([TeamId] ASC);  
    82. GO  
    83.   
    84. --Creating primary key on[BandId] in table 'Bands'  
    85. ALTER TABLE[dbo]. [Bands]  
    86. ADD CONSTRAINT[PK_Bands]  
    87. PRIMARY KEY CLUSTERED([BandId] ASC);  
    88. GO  
    89.   
    90. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    91. --Creating all FOREIGN KEY constraints  
    92. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    93.   
    94. --Creating foreign key on[ServiceLine_ServiceLineId] in table 'ResourceDetails'  
    95. ALTER TABLE[dbo]. [ResourceDetails]  
    96. ADD CONSTRAINT[FK_ServiceLineResourceDetail]  
    97. FOREIGN KEY([ServiceLine_ServiceLineId])  
    98. REFERENCES[dbo]. [ServiceLines]  
    99.       ([ServiceLineId])  
    100. ON DELETE NO ACTION ON UPDATE NO ACTION;  
    101.   
    102. --Creating non - clustered index  
    103. for FOREIGN KEY 'FK_ServiceLineResourceDetail'  
    104. CREATE INDEX[IX_FK_ServiceLineResourceDetail]  
    105. ON[dbo]. [ResourceDetails]  
    106.       ([ServiceLine_ServiceLineId]);  
    107. GO  
    108.   
    109. --Creating foreign key on[ProjectTeam_TeamId] in table 'ResourceDetails'  
    110. ALTER TABLE[dbo]. [ResourceDetails]  
    111. ADD CONSTRAINT[FK_ProjectTeamResourceDetail]  
    112. FOREIGN KEY([ProjectTeam_TeamId])  
    113. REFERENCES[dbo]. [ProjectTeams]  
    114.       ([TeamId])  
    115. ON DELETE NO ACTION ON UPDATE NO ACTION;  
    116.   
    117. --Creating non - clustered index  
    118. for FOREIGN KEY 'FK_ProjectTeamResourceDetail'  
    119. CREATE INDEX[IX_FK_ProjectTeamResourceDetail]  
    120. ON[dbo]. [ResourceDetails]  
    121.       ([ProjectTeam_TeamId]);  
    122. GO  
    123.   
    124. --Creating foreign key on[Band_BandId] in table 'ResourceDetails'  
    125. ALTER TABLE[dbo]. [ResourceDetails]  
    126. ADD CONSTRAINT[FK_BandResourceDetail]  
    127. FOREIGN KEY([Band_BandId])  
    128. REFERENCES[dbo]. [Bands]  
    129.       ([BandId])  
    130. ON DELETE NO ACTION ON UPDATE NO ACTION;  
    131.   
    132. --Creating non - clustered index  
    133. for FOREIGN KEY 'FK_BandResourceDetail'  
    134. CREATE INDEX[IX_FK_BandResourceDetail]  
    135. ON[dbo]. [ResourceDetails]  
    136.       ([Band_BandId]);  
    137. GO 
  20. 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.
  21.  
    EntFram14.gif
     
  22. Here we need to perform two steps. First, verify the syntax and execute SQL  
    • Right-click on the opened file and select Validate SQL Syntax. Make sure that syntax validation successful.
    •  
      EntFram15.gif
       
    • Now click on the Execute SQL.
       
      EntFram16.gif
       
    • Go back to your SQL Server database and verify the all tables are created.
       
      EntFram17.gif
       
    • One more thing that we have to confirm is that relationships. Create a new database diagram and add all the tables to verify the same.
       
      EntFram18.gif
       
  23. By default, EDM provides only Create Operation. Select Band entity, right-click, and select 'Table Mapping' notice that what is the default.
     
    EntFram19.gif
     

Performing CRUD Operations on Bands Entity

  1. Create CreateBands, SelectBands, UpdateBands, and DeleteBands Procedures in the Project Resources DataBase.
     
    • CreateBands
      1.  CREATE PROCEDURE CreateBands  
      2.        @BandName nvarchar(max)  
      3. AS  
      4. BEGIN  
      5.         
      6.        BEGIN TRY  
      7.               BEGIN TRANSACTION;  
      8.    
      9.                      INSERT INTO ProjectResource.dbo.Bands  
      10.                      ( (  
      11.                      [BandName]  
      12.                      ) )  
      13.                      VALUES  
      14. " style="line-height: normal; margin: 0in 0in 0.0001pt 1in;">                      (  
      15.                            @BandName  
      16.                      ) ) ) ) )  
      17.               COMMIT TRANSACTION;  
      18.        END TRY  
      19.        BEGIN CATCH  
      20.               IF @@TRANCOUNT > 0  
      21.                      BEGIN  
      22.                      ROLLBACK TRANSACTION;  
      23.               END  
      24.        END CATCH;  
      25. END  
      26. GO 
    • SelectBands
      1.  CREATE PROCEDURE [dbo].[SelectBand]  
      2. AS  
      3. BEGIN  
      4.        SELECT Bands.BandId, Bands.BandName  
      5.        FROM Bands  
      6. END  
      7.    
      8. Go 
    • UpdateBands
      1. CREATE PROCEDURE [dbo].[UpdateBands]  
      2. @BandId int,  
      3. @BandName nvarchar(max)  
      4. AS  
      5. BEGIN  
      6. SET NOCOUNT ON;  
      7.    
      8.        BEGIN TRY  
      9.               BEGIN TRANSACTION;  
      10.    
      11.                      UPDATE ProjectResource.dbo.Bands SET  
      12.                            [BandName] = @BandName  
      13.                      WHERE [BandId] = @BandId  
      14.               COMMIT TRANSACTION;  
      15.        END TRY  
      16.        BEGIN CATCH  
      17.               IF @@TRANCOUNT > 0  
      18.                      BEGIN  
      19.                      ROLLBACK TRANSACTION;  
      20.               END  
      21.        END CATCH;  
      22. END  
      23.    
      24.        GO 
    • DeleteBands
      1. CREATE PROCEDURE DeleteBands  
      2.        @BandID int  
      3. AS  
      4. BEGIN  
      5. BEGIN TRY  
      6.               BEGIN TRANSACTION;  
      7.                      DELETE FROM ProjectResource.dbo.Bands  
      8.                      WHERE [BandId] = @BandId  
      9.               COMMIT TRANSACTION;  
      10.        END TRY  
      11.        BEGIN CATCH  
      12.               IF @@TRANCOUNT > 0  
      13.                      BEGIN  
      14.                      ROLLBACK TRANSACTION;  
      15.               END  
      16.        END CATCH;  
      17. END  
      18. GO 
  2. Go back to EDM and right-click on the EDM and select Update Model from Database.
  3.  
    EntFram20.gif
     
  4. 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!!
  5.  
    EntFram21.gif
     
     
  6. 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.
  7.  
    EntFram22.gif
     
  8. Now map the respective procedures to the Sp's. Make sure that DeleteBands BandId mapped to BandId under the Property column.
  9.  
    EntFram23.gif
     
  10. Notice that we don't have a 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.
  11.  
    EntFram24.gif
     
  12. In the Add, Import Function window set the values as follows and click on Ok. Notice that SelectBands function added under Import Function.
  13.  
    EntFram25.gif
     
  14. Compile the Solution.
  15.  
  16. Now we will query the EDM for Bands. I have inserted some sample data from the backend.
  17.  
  18. 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.
    1. private void LoadBands() {  
    2.     var context = new ProjectTeamContainer();  
    3.     var bands = context.SelectBands();  
    4.     gvBandDetails.DataSource = bands;  
    5.     gvBandDetails.DataBind();  
  19. Add the following code to insert new Band.
    1. var context = new ProjectTeamContainer();  
    2. context.AddToBands(  
    3.     new Band() {  
    4.         BandName = "Some Band"  
    5.     }  
    6. );  
    7. context.SaveChanges(); 
  20. Add the following code to delete the Band.
    1. var context = new ProjectTeamContainer();  
    2. var band = (from mybands in context.SelectBands() where mybands.BandId == selectedBandId select mybands).First();  
    3. context.Bands.DeleteObject(band);  
    4. context.SaveChanges(); 
  21. Add the following code to update the bands.
    1. var context = new ProjectTeamContainer();  
    2. var band = (from mybands in context.SelectBands() where mybands.BandId == selectedBandId select mybands).First();  
    3. band.BandName = txtBandName.Text;  
    4. context.SaveChanges(); 
Now you can try with the other entities.
 
Thanks,
 
Chinna Srihari