Windows Phone Mango Local Database(SQL CE): [Index] attribute

published on: 8/11/2011 | Views: N/A | Tags: LocalDB Mango

by WindowsPhoneGeek

This is the 6th post from the "Windows Phone Mango Local Database(SQL CE)" series of short posts that will cover all you need to know in order  to get started using a Local Database in Windows Phone 7.1 Mango.  This time I am going to talk about using the [Index] attribute when working with Local Databases in Windows Phone 7.1 Mango.

Here is what else is included in this series:

• Windows Phone Mango Local Database(SQL CE): Introduction
• Windows Phone Mango Local Database(SQL CE): Linq to SQL
• Windows Phone Mango Local Database(SQL CE): [Table] attribute
• Windows Phone Mango Local Database(SQL CE): [Column] attribute
• Windows Phone Mango Local Database(SQL CE): [Association] attribute
• Windows Phone Mango Local Database(SQL CE): [Index] attribute
• Windows Phone Mango Local Database(SQL CE): Database mapping
• Windows Phone Mango Local Database(SQL CE): DataContext
• Windows Phone Mango Local Database(SQL CE): Connection Strings
• Windows Phone Mango Local Database(SQL CE): Creating the Database
• Windows Phone Mango Local Database(SQL CE): Database Queries with LINQ
• Windows Phone Mango Local Database(SQL CE): How to Insert data
• Windows Phone Mango Local Database(SQL CE): How to Update data
• Windows Phone Mango Local Database(SQL CE): How to Delete data

To begin with, lets first mention that basically the local database functionality in Windows Phone 7.1 is an implementation of SQL Compact for Mango. You access the data stored in a local database using LINQ to SQL.

NOTE: [Index] attribute is placed in:

Namespace: Microsoft.Phone.Data.Linq.Mapping
Assembly: System.Data.Linq (in System.Data.Linq.dll)

What is [Index] attribute?

Basically [Index] attribute specifies an additional index on a local database table. Written at the table level, designates additional indexes on the table. Each index can cover one or more columns.

NOTE: [Index] attribute is used internally by the database engine. This means that, except for defining an index, you do not have to write your LINQ to SQL queries or do anything else differently in order to use an index.

For reference you can also take a look at the MSDN documentation.

Why use the [Index] attribute?

If you are using a WHERE clause, ORDER BY or JOIN in your LINQ 2 SQL queries, an index on the appropriate columns can improve performance tremendously!

NOTE: [Index] attribute is used internally by the database engine!

How to use [Index] attribute?

The Index attribute has the following important properties:

• Columns: Gets or sets the columns on which the index is based.
• IsUnique: Gets or sets a value that indicates whether the index is unique, in which no two rows are permitted to have the same index key value.
• Name: Gets or sets the name of the index.

Example1:

[Index(Columns = "Name", IsUnique = true, Name= "city_Name")]  
[Table]
public class City
{
    private Nullable<int> countryID;
  
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int ID
    {
        get;
        set;
    }
  
    [Column(CanBeNull = false)]
    public string Name
    {
        get;
        set;
    }
  
    [Column(Storage = "countryID", DbType = "Int")]
    public int? CountryID
    {
        get
        {
            return this.countryID;
        }
        set
        {
            this.countryID = value;
        }
    }
//...
}

Sample Query:(Note that your LINQ to SQL query will look in the same way as without using any [Index] attribute but with  improved performance):

var query = from c in context.City where p.Name ="London" select p;

In this article I talked about using [Index] attribute when working with Windows Phone Mango Local Database(SQL CE). Stay tuned for the rest of the posts.

You can also follow us on Twitter @winphonegeek

Comments

Add comment to 'Windows Phone Mango Local Database(SQL CE): [Index] attribute'

Title Your Name

Comment