This is another post in a series of posts on how to use SQL CE on WP7 Mango.  If you would like to see the other posts they are listed below

• Using SQL CE on WP7 Mango–Getting Started
• Using SQL CE On WP7 Mango–Working with Associations
• Using SQL CE On WP7 Mango–Working with Indexes

In my first post we took a step by step look at how to get up and running using SQL CE inside of your WP7 Mango Application.  However, in that previous post I showed you how to get up and running on a database with only 1 column and we all know that most production database have at least 2 tables : ) 

In this post we will focus on how to add additional tables to the mix and how to setup relationships between these tables.

In my prior post I setup my Episode model as below

using System.Data.Linq;
using System.Data.Linq.Mapping;

[Table]
public class Episode
{
    [Column(IsPrimaryKey = true, IsDbGenerated = false, DbType = "INT NOT NULL", CanBeNull = false)]
    public Int32 Id { get; set; }

    [Column]
    public Int32 Number { get; set; }

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

[Table]
public class EpisodeTag
{
    [Column(IsPrimaryKey = true, IsDbGenerated = false, DbType = "INT NOT NULL", CanBeNull = false)]
    public Int32 Id { get; set; }

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

Now that we know the basic layout of the 2 models how do go about setting up the Association between them?

Step 1: Update our Datacontext to have a pointer to the new [Table] (aka model)

public class DimecastsDataContext : DataContext
{
    public DimecastsDataContext() : base("Data Source=isostore:/DimecastsData.sdf")
    {

    }

    public Table<Episode> Episodes;

    public Table<EpisodeTag> EpisodeTags;

The change in the context is the addition of the new EpisodeTags table pointer.  If you do not add this you will get a SqlCEException – Table not found error when you try to access information in this table.

Step 2: Setup the Association between Episode and Episode Tag (parent down to child)

Now that we have added the table pointer in our datacontext the next thing we need to do is actually setup our pointers and mappings from Episode to EpisodeTags.  The code below is the final model showing the needed attributes. 

[Table]
public class Episode
{
    [Column(IsPrimaryKey = true, IsDbGenerated = false, DbType = "INT NOT NULL", CanBeNull = false)]
    public Int32 Id { get; set; }

    [Column]
    public Int32 Number { get; set; }

    [Column]
    public string Name { get; set; }
        
    private EntitySet<EpisodeTag> _episodeTags = new EntitySet<EpisodeTag>();

    [Association(Storage = "_episodeTags", OtherKey = "_episodeId", ThisKey = "Id")]
    public EntitySet<EpisodeTag> EpisodeTags
    {
        get { return _episodeTags; }
        set { _episodeTags.Assign(value); }
    }
}

Lets take a look at what changes were made to this model.

1. Addition of the EntitySet<EpisodeTag> property
   • This will allow us to navigate from the Episode (Parent) to the EpisodeTag (Child) objects
2. Addition of the EntitySet<EpisodeTag> private backing field
   • This backing field is what will be set by the database engine when building the object graph, set via the ‘Storage’ property
3. Addition of the [Association] attribute
   1. Storage Property - Gets or sets a private storage field to hold the value from a column.
   2. OtherKey (aka Foreign Key) Property - Gets or sets one or more members of the target entity class as key values on the other side of the association
   3. ThisKey (aka Primary Key) Property - Gets or sets members of this entity class to represent the key values on this side of the association

Step 3: Setup the Association between EpisodeTag and Episode (child backwards to parent)

Now that we can walk from the Episode (parent) to the child (EpisodeTag) we need to setup the ability to from the opposite direction.  In the code below we do exactly that.

[Table]
public class EpisodeTag
{
    [Column(IsPrimaryKey = true, IsDbGenerated = false, DbType = "INT NOT NULL", CanBeNull = false)]
    public Int32 Id { get; set; }

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

    [Column]
    internal int? _episodeId;

    private EntityRef _episode;

    [Association(Storage = "_episode", ThisKey = "_episodeId", OtherKey = "Id", IsForeignKey = true)]
    public Episode Episode
    {
        get { return _episode.Entity; }
        set { _episode.Entity = value; }
    }       
}

Lets take a look at what we added, but as you may have already noticed this code is VERY similar to the code needed for Episode.

1. Addition of the Episode property
   • This allows us to walk the object tree backwards to get to the parent
2. Addition of the EpisodeID backing field
   • Since the DB Table needs to hold the ID this is how this is accomplished.
3. Addition of the Episode EntityRef property
   • This is an object pointer which is based off the _episdoeId backing field
4. Addition of the [Association] attribute to the Episode Property
   1. Storage Property - Gets or sets a private storage field to hold the value from a column
   2. OtherKey (aka Foreign Key) Property - Gets or sets one or more members of the target entity class as key values on the other side of the association
   3. ThisKey (aka Primary Key) Property - Gets or sets members of this entity class to represent the key values on this side of the association
   4. IsForeignKey Property - Gets or sets the member as the foreign key in an association representing a database relationship

Now that we have our mappings setup to allow for bi-directional mapping lets take a look at how we can use these mappings to insert data as well as query for exisiting data

How to insert data

var newEpisode = new Episode
    {
        Id = 1,
        Number = 1,
        Name = "Something Cool",
        EpisodeTags = new EntitySet
                            {
                                new EpisodeTag {Id = 1, TagName = "Tag"},
                                new EpisodeTag {Id = 2, TagName = "Tag"}
                            }
    };

How to query data

var episodes = from e in DimecastsDataContext.Episodes
                           select e;

There you have it all the information need to understand how to setup an Association between 2 tables (models) inside of Sql CE.

Till next time,