Derik Whittaker

Syndication

News


Using SQL CE On WP7 Mango–Working with Associations

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

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; }
   
}
In this post we want to create an additional model called EpisodeTag.  This will add a 0 to N relationship between Episodes and EpisodeTag

image

[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,


Posted 07-23-2011 1:20 PM by Derik Whittaker
Filed under: , ,

[Advertisement]

Comments

Derik Whittaker wrote Using SQL CE On WP7 Mango–Working with Indexes
on 07-24-2011 3:58 PM

This is another post in a series of posts on how to use SQL CE on WP7 Mango.  If you would like

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)