Coming In Mango–Local DB Part 2: Relationships

On May 10 I posted about the upcoming addition of SQL Server Compact Edition (CE) RELATIONALDB in Windows Phone Mango.  At that time, I showed how to create the DataContext object and how to store and retrieve “flat” data.

In this posting, I build on and modify the previous example to demonstrate relational data.

We will have a Book class and a Publisher class.  The relationship is one to many (each book has one publisher, but any publisher may have many books).

We can model this by modifying the Book class,

using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using Microsoft.Phone.Data.Linq.Mapping;

namespace DataBase101
{

    [Table]
    public class Book
    {
        [Column( IsPrimaryKey = true )]
        public string BookID { get; set; }

        [Column]
        public string Title { get; set; }

        [Column]
        public string PublisherID { get; set; }

        private EntityRef<Publisher> _publisher;

        [Association(
            OtherKey = "PublisherID",
            ThisKey = "PublisherID",
            Storage = "_publisher" ) ]
        public Publisher BookPublisher
        {
            get
            {
                return _publisher.Entity;
            }
            set
            {
                _publisher.Entity = value;
                PublisherID = value.PublisherID;
            }
        }

        [Column]
        public DateTime PublicationDate { get; set; }

    }
}

The highlighted lines show the declarations for the the Publisher.   We have a public PublisherID which allows us to link this Book’s publisher to the ID of a publisher in the database.  In addition, we have a private Entity Reference back to the instance of the Publisher class.   Finally, we add the public property BookPublisher, adorning it with the Association attribute. In this case we’ve added three properties to the attribute:

  • OtherKey – the key as it is represented in the Publisher class
  • ThisKey – the foreign key in the Book class
  • Storage – the backing variable for the property

Since we are only going to be following the association from the Book to the Publisher (and not back the other way) that’s all we have to do to the classes.

We can now instantiate a couple publishers,

 BooksDataContext db = new BooksDataContext( "isostore:/bookDB.sdf" );
 Publisher pub = new Publisher()
 {
     PublisherID = "1",
     Name = "APress",
     City = "Acton",
     Url = "http://Apress.com"
 };
 db.Publishers.InsertOnSubmit( pub );

 Publisher pub2 = new Publisher()
 {
     PublisherID = "2",
     Name = "O'Reilly",
     City = "Cambridge",
     Url = "http://Oreilly.com"
 };
 db.Publishers.InsertOnSubmit( pub );

and then we can instantiate a Book object.  Notice that we handle the relationship from an object perspective, making an instance of the publisher a member of the Book object (tying this back to the EntityRef shown above),

 Book theBook = new Book()
 {
     BookID = "1",
     BookPublisher = pub,
     PublicationDate = DateTime.Now,
     Title = "Programming Reactive Extensions"
 };
 db.Books.InsertOnSubmit( theBook );

We can make as many Book instances as we like, assigning either pub or pub2 (or any other Publisher objects we create) to each Book,

theBook = new Book()
{
    BookID = "2",
    BookPublisher = pub,
    PublicationDate = DateTime.Now,
    Title="Migrating to Windows Phone"
};
db.Books.InsertOnSubmit( theBook );

theBook = new Book()
{
    BookID = "3",
    BookPublisher = pub2,
    PublicationDate = DateTime.Now,
    Title = "Programming C#"
};
db.Books.InsertOnSubmit( theBook );

Searching

When we are ready to display the Books we will execute a query obtaining the Books, ordered by Title. We can then assign the results to the ItemsSource property of the ListBox,

var q = from b in db.Books
        orderby b.Title
        select b; 

BooksLB.ItemsSource = q;

It is slightly more efficient to create a temporary class with just the fields we need,

var q = from b in db.Books
        orderby b.Title
        select new
        {
            b.Title,
            b.PublicationDate,
            b.BookPublisher
        };

BooksLB.ItemsSource = q;

Note carefully that Anonymous Types are internal and by default Windows Phone (and Silverlight) does not allow reflection into internal types.  To allow this anonymous type to provide data via binding you need to add the following line to AssemblyInfo.cs

[assembly: InternalsVisibleTo("System.Windows")]

Binding the Data

We need to modify the Xaml to bind to the publisher as a reference within the book object (we do this with the Path property).  Here is the relevant excerpt,

<ListBox
  Name="BooksLB"
  Grid.Row="2"
  VerticalAlignment="Stretch"
  HorizontalAlignment="Stretch"
  Margin="20">
  <ListBox.ItemTemplate>
    <DataTemplate>
      <StackPanel>
        <TextBlock
          Text="{Binding Title}" />
        <StackPanel
          Orientation="Horizontal">
          <TextBlock
           Text="Published: " />
          <TextBlock
           Text="{Binding PublicationDate}" />
        </StackPanel>
        <TextBlock
          Text="{Binding Path=BookPublisher.Name}" />
        <TextBlock
          Text="------------------" />
      </StackPanel>
    </DataTemplate>
  </ListBox.ItemTemplate>
</ListBox>

About Jesse Liberty

Jesse Liberty has three decades of experience writing and delivering software projects and is the author of 2 dozen books and a couple dozen online courses. His latest book, Building APIs with .NET will be released early in 2025. Liberty is a Senior SW Engineer for CNH and he was a Senior Technical Evangelist for Microsoft, a Distinguished Software Engineer for AT&T, a VP for Information Services for Citibank and a Software Architect for PBS. He is a Microsoft MVP.
This entry was posted in Data, Linq, Mini-Tutorial, WindowsPhone and tagged . Bookmark the permalink.

8 Responses to Coming In Mango–Local DB Part 2: Relationships

Comments are closed.