LINQ to SQL (SQL Server Compact)
Language-Integrated Query (LINQ to SQL) is a component of the .NET Framework 3.5 that provides a run-time infrastructure for managing relational data as objects, without losing the ability to query. LINQ to SQL accomplishes this by translating language-integrated queries into SQL for execution by the database, and then translating the tabular results back into objects you define. Your application is then free to change the objects and LINQ to SQL stays in the background tracking your changes automatically.
Together, the LINQ to SQL run-time infrastructure and design-time tools significantly reduce the workload for the database application developer. LINQ to SQL is language-agnostic. Any language built to provide Language-Integrated Query can use it to enable access to information stored in relational databases.
Note
Relational data appears as a collection of two-dimensional tables (relations or flat files), where common columns relate tables to each other. To use LINQ to SQL effectively, you must have some familiarity with the underlying principles of relational databases.
LINQ to SQL in SQL Server Compact
By default, SQL Server Compact 3.5 is installed for all editions and is available on the development computer for use with LINQ to SQL. However, deployment of an application that uses SQL Server Compact 3.5 and LINQ to SQL differs from the procedure for deploying a SQL Server application. Because SQL Server Compact 3.5 is an embedded database, it packages the provider with the engine.
SQL Server Compact 3.5 is like SQL Server because it must be installed separately from the .NET Framework. However, SQL Server Compact 3.5 can be privately deployed, or embedded in the application. This makes the use of local data transparent to your application's installations requirements. You can embed SQL Server Compact 3.5 with your application, or you can use the SQL Server Compact 3.5 Windows Installer file (SSCERuntime-<lang>.msi).
SQL Server Compact 3.5 is packaged as a set of DLLs that can work with database files (.sdf) directly. The SQL Server Compact 3.5 DLLs files are small, which reduces the overall application size. Embedded deployments do not require administrative rights to add local data storage functionality.
The benefits of LINQ to SQL functionality in SQL Server Compact 3.5 include the following:
- Familiar SQL Server syntax for writing queries.
- Compile-time checking for syntax errors and type safety.
- Improved debugger support.
- IntelliSense support.
- Powerful filtering, ordering, and grouping capabilities.
- Consistent model for working with data across various kinds of data sources and formats.
Differences from SQL Server
The SQL Server Compact 3.5 feature set provides a rich subset of the other SQL Server versions related to local, embedded data scenarios. By using embedded deployment, SQL Server Compact 3.5 supports a rich set of functionality for local data scenarios consistent across all Microsoft clients from devices to desktops. LINQ to SQL in SQL Server Compact 3.5:
- Supports the LINQ to SQL runtime and the SQLMetal command-line tool.
- Supports a rich subset of the SQL Server data types and functions.
- Supports a rich subset of SQL constructs related to local data scenarios.
- Query optimizer is targeted around local data scenarios and might time out if LINQ to SQL queries become too complex as mapped to SQL queries.
- Does not support stored procedures or views.
- Does not support Object Relational Designer.
- Does not support partial trust.
To optimize performance of LINQ to SQL in SQL Server Compact 3.5, the LINQ to SQL compiled query feature should be used to pre-create the Transact-SQL query. A compiled query should be used where you can do so. In addition, the LINQ to SQL DataContext should be used in read-only mode when updates are not needed. To set DataContext, use the following statement:
DataContext.ObjectTrackingEnabled=false
See Also
Other Resources
LINQ to SQL
Language-Integrated Query (LINQ)