ADO.NET Entity Framework and .NET 4 & Visual Studio 2010 Modeling Tools – Writing Code And Working with Entities and Viewing Data

Here are some of the modest goals of this blog post. We will learn:

• How the ADO.NET Entity Framework simplifies our C# code
• How to populate a TreeView control with the data in a one to many relationship
• How to validate our entity relationships using a database diagram in SQL Server Management Studio

Let’s continue on from the previous 3 posts about the entity framework:

Previous Blog Entries

• https://blogs.msdn.com/brunoterkaly/archive/2010/01/30/ado-net-entity-framework-and-net-4-visual-studio-2010-modeling-tools-writing-code-and-working-with-entities-to-add-data.aspx
• https://blogs.msdn.com/brunoterkaly/archive/2010/01/25/ado-net-entity-framework-and-net-4-how-to-use-visual-studio-2010-modeling-tools-to-build-a-database.aspx
• https://blogs.msdn.com/brunoterkaly/archive/2010/02/01/ado-net-entity-framework-4-0-introduction-to-quadrant.aspx

Viewing data

Syntactically clean code to view data

I keep addressing the need to have intuitive code. You’ll see shortly that our code is going to look pretty sweet, thanks to the ADO.NET Entity Framework.

We’re going to write code that reads from our context object from the Entity Framework. Our code this time around will loop through parent and child entities. That is the main lesson here.

Getting our WPF Application ready

If you’ve been reading the previous posts, we’ve been building up a WPF application. Here is the source code in case you haven’t seen the other posts. It will be much easier if you just build up the database from the model as my posts describe. You’ll have a much better understanding of how the pieces fit together.

Source Code From Previous Blog Post

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

The Database Code

