Derik Whittaker



Using SQL CE On WP7 Mango–Working with Indexes

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 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")]
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)]
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,

Posted 07-24-2011 2:58 PM by Derik Whittaker
Filed under: , ,


About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Google Reader or Homepage Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of
Red-Gate Tools For SQL and .NET


SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
NHibernate Profiler
Balsamiq Mockups
JetBrains - ReSharper
Web Sequence Diagrams
Ducksboard<-- NEW Friend!


Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers


Community Server (Commercial Edition)