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

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

Previous Blog Entries

 

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

http://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. snap957
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 folder
snap958
How To Get This Screen: : Right mouse clicking on project folder

We will now add an image to the images folder.

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

Purpose of Screen: Illustrates the “Images” folder
snap959
How 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.
snap960
How To Get This Screen: : Right mouse click on the “Images” folder

Select the image. You can download my copy here.

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

Purpose of Screen: To choose an image to add to the “Images” foldersnap961
How 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 explorer
snap962
How 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.

http://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.snap966
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.
snap967
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 diagram
snap968
How 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)
snap969
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 logic
snap970
How 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.