Here is the database code for BlogDB. If you followed the previous posts, you won’t need this script. Otherwise, open up SQL Server Management Studio 2008 and run this script.

 USE [master]
 GO
  
 /****** Object:  Database [BlogDB]    Script Date: 02/01/2010 15:20:23 ******/
 IF    EXISTS (SELECT name FROM sys.databases WHERE name = N'BlogDB')
 DROP DATABASE [BlogDB]
 GO
  
 USE [master]
 GO
  
 /****** Object:  Database [BlogDB]    Script Date: 02/01/2010 15:20:23 ******/
 CREATE DATABASE [BlogDB] ON  PRIMARY 
 ( NAME = N'BlogDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BlogDB.mdf' , SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
  LOG ON 
 ( NAME = N'BlogDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BlogDB_log.LDF' , SIZE = 768KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 GO
  
 ALTER DATABASE [BlogDB] SET COMPATIBILITY_LEVEL = 100
 GO
  
 IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
 begin
 EXEC [BlogDB].[dbo].[sp_fulltext_database] @action = 'enable'
 end
 GO
  
 ALTER DATABASE [BlogDB] SET ANSI_NULL_DEFAULT OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET ANSI_NULLS OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET ANSI_PADDING OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET ANSI_WARNINGS OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET ARITHABORT OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET AUTO_CLOSE OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET AUTO_CREATE_STATISTICS ON 
 GO
  
 ALTER DATABASE [BlogDB] SET AUTO_SHRINK OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET AUTO_UPDATE_STATISTICS ON 
 GO
  
 ALTER DATABASE [BlogDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET CURSOR_DEFAULT  GLOBAL 
 GO
  
 ALTER DATABASE [BlogDB] SET CONCAT_NULL_YIELDS_NULL OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET NUMERIC_ROUNDABORT OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET QUOTED_IDENTIFIER OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET RECURSIVE_TRIGGERS OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET  ENABLE_BROKER 
 GO
  
 ALTER DATABASE [BlogDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET TRUSTWORTHY OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET PARAMETERIZATION SIMPLE 
 GO
  
 ALTER DATABASE [BlogDB] SET READ_COMMITTED_SNAPSHOT OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET HONOR_BROKER_PRIORITY OFF 
 GO
  
 ALTER DATABASE [BlogDB] SET  READ_WRITE 
 GO
  
 ALTER DATABASE [BlogDB] SET RECOVERY FULL 
 GO
  
 ALTER DATABASE [BlogDB] SET  MULTI_USER 
 GO
  
 ALTER DATABASE [BlogDB] SET PAGE_VERIFY CHECKSUM  
 GO
  
 ALTER DATABASE [BlogDB] SET DB_CHAINING OFF 
 GO
  
  
 USE [BlogDB]
 GO
 /****** Object:  Table [dbo].[Tags]    Script Date: 02/01/2010 15:22:11 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tags]') AND type in (N'U'))
 BEGIN
 CREATE TABLE [dbo].[Tags](
     [Id] [int] NOT NULL,
     [Name] [nvarchar](max) NOT NULL,
  CONSTRAINT [PK_Tags] PRIMARY KEY CLUSTERED 
 (
     [Id] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 END
 GO
 /****** Object:  Table [dbo].[Blogs]    Script Date: 02/01/2010 15:22:11 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Blogs]') AND type in (N'U'))
 BEGIN
 CREATE TABLE [dbo].[Blogs](
     [Id] [int] NOT NULL,
     [Name] [nvarchar](max) NOT NULL,
     [Owner] [nvarchar](max) NOT NULL,
  CONSTRAINT [PK_Blogs] PRIMARY KEY CLUSTERED 
 (
     [Id] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 END
 GO
 INSERT [dbo].[Blogs] ([Id], [Name], [Owner]) VALUES (1, N'Blog about developing software', N'Bruno Terkaly')
 /****** Object:  Table [dbo].[Posts]    Script Date: 02/01/2010 15:22:11 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Posts]') AND type in (N'U'))
 BEGIN
 CREATE TABLE [dbo].[Posts](
     [Id] [int] NOT NULL,
     [CreatedDate] [datetime] NOT NULL,
     [ModifiedDate] [datetime] NOT NULL,
     [PostContent] [nvarchar](max) NOT NULL,
     [Title] [nvarchar](max) NOT NULL,
     [BlogId] [int] NOT NULL,
  CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED 
 (
     [Id] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 END
 GO
 INSERT [dbo].[Posts] ([Id], [CreatedDate], [ModifiedDate], [PostContent], [Title], [BlogId]) VALUES (1, CAST(0x00009D0E003D691A AS DateTime), CAST(0x00009D0E003D691B AS DateTime), N'Read this blog post by Bruno', N'Learning ADO.NET Entity Framework - Intro', 1)
 INSERT [dbo].[Posts] ([Id], [CreatedDate], [ModifiedDate], [PostContent], [Title], [BlogId]) VALUES (2, CAST(0x00009D0E003D694E AS DateTime), CAST(0x00009D0E003D694E AS DateTime), N'Read this blog post by Bruno', N'Learning ADO.NET Entity Framework - Advanced Topics', 1)
 /****** Object:  Table [dbo].[PostTag]    Script Date: 02/01/2010 15:22:11 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PostTag]') AND type in (N'U'))
 BEGIN
 CREATE TABLE [dbo].[PostTag](
     [Posts_Id] [int] NOT NULL,
     [Tags_Id] [int] NOT NULL,
  CONSTRAINT [PK_PostTag] PRIMARY KEY NONCLUSTERED 
 (
     [Posts_Id] ASC,
     [Tags_Id] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 END
 GO
 /****** Object:  ForeignKey [FK_PostTag_Post]    Script Date: 02/01/2010 15:22:11 ******/
 IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PostTag_Post]') AND parent_object_id = OBJECT_ID(N'[dbo].[PostTag]'))
 ALTER TABLE [dbo].[PostTag]  WITH NOCHECK ADD  CONSTRAINT [FK_PostTag_Post] FOREIGN KEY([Posts_Id])
 REFERENCES [dbo].[Posts] ([Id])
 GO
 IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PostTag_Post]') AND parent_object_id = OBJECT_ID(N'[dbo].[PostTag]'))
 ALTER TABLE [dbo].[PostTag] CHECK CONSTRAINT [FK_PostTag_Post]
 GO
 /****** Object:  ForeignKey [FK_PostTag_Tag]    Script Date: 02/01/2010 15:22:11 ******/
 IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PostTag_Tag]') AND parent_object_id = OBJECT_ID(N'[dbo].[PostTag]'))
 ALTER TABLE [dbo].[PostTag]  WITH NOCHECK ADD  CONSTRAINT [FK_PostTag_Tag] FOREIGN KEY([Tags_Id])
 REFERENCES [dbo].[Tags] ([Id])
 GO
 IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PostTag_Tag]') AND parent_object_id = OBJECT_ID(N'[dbo].[PostTag]'))
 ALTER TABLE [dbo].[PostTag] CHECK CONSTRAINT [FK_PostTag_Tag]
 GO
 /****** Object:  ForeignKey [FK_BlogPost]    Script Date: 02/01/2010 15:22:11 ******/
 IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_BlogPost]') AND parent_object_id = OBJECT_ID(N'[dbo].[Posts]'))
 ALTER TABLE [dbo].[Posts]  WITH NOCHECK ADD  CONSTRAINT [FK_BlogPost] FOREIGN KEY([BlogId])
 REFERENCES [dbo].[Blogs] ([Id])
 GO
 IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_BlogPost]') AND parent_object_id = OBJECT_ID(N'[dbo].[Posts]'))
 ALTER TABLE [dbo].[Posts] CHECK CONSTRAINT [FK_BlogPost]
 GO

