ADO.NET Entity Framework and .NET 4 – How to use Visual Studio 2010 Modeling Tools to Build a Database

I attended the PDC session by Shyam Pather and Chris Anderson at Microsft because I want to see learn about the way Microsoft looks at data. We’ve seen the evolution over the years. There is a wide array (some say dizzying array) of options for the developer to consume and publish data in its various forms.

The inventor of the Entity Relationship Model is Peter Chen. He makes the following points:

• We want to model the real world -  a relational data model is not as real world as we need
  • Too many headaches worrying about relationships
• We want to be as “truthful” as possible so if we see a table, we should make an entity called “Employee” or “Building”
  • I will say this a lot. Good Entities are invariant over time. They exist independent of relationships.
• Whatever we see, “table, chairs, people, cups”  - that should be what the developer should have in their code
• You need to know “relationships” in entities, like a group of people. They are gathered at a place and time. They have “relationships”
• There may be a spatial relationship, proximity or other type of “relationships”
• Relational model was a way to formalize the structure of data (customers have orders)
  • Set theory
  • Query and update is an abstraction over set operations
  • A set of principles (3rd Normal Form)
  • Meaningful structure for queries and updates, but not for a developer who wants to see a single employee object, not a collection of related tables.
  • Issues – degree of concurrency, workloads
  • Isolate applications from details of objects and data model layout.
• Applications are hard to write because you have to know all the relationships in a relational model
• Applications like concepts and abstractions that closely monitor the real world
• You don’t want apps to write complex queries or to have to understand the myriad of relationships

Making Life Easier For the Developer

• A good example is an employee.
• The information for an employee might be spread among many different tables.  We have many different tables because most design sessions start with lists of data, which later become segregated into tables.
• But as a developer we only want to worry about a single object, not all the related tables and associations.
• Having to understand all the relationships in a database lowers productivity, obscures the structure and meaning of your code, and generally increases the time it takes to build typical business applications.
• Additional challenges include performance in terms of concurrency and response time.
• We need something natural, more intuitive.
• Semantics gets lost because some tables exist just for relationships, like in a many to many scenario
• Developers like real objects
  • An “Employee” object is a lot easier than the 5 tables that make up an employee
• Relational Data
  • No structured way to say “how many tables in an Employee object”

Key Facts about Entity Modeling

• Some data is designated as invariant to time
  • It therefore exists on its own
  • Objects have independent existence
• Relationships, on the other hand
  • Are connections over time
  • But still are first class concepts (Relationships)
    • They are time dependent, no independent existence
• Entity Framework is basically a client side view manager
  • Entities and relationships are views
  • Build a view abstraction
  • Allow extensions to SQL Language to greatly simplify joins
  • Have intellisense work with query construction

Many times, data has an independent existence. In contrast, relationships are time dependent. For example, you may no longer be with a specific insurance company.

Usually you create a relational schema to see your chunks of data. But when it comes time to code you notice an employee is spread among a collection of tables. Relationships are important because they exist for data integrity, much like an order having many line items. Nice and easy from a query point of view. The ideal scenario is to have just an order object that automatically has all the line items in it already, or at least available by calling a method (you may want lazy loading, “loading on demand, not all the time).

Oftentimes, partitioning of data complicates matters

Tables are often horizontally partitioned to support better performance and concurrency. This complicates the developers life because data is now even more fragmented.

Evolving ADO.NET Entity Framework in .NET 4 and Beyond

https://ecn.channel9.msdn.com/o9/pdc09/wmvhigh/FT10.wmv

It is somewhat overwhelming to think about all the Microsoft technology surrounding data.

There are some core pillars from which other frameworks are based. Those low-level core pillars include:

1. SQL Server Modeling Services
2. Cloud Storage
3. On Premise SQL Server

Cloud Storage

Cloud Storage is the latest in the Microsoft offerings with options for relational as wells Windows Azure Storage for access to secure anytime, anywhere data that is durable and scalable.

On Premise SQL Server

• Run your most demanding mission-critical applications on premise.
• Reduce time and cost of development and management through great tooling
  of applications.
• Deliver actionable insight to your entire organization through business intelligence software and other offerings.

SQL Server Modeling Services

Note: Used to be called “Oslo” repository and the built-in domains.

I found this one to be the newest terminology. So I started to explore it. I essentially uncovered a ton of technologies being put under one umbrella:

• SQL Server Modeling Services provides tables and procedures that support features such as claims-based security, change tracking, versioning, and localization.
• Also includes clustering, database mirroring, resource governor, backup and restore, replication, reporting services, change data capture.
• These services include the Repository, a SQL Server database enhanced with capabilities for the storage of model schemas and instances.
• There are built-in domains such as the Common Language Runtime (CLR), Unified Modeling Language (UML), and System.Identity
• Key Point UML is playing a super big role here for application structure, behavior, and architecture, but also business process and data structure.
  • UML is seen as a universal language for business analysts, architects, developers, testers and IT staff.
  • When everything works well, you can enjoy:
    • Maintaining catalogs of commonly used models,
    • Providing impact analysis across models, and potentially traceability between software deliverables and the models that describe them

It is about being able to build customizable frameworks for database applications

These are the base layers that we want to “abstract away” for our applications.

Figure Above: The lower level data stores for the Entity Framework 

   Why Entity Framework?

• The ADO.NET Entity Framework is designed to enable developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema.
• The goal is to decrease the amount of code and maintenance required for data-oriented applications.

We will start with Visual Studio 2010 and begin modeling our blogging. We’ll create 3 entities and 2 relationships.

Let’s not waste anytime and write some code. I’m using Visual Studio 2010 here.

I will start with a WPF Application and add a database to it.

  Choose “WPF Application” and provide:

• Name
• Location
• Solution Name 

Add a New Item (ADO.NET Entity Data Model)

Add “ADO.NET Entity Data Model.”

We will choose Empty model because we wish to build the database from the model, not the other way around.

An empty data model. This is the design surface where we can build our conceptual model. The design surface is ready for us to add entities.

On the design surface you can right mouse click to add a new entity. Note that the design surface is BloggingModel.edmx

You will need some simple skills. You will need to know how:

To add a "New Entity"

To add "scalar properties” right mouse click on the “Blog” entity:

You will add 3 entities. So you the 2 right mouse clicks above (Add Entity, Add Association)

• Blog
  • Id (int32)
  • Name (string)
  • Owner (string)
• Post
  • Id (int32)
  • CreatedDate (DateTime)
  • ModifiedDate (DateTime)
  • PostContent (string)
  • Title (string)
• Tag
  • Id (int32)
  • Name (string)

Built in pluralization that is pretty smart. If you say “Person,” it will pluralize to “People.”

Here is where we are so far:

https://brunoblogfiles.com/zips/DemoFeatures.zip

It’s about relationships. From Blog to Post we need a 1-to-many relationship. A right-mouse click gets you “Add Association.”

How to setup the one-to-many relationship.

Blog has a one-to-many to Post.

Now it is time to setup a many-to-many.

Make sure you select:

• Post to Tag relationship
• Many to Many

Make sure to select many-to-many.  It is from Post to Tag.

The promise of model driven development is that the database gets created based on the model. A simple right-mouse click gets you to “Generate Database from Model.”

Notice the model called BloggingModel.edmx.sql.

Here is the sql generated by Visual Studio 2010 Beta2.

BlogginModel.esmx.sql

Code Snippet

