Windows Phone Mango Local Database(SQL CE): Database Queries with LINQ

published on: 10/5/2011 | Views: N/A | Tags: LocalDB

by WindowsPhoneGeek

This is the 11th post from the "Windows Phone Mango Local Database" 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 database queries with LINQ  when working with a Windows Phone 7.1 Mango local database.

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

What is a Database query?

On Windows Phone, Language Integrated Query (LINQ) is used to query the database. LINQ is used to represent the connection between the objects and the real data. Queries in LINQ to SQL use the same syntax as queries in LINQ.

Because objects referenced in LINQ to SQL queries are mapped to records in a database, LINQ to SQL differs from other LINQ technologies in the way that queries are executed. A typical LINQ query is executed in memory at the application layer. With LINQ to SQL, using the object-relational capabilities of the runtime, each LINQ query is translated to Transact-SQL and then executed directly in the database. This can yield a performance gain for queries such as selecting a few records out of a large database.

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

You can also take a look at our previous post: Windows Phone Mango Local Database(SQL CE): Linq to SQL

How to Select Data?

In the following example, a DataContext object named "CountryDataContext" is queried with LINQ to SQL and the results are placed into an IList collection of Country objects named countryList .

NOTE: In the next example we will use the "CountryDataContext"  data context explained in the previous posts of this series.

Example 1 - Select all country records from the database:

private IList<Country> GetCountries()
{
    IList<Country> countryList = null;
    using (CountryDataContext context = new CountryDataContext(ConnectionString))
    {
        IQueryable<Country> query = from c in context.Countries select c;
        countryList = query.ToList();
    }
 
    return countryList;
}

Example 2 - Select all country records from the database where the country name starts with "U'":

private IList<Country> GetCountriesStartingWithU()
{
    IList<Country> countryList = null;
    using (CountryDataContext context = new CountryDataContext(ConnectionString))
    {
        IQueryable<Country> query = 
            from c in context.Countries 
            where c.Name.StartsWith("U") 
            select c;
        countryList = query.ToList();
    }

    return countryList;
}

In this article I talked about database queries with LINQ  when working with a Windows Phone 7.1 Mango local database. Stay tuned for the rest of the posts.

You may also find interesting the following articles:

• Windows Phone Mango Local Database(SQL CE): Introduction
• Using SqlMetal to generate Windows Phone Mango Local Database classes
• A Simple ToDo List: Persisting Data with the Sterling NoSQL Database
• All about WP7 Isolated Storage series

You can also follow us on Twitter @winphonegeek

Comments

Add comment to 'Windows Phone Mango Local Database(SQL CE): Database Queries with LINQ'

Title Your Name

Comment