We will need to do the following:

• Bring in a background image for our main window. I want our applications to be clear in purpose.
• Add another button to view data

Purpose of Screen: This screen is where we left off. The zip file holds it above. How To Get This Screen: : Built in previous posts

Adding a background image for our main window

First add an “Images” folder

Right mouse click on your project and select “Add / New Folder”

Purpose of Screen: Adding a new folderHow To Get This Screen: : Right mouse clicking on project folder

We will now add an image to the images folder.

https://brunoblogfiles.com/images/WindowBackground.png

Purpose of Screen: Illustrates the “Images” folderHow To Get This Screen: : Follow the step above to add the “Images” folder

You’ll need the folder path to your image to complete the next step:

Add an “Existing Item.”

Purpose of Screen: To add an image to the images folder. This image will serve as the background to a WPF Window.How To Get This Screen: : Right mouse click on the “Images” folder

Select the image. You can download my copy here.

https://brunoblogfiles.com/images/WindowBackground.png

Purpose of Screen: To choose an image to add to the “Images” folderHow To Get This Screen: : You need to “Add / Existing Item” as explained above

Finally, we have the “Images” folder ready with the image.

Purpose of Screen: The completed solution explorerHow To Get This Screen: : From the “View” menu choose “Solution Explorer”

Adding a “View Data” button

I will make the project up this point available here. Teaching the basic skill of dragging a button to the form is not in scope for this tutorial.

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

Beautiful Code

Iterating through related collections

• Two nested loops – because we have many “posts” per “blog”
• There is a one to many relationship. I will verify the database schema with you in a moment.

Purpose of Screen: To show beautiful code. No ugly queries. Just a nice intuitive one to many.How To Get This Screen: : You have to type in the code above (or download the final zip)

Code Snippet