1.  
2. -- --------------------------------------------------
3. -- Date Created: 01/25/2010 13:40:29
4. -- Generated from EDMX file: c:\devprojects\entity_framework\DemoFeatures\DemoFeatures\BloggingModel.edmx
5. -- --------------------------------------------------
6.  
7. SET QUOTED_IDENTIFIER OFF;
8. SET ANSI_NULLS ON;
9. GO
10.  
11. USE [BlogDB]
12. GO
13. IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]')
14. GO
15.  
16. -- --------------------------------------------------
17. -- Dropping existing FK constraints
18. -- --------------------------------------------------
19.  
20.  
21. -- --------------------------------------------------
22. -- Dropping existing tables
23. -- --------------------------------------------------
24.  
25.  
26. -- --------------------------------------------------
27. -- Creating all tables
28. -- --------------------------------------------------
29.  
30. -- Creating table 'Posts'
31. CREATE TABLE [dbo].[Posts] (
32.     [Id] int  NOT NULL,
33.     [CreatedDate] datetime  NOT NULL,
34.     [ModifiedDate] datetime  NOT NULL,
35.     [PostContent] nvarchar(max)  NOT NULL,
36.     [Title] nvarchar(max)  NOT NULL,
37.     [BlogId] int  NOT NULL
38. );
39. GO
40. -- Creating table 'Blogs'
41. CREATE TABLE [dbo].[Blogs] (
42.     [Id] int  NOT NULL,
43.     [Name] nvarchar(max)  NOT NULL,
44.     [Owner] nvarchar(max)  NOT NULL
45. );
46. GO
47. -- Creating table 'Tags'
48. CREATE TABLE [dbo].[Tags] (
49.     [Id] int  NOT NULL,
50.     [Name] nvarchar(max)  NOT NULL
51. );
52. GO
53. -- Creating table 'PostTag'
54. CREATE TABLE [dbo].[PostTag] (
55.     [Posts_Id] int  NOT NULL,
56.     [Tags_Id] int  NOT NULL
57. );
58. GO
59.  
60. -- --------------------------------------------------
61. -- Creating all Primary Key Constraints
62. -- --------------------------------------------------
63.  
64. -- Creating primary key on [Id] in table 'Posts'
65. ALTER TABLE [dbo].[Posts] WITH NOCHECK
66. ADD CONSTRAINT [PK_Posts]
67.     PRIMARY KEY CLUSTERED ([Id] ASC)
68.     ON [PRIMARY]
69. GO
70. -- Creating primary key on [Id] in table 'Blogs'
71. ALTER TABLE [dbo].[Blogs] WITH NOCHECK
72. ADD CONSTRAINT [PK_Blogs]
73.     PRIMARY KEY CLUSTERED ([Id] ASC)
74.     ON [PRIMARY]
75. GO
76. -- Creating primary key on [Id] in table 'Tags'
77. ALTER TABLE [dbo].[Tags] WITH NOCHECK
78. ADD CONSTRAINT [PK_Tags]
79.     PRIMARY KEY CLUSTERED ([Id] ASC)
80.     ON [PRIMARY]
81. GO
82. -- Creating primary key on [Posts_Id], [Tags_Id] in table 'PostTag'
83. ALTER TABLE [dbo].[PostTag] WITH NOCHECK
84. ADD CONSTRAINT [PK_PostTag]
85.     PRIMARY KEY NONCLUSTERED ([Posts_Id], [Tags_Id] ASC)
86.     ON [PRIMARY]
87. GO
88.  
89. -- --------------------------------------------------
90. -- Creating all Foreign Key Constraints
91. -- --------------------------------------------------
92.  
93. -- Creating foreign key on [BlogId] in table 'Posts'
94. ALTER TABLE [dbo].[Posts] WITH NOCHECK
95. ADD CONSTRAINT [FK_BlogPost]
96.     FOREIGN KEY ([BlogId])
97.     REFERENCES [dbo].[Blogs]
98.         ([Id])
99.     ON DELETE NO ACTION ON UPDATE NO ACTION
100. GO
101. -- Creating foreign key on [Posts_Id] in table 'PostTag'
102. ALTER TABLE [dbo].[PostTag] WITH NOCHECK
103. ADD CONSTRAINT [FK_PostTag_Post]
104.     FOREIGN KEY ([Posts_Id])
105.     REFERENCES [dbo].[Posts]
106.         ([Id])
107.     ON DELETE NO ACTION ON UPDATE NO ACTION
108. GO
109. -- Creating foreign key on [Tags_Id] in table 'PostTag'
110. ALTER TABLE [dbo].[PostTag] WITH NOCHECK
111. ADD CONSTRAINT [FK_PostTag_Tag]
112.     FOREIGN KEY ([Tags_Id])
113.     REFERENCES [dbo].[Tags]
114.         ([Id])
115.     ON DELETE NO ACTION ON UPDATE NO ACTION
116. GO
117.  
118. -- --------------------------------------------------
119. -- Script has ended
120. -- --------------------------------------------------

Before we can run the script above, we need a database so what we have place to put the tables, relationships, indexes and so on.

Use Visual Studio 2010’s Server Explorer to “Create a New SQL Server Database.”

We will name ours BlogDB

Choose the target database for which the data will place all the tables, relationships, indices, etc.

Here is the script that results from the model we previously defined.

I pasted the script above into Microsoft SQL Server Management Studio.

We still need to run the sql script. I started Microsoft SQL Server Management Studio and hit “New Query”. The query uses “BlogDB” and then creates the needed tables and relationships.

To verify everything correctly, let’s get SQL Server Management Studio to generate a Database Diagram for us. Let’s add all the tables.

We’ve come full circle. We started with a conceptual entities in the ADO.NET Entity Framework and ended up with a physical representation in SQL Server.