Windows phone Local Database (SQLCE): 2, LINQ to SQL (translation)

First and foremost, the basic database functionality on Windows Phone 7.1 is an implementation of SQL Compact on Mango that accesses data stored on the database using LINQ to SQL.

1. What is LINQ to SQL

LINQ to SQL is an ORM (object-relational mapping) framework that is part of the. NET Framework. It allows you to map your business objects (model classes) to tables in the database, and then you can access or query the data in the database without having to write a single SQL statement. With the mango update, LINQ to SQL is now available for Windows Phone.

you can use LINQ to SQL:1. Map your business object to a table in the database2. Querying the database using LINQ3. Use LINQ to SQL APIs to insert data into the database4. Updating data using LINQ to SQL APIs5. Using LINQ to SQL APIs to delete dataAlthough the Windows Phone supports most of the features of LINQ to SQL, it has some limitations. You can take a look at the full MSDN Document http://msdn.microsoft.com/zh-cn/library/hh202872 (v=vs.92). aspx. some of them are given below: 1. Executecommand:windows Phone does not support the execution of Raw Transact-SQL, Data Definition language (DDL), or Data Modeling Language (DML) statements.      2, does not support ADO (for example, DataReader): All data for LINQ to SQL queries will be returned in the collection of types specified by DataContext 3 . Support for Microsoft SQL Server Compact Edition (SQL CE) data type only: SqlCe is the underlying database technology for the local database. For a complete list of SQLCE data types, see: http://msdn.microsoft.com/zh-cn/library/ms172424 (v=sql.110). aspx 4. Table.IListSource.GetList is not supported: to bind to all content in a table, query the entire table and bind to the query. Use business logic to handle insertions and deletions. 5. BinaryFormatter (binary type) is not supported: To convert a custom type to a SQL Server binary or varbinary data type, your data context properties can implement LINQ to SQL Customtype, or can be a byte[] or System.Data.Linq.Binary type. To implement LINQ to SQL Customtype, you 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 be mapped from customtype to any SQL Server string type, such asChar,NChar,NVarChar, Text,and XML. 6. Take () Use constant values in LINQ queries : SQL CE does not support the use of query values in a Transact-SQL TOP session. If you want to use variable values in the Take method, calculate the value in other queries that do not use the Take method. 7. Skip () and take () need to use a sorted list: These methods rely on sorting to return consistent results. 2. How does it work ?

Simply put, LINQ is a set of extension methods that allow you to query data in a database using special query syntax in C # or VB. To represent queries using standard LINQ query Operators, the basic query syntax is a convenient declarative shorthand. Before you begin, you need to understand some basic things:

1. Linq to SQL query expressions usually start with a "from" clause and end with a "select" clause2. The "from" clause represents the data you want to query (usually you query the data from the Collection/datacontext)3, the "SELECT" clause represents the data you want to return, and what format it should be4. You can use the "where" clause whenever you want to filter the data5. Arrange the data by the "ORDER BY" clause Note : LINQ to SQL completes all filtering and sorting at the database level, which makes it very efficient. To do this, you can write a LINQ query in C # code that is automatically translated into SQL, and then SQL runtime executes the statement, so when you execute the following LINQ query

Select P;

The LINQ to SQL runtime automatically translates it into the following SQL query before it is actually executed

1SELECT[T0].[Id],[T0].[FirstName],[T0].[LastName],[T0].[age 2 from [dbo]. [people as [t0 3 where [t0]. [age >  @p0           

Note : For more about the complete LINQ syntax you can look at the official document: Http://msdn.microsoft.com/zh-cn/library/bb308959.aspx

This article I talked about using LINQ to SQL in the Windows Phone Mango Local database. Keep your eye on the next article.