1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Text;
5. using System.Windows;
6. using System.Windows.Controls;
7. using System.Windows.Data;
8. using System.Windows.Documents;
9. using System.Windows.Input;
10. using System.Windows.Media;
11. using System.Windows.Media.Imaging;
12. using System.Windows.Navigation;
13. using System.Windows.Shapes;
14.  
15. namespace DemoFeatures
16. {
17.     /// <summary>
18.     /// Interaction logic for MainWindow.xaml
19.     /// </summary>
20.     public partial class MainWindow : Window
21.     {
22.         public MainWindow()
23.         {
24.             InitializeComponent();
25.         }
26.  
27.         private void cmdAddData_Click(object sender, RoutedEventArgs e)
28.         {
29.             using(var context = new BloggingModelContainer())
30.             {
31.                 var blog = new Blog()
32.                 {
33.                     Id = 1,
34.                     Name = "Blog about developing software",
35.                     Owner = "Bruno Terkaly"
36.                 };
37.                 var post1 = new Post()
38.                 {
39.                     Id = 1,
40.                     BlogId = 1,
41.                     CreatedDate = DateTime.Now,
42.                     ModifiedDate = DateTime.Now,
43.                     PostContent = "Read this blog post by Bruno",
44.                     Title = "Learning ADO.NET Entity Framework - Intro"
45.                 };
46.  
47.                 var post2 = new Post()
48.                 {
49.                     Id = 2,
50.                     BlogId = 2,
51.                     CreatedDate = DateTime.Now,
52.                     ModifiedDate = DateTime.Now,
53.                     PostContent = "Read this blog post by Bruno",
54.                     Title = "Learning ADO.NET Entity Framework - Advanced Topics"
55.                 };
56.                 blog.Posts.Add(post1);
57.                 blog.Posts.Add(post2);
58.                 context.Blogs.AddObject(blog);
59.                 context.SaveChanges();
60.  
61.  
62.  
63.             }
64.         }
65.  
66.         private void cmdViewData_Click(object sender, RoutedEventArgs e)
67.         {
68.             Title = "Selecting entity data";
69.  
70.             TreeView tree = this.treeView1;
71.  
72.             using (var context = new BloggingModelContainer())
73.             {
74.                 foreach (Blog b in context.Blogs)
75.                 {
76.                     TreeViewItem itemBlog = new TreeViewItem();
77.                     itemBlog.Header = b.Name;
78.                     tree.Items.Add(itemBlog);
79.                     foreach (Post p in b.Posts)
80.                     {
81.                         TreeViewItem itemPost = new TreeViewItem();
82.                         itemPost.Header = p.Title;
83.                         itemBlog.Items.Add(itemPost);
84.                     }
85.                 }
86.             }
87.         }
88.     }
89. }

The running application using ADO.NET Entity Framework code

Our tree control now has data (populated from the the code above)

• The treeview control gets populated after clicking “View Data” in the application

Purpose of Screen: Showing the code functioning correctly. Note the tree control that has been populated with data from the BlogDB tables.How To Get This Screen: : You ran the application by hitting “F5” or “Debug / Continue” in Visual Studio

Review – Verifying our one to many by looking at SQL Server

Entity Models are based on relational data (in our case)

By now it should be obvious to anyone that has followed the last several posts that our entity data is always based on relational data. This is a quick detour back to SQL Server Management Studio 2008 that shows how to add database diagrams with a few mouse clicks.

• Our entity model is based on the relational schema
• This is easily verified in SQL Server

Purpose of Screen: Using SQL Server Management Studio to show us our relational schema through an automatically generated database diagramHow To Get This Screen: : Right mouse click as seen above

Add all of our tables

We would like to see all the relationships so we’ll select all the tables.

• Note that the junction table is needed below for the many to many relationship between Posts and Tags.
• The juncture table is called PostTag

Purpose of Screen: Adding tables for diagramming purposes (in SQL Server Management Studio)How To Get This Screen: : Follow previous steps

Database Diagram

You can see that there are various relationships that our entity modeling tools automatically leverage

Note the following:

• Blogs to Posts is a one to many relationship
• There is a many to many from Posts to Tags, and hence the need for the juncture table (PostTag)
• These complexities were abstracted away from our business code with the ADO.NET Entity Framework

Purpose of Screen: The database diagram that validates our entity model. Our code that we wrote perfectly matches this diagram without the need for messy sql statements obscuring the business logicHow To Get This Screen: : The final step to adding a database diagram

Conclusions

The main lesson is awesome looking code that is easy to read

Hopefully you learned a few more things when working with the ADO.NET Entity Framework:

• How to insert data using the Entity Framework with C# inside a WPF application
• How to read data using the Entity Framework and populate a Tree View control to depict a one to many relationship.