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 this posting we will take a deeper look at how we can setup and use indexes on our SQL CE database table.  Using Indexes inside of WP7 for SQL CE follows the same basic pattern as setting up Tables or Columns which means that we are going to introduce a new attribute called [Index].  The only difference is that this attribute is not part of the System.Data.Linq.Mapping namespace, but rather part of the Microsoft.Phone.Data.Linq.Mapping.

When setting up an index there is 2 things that this index can do

1. Help speed up your queries
2. Provide the ability to enforce unique constraints  on your data

Step 1: Adding reference to the correct namespace

You will need to add a reference to Microsoft.Phone.Data.Linq.Mapping

Step 2: Defining your index

When setting up your index you of course will need to use the [Index] attribute, but this attribute does NOT go at the column level, but rather at the table level. And since the Index attribute is setup as AllowMultiple == true you have the ability to setup many indexes for a given table

[Index(Columns = "Name", Name = "i_Name")]
[Index(Columns = "Name, Number", Name = "i_Name_Number")]
[Table]
public class Episode
{
	// blah, blah, blah
}

Taking a deeper look at the code above there is a few items we should point out

1. Name Property - Gets or sets the name of the index
2. Columns Property - Gets or sets the columns on which the index is based.
   1. Indexing Multiple Columns: This is done by putting a ‘,’ between the names of the columns.  This MUST be a comma otherwise you will get a InvalidOperationException and it will tell you it cannot find the key Member [ColumnNameHere]
   2. If you fat finger or misspell a column when setting up your index you will also get an exception of type -- InvalidOperationException and it will tell you it cannot find the key Member [ColumnNameHere]

Step 3: Adding the unique constraint to your index

Now adding an index for performance reasons is one thing, but adding it to enforce unique constraints is s a very powerful feature.  Fortunattly adding uniqueness to an index is CAKE, you simply add the IsUnique property as below

[Index(Columns = "TagName", Name = "i_TagName", IsUnique = true)]
[Table]
public class EpisodeTag
{
	// blah, blah, blah
}

Now that you have added the uniqueness constraint you must be careful because if/when you try to save data into that column that violates the constraint you will receive an exception as below.

As you can see working with indexes in SQL CE on WP7 mango is pretty easy and pretty powerful.

Till next time,