SQL Server Compact and LINQ to SQL
SQL Server Compact is the default database installed with Visual Studio. For more information, see PAVE OVER Using SQL Server Compact (Visual Studio).
This topic outlines the key differences in usage, configuration, feature sets, and scope of LINQ to SQL support.
By default, SQL Server Compact is installed for all Visual Studio editions, and is therefore available on the development computer for use with LINQ to SQL. But deployment of an application that uses SQL Server Compact and LINQ to SQL differs from that for a SQL Server application. SQL Server Compact is not a part of the .NET Framework, and therefore must be packaged with the application or downloaded separately from the Microsoft site.
Note the following characteristics:
SQL Server Compact is packaged as a DLL that can be used against database files (.sdf extension) directly.
SQL Server Compact runs in the same process as the client application. The efficiency of communication with SQL Server Compact can therefore be significantly higher than communicating with SQL Server. On the other hand, SQL Server Compact does require interoperability between managed and unmanaged code with its attendant costs.
The size of the SQL Server Compact DLL is small. This feature reduces the overall application size.
The LINQ to SQL runtime and the SQLMetal command-line tool support SQL Server Compact.
The Object Relational Designer does not support SQL Server Compact.
The SQL Server Compact feature set is much simpler than the feature set of SQL Server in the following ways that can affect LINQ to SQL applications :
SQL Server Compact does not support stored procedures or views.
SQL Server Compact supports only a subset of data types and SQL functions.
SQL Server Compact supports only a subset of SQL constructs.
SQL Server Compact provides only a minimal optimizer. It is possible that some queries might time out.
SQL Server Compact does not support partial trust.