Windows Phone Mango Local Database(SQL CE): Linq to SQL

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

by WindowsPhoneGeek

This is the second 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.  I am going to talk about LINQ to SQL when working with Local Database (SQL CE) 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.

What is Linq to SQL?

LINQ to SQL is an O/RM (object relational mapping) framework which comes as a part of the .NET Framework.  It allows you to map your business objects(model classes) to tables in the database and then access/query local database data without writing a single line of SQL code.  With the Mango update, LINQ to SQL is now available for Windows Phone as well.

Using LINQ to SQL you can :

• Map you business objects to tables in the database
• Query the database using LINQ
• Insert data to the database using the LINQ to SQL APIs
• Update data to the database using the LINQ to SQL APIs
• Delete data to the database using the LINQ to SQL APIs

While Windows Phone supports most LINQ to SQL features, there are some limitations. You can take a look at the full MSDN Documentation for reference: LINQ to SQL Support for Windows Phone . Here are some of them:

• ExecuteCommand is not supported: Windows Phone does not support executing "raw" Transact-SQL, Data Definition Language (DDL), or Data Modeling Language (DML) statements.

• ADO.NET Objects (such as DataReader) are not supported: All data from a LINQ to SQL query is returned in an object collection of type specified by the data context.

• Only Microsoft SQL Server Compact Edition (SQL CE) data types are supported: SQL CE is the underlying database technology for a local database. For a full list of SQL CE data types, see Data Types (SQL Server Compact).

• Table.IListSource.GetList Method is not supported: To bind to all contents in a table, query the entire table and bind to the query. Handle inserts and deletes with business logic.

• BinaryFormatter is not supported: To convert custom types to a SQL Server Binary or VarBinary data type, your data context property can implement a LINQ to SQL CustomType or be of type byte[] or System.Data.Linq.Binary. To implement a LINQ to SQL CustomType, first create a custom class that implements ToString() and Parse(), and then use that class as a property in your data context. LINQ to SQL can map from CustomType to any SQL Server string types such as Char, NChar, NVarChar, Text, and XML.

• Take() requires a constant value in LINQ queries: SQL CE does not support the use of queried values within the Transact-SQL TOP statement. If you want to use a variable value within the Take method, calculate that value in a different query than the one that the Take method is used in.

• Skip() and Take() require an ordered list: These methods depend on ordering to return results in a consistent manner.

How does it work?

In short LINQ is a set of extension methods that enable you to write queries against data in a local database in C# or VB using special Query syntax.  Basically Query syntax is a convenient declarative shorthand for expressing queries using the standard LINQ query operator. Here are the basic things you need to know before getting started:

1. Usually LINQ to SQL query expression in begins with a "from" clause and ends with a "select" clause.

2. The "from" clause indicates what data you want to query(usually you query data from a collection/datacontext)

3. The "select" clause indicates what data you want returned, and in what format it should be in.

4. Whenever you want to filter data you can use "where" clause.

5.  For ordering data the clause is "orderby"

NOTE: LINQ to SQL  does all of the filtering and ordering in the database layer - which makes it very efficient. In order to do this, the LINQ queries that you write in C# (for example), are automatically translated to SQL and then executed by the SQL runtime. So when you execute the following LINQ query(written in your WP7 app):

var query = from p in context.Persons where p.Age > 18 select p;

It is automatically translated by the LINQ to SQL runtime to the following SQL query before it is actually executed:

SELECT [t0].[ID], [t0].[FirstName], [t0].[LastName], [t0].[Age] 
FROM [dbo].[People] AS [t0] 
WHERE [t0].[Age] > @p0

NOTE: For more information about the complete LINQ syntax you can take a look at the official documentation: LINQ: .NET Language-Integrated Query

In this article I talked about using LINQ to SQL 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): Linq to SQL'

Title Your Name

Comment