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>
Share

About Jesse Liberty

Jesse Liberty is an independent consultant and programmer with 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 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 Xamarin Certified Mobile Developer and a Xamarin MVP, Microsoft MVP and Telerik MVP.
This entry was posted in Data, Linq, Mini-Tutorial, WindowsPhone and tagged . Bookmark the permalink.

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

  1. Fredo says:

    good job! What about Many to Many relationships?

  2. Eng Dev says:

    Hi, Jesse how to a query obtaining the Books with where condition and to display publisher name not publisher id

  3. Hello Jessy

    I have 2 questions:
    + I have seen some example of many to one associations that use EntitySet type for the navigation property to the many end point of the association. Do you think it could be possible to use an Observable Collection instead?
    + What about Many to Many relationships, how are they supported on SQLCE for WP7?

    Regards!

  4. Pingback: Arsanth » Blog Archive » Some LINQ to SQL CE performance considerations for WP7

  5. Pingback: Arsanth » Blog Archive » Arsanth Daily – May 25th

  6. Dale says:

    Great mini tutorials. Looking foward when Mango is released. Dale

  7. Pingback: Coming In Mango–Local DB Part 2: Relationships | WP7 Developers Links

  8. Nice series you are doing. Thanks for sharing, Karl

Leave a Reply

Your email address will not be published.