Project Turing: Multiple Tables & RIA Services

MiniTutorialLogo

ProjectTuringLogo

Turing Project Page: [Novice: 10 | Advanced: 7 ]
FAQ | Table of Contents | Definitions

What is this and where do I start?

In the previous listing in the Project Turing series, I examined extracting data from a single table and displaying it in the DataGrid.  This time we’ll modify the program by including data from related data (the Blog and its author) retrieved from two (joined) tables.

Let’s start by opening the project as it was after the previous posting (source available here). Delete the Data Entity Frameworks and the RIA Services projects by removing the four associated files from the project.

DeleteProjects

Displaying Data From Two Tables In The DataGrid

Return to Blogs.xaml, replace the DataGrid with the following code

<data:DataGrid x:Name="BlogsDataGrid"
               MinHeight="150"
               IsReadOnly="True"
               AutoGenerateColumns="False" >
   <data:DataGrid.Columns>
      <data:DataGridTextColumn x:Name="BlogName"
                               Binding="{Binding BlogName}"
                               Header="Blog Name" />
      <data:DataGridTextColumn x:Name="BlogURL"
                               Binding="{Binding BlogURL}"
                               Header="URL" />
      <data:DataGridTextColumn x:Name="First"
                               Binding="{Binding First}"
                               Header="First Name" />
      <data:DataGridTextColumn x:Name="Last"
                               Binding="{Binding Last}"
                               Header="Last Name" />
      <data:DataGridTextColumn x:Name="Email"
                               Binding="{Binding Email}"
                               Header="Email" />
      <data:DataGridTextColumn x:Name="Phone"
                               Binding="{Binding Phone}"
                               Header="Phone" />
   </data:DataGrid.Columns>
</data:DataGrid>

Obtaining The Object Graph

The two most common ways to obtain the data from joined tables is to delegate that responsibility either to the database, by creating the view, or by using the ability of the Data Entity Framework to return the entire object graph.

Remember that our database design creates a one to many relationship between Bloggers (one) and their Blogs (many). This is reflected in the database diagram, and will be reflected in our Data Entity Model by having each Blog object contain a Blogger object.

As you did previously, right-click on the web project choose add new, select the data, select ADO.net Entity Data Model and mean the new model Blogs.emdx.

As you did previously, indicate that you want to build your model from the database, choose the appropriate connection string, click okay and in the Entity Data Model Wizard expand and choose both the Blogs and the Bloggers table. Name the Blogs model and click on the finish button.

It is instructive to compare side-by-side the SQL Server diagram of the tables and the Data Entity Model you just created, as shown in the next illustration with the Data Entity objects on the bottom

RelationalAndObject

In this case, because of the simplicity of the design, there is an isomorphic mapping from the relational database tables to the data objects. It is important to note that this need not be true, and that a given data object may be composed from numerous tables, and may contain a subset or superset or a composite of the values stored in the database.

In this case, the most important thing brought forward into the data object model is the one to many relationship between Bloggers and Blogs. This is manifested in the Blogs class by the navigation property Bloggers, which serves as a reference to all of the Bloggers who own this Blog (by design that will always be one object as in our design while one Blogger may own many Blogs, each Blog is owned by a single Blogger)

The key point, is that when we retrieve the Blog, we can instruct the data model either to return a reference to each Blogs Blogger, or, via containment, to return each logger object within each Blog object.

In this case, so that we can display information in the data grid about both the Blog and the Bloggers, rather than thinking about joining the tables as a database programmer might, we will instruct the data Entity Frameworks to return the object graph: that is to return each Blog will have its Blogger via containment.

Using attributes in the metadata

To accomplish this, first build the web project so that your new data model will be available. Next right-click on the project and choose add new, in the Categories choose Web, and in the templates area choose Domain Service Class.

Give it the name Blog service and click the add button, and in the Add New Domains Service Class dialogue be sure to check all of the checkboxes before pressing okay.

The RIA Services framework will generate both the Blogs service and a file with its metadata. Open the metadata file where you will find classes representing the Bloggers metadata and the Blogs metadata. The last few lines of the Blogs metadata class represents the public properties the first of which is of type Bloggers. You will need to decorate that property with the include attribute to indicate which fields you want included in to the Blogs class from the Bloggers object as shown in the next snippet of code

[Include( "alias", "Email" ), Include( "FirstName", "First" )]

[Include( "LastName", "Last" ), Include( "phone", "Phone" )]

C#

public Bloggers Bloggers

VB.Net

public Bloggers as Bloggers

Notice that I am including four fields: alias, FirstName, LastName, and phone, and while I’m at it I am indicating that alias should be returned as “Email,” FirstName as “First,” LastName as “Last,” and phone with a lowercase P. as Phone with an P.

Having indicated in the metadata what fields of Bloggers to return, I now must modify the get Blogs query to ensure that Bloggers are returned as entities rather than references. To do so open the BlogsService.cs file and navigate to the GetBlogs method, which you will modify as indicated in the next listing

C#

public IQueryable<BLOGS> GetBlogs()
{
     return this.Context.Blogs.Include("Bloggers" );
}

VB.NET

Public Function GetBlogs() As IQueryable(Of Blogs)
     Return Me.Context.Blogs.Include("Bloggers")
End Function
Be sure to rebuild the project. Note, you may have to temporarily comment out the BlogsService lines in Blogs.xaml.cs)

Modifying Blogs.xaml

Return to Blogs.xaml.cs and make sure that the context there is named BlogsContext or change it to be so. In any case the logic is unchanged from the previous version:, we will instantiate a BlogsContext object, use that to load the results of our now modified GetBlogs query, and assign the results to the DataGrid’s ItemsSource property:

C#

private readonly BlogsContext bc = new BlogsContext();
  public Blogs()
   InitializeComponent();
   bc.Load( bc.GetBlogsQuery() );
  this.blogsDataGrid.ItemsSource = bc.Blogs;

VB.Net

Private ReadOnly bc As New BlogsContext()
  public Blogs() InitializeComponent()
   bc.Load(bc.GetBlogsQuery())
  Me.blogsDataGrid.ItemsSource = bc.Blogs

Build and run the application

MultiTableRunning

(Click on image to enlarge)

This entire post was dictated and translated by computer; apologies in advance for any resulting absurdities.

Completed Code    DataBase Backup

Novice Previous:  Ria Services Part 1 Next: Domain Data Source Control
Advanced Previous:  Ria Services Part 1 Next:  Domain Data Source Control
Share

About Jesse Liberty

Jesse Liberty is a Master Consultant for Falafel Software, and has three decades of experience writing and delivering software projects. He is the author of 2 dozen books and multiple Pluralsight courses, and has been a Technical Evangelist for Telerik and for Microsoft, a Distinguished Software Engineer for AT&T, a VP for Information Services for Citibank and a Software Architect for PBS.
This entry was posted in Mini-Tutorial, z Silverlight Archives and tagged , . Bookmark the permalink.

2 Responses to Project Turing: Multiple Tables & RIA Services

  1. Francis Irungu says:

    Hi,

    How about saving data to multiple tables. The retrieving is not so hard given its just decorating with a single Include and changing an IQueryable. Could you kindly give an example of saving to two or three related tables say Patient [PatientID,Name…] and PatientAllergies[ID,PatientId,Allergy,Comment..]. An RIA Services MVVM example would really help.

    Thanks.

    Francis

  2. Climb says:

    How about triple tables,I tried to get a field of the third table, but I failed.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>