On May 10 I posted about the upcoming addition of SQL Server Compact Edition (CE) 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>
good job! What about Many to Many relationships?
Hi, Jesse how to a query obtaining the Books with where condition and to display publisher name not publisher id
Because i got exception Can not access a disposed object.
Object name: ‘DataContext accessed after Dispose
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!
Sorry JESSE
Great mini tutorials. Looking foward when Mango is released. Dale
Nice series you are doing. Thanks for sharing, Karl
Thanks Karl! Great to have you here, I admire your work.