Querying data from Sql Server Compact database gets easier!
“LINQ” stands for .net Language INtegrated Query. LINQ-enabled languages can provide full type-safety and compile-time checking of query expressions, and development tools can provide full intellisense, debugging, and rich refactoring support when writing LINQ code.
What is LINQ to SQL?
LINQ to SQL provides a runtime infrastructure for managing relational data as objects (object relational mapping) without losing the ability to query. You can query the database using LINQ and also update, insert or delete from it. LINQ to SQL stays in the background tracking your changes automatically.
LINQ to SQL provides an easy way to integrate data validation and business logic rules into your data model. Best of all, LINQ to SQL applications are easy to get started. LINQ to SQL: .NET Language-Integrated Query for Relational Data is an informative article on the topic.
Note: LINQ to SQL was formerly called DLINQ.
The good news for SQL Compact developers is that from Visual Studio ‘Orcas’ Beta2, LINQ to SQL will support SQL Compact as an underlying database!
Creating your first LINQ to SQL application over SQL Server Compact:
· Run the Windows SDK tool Program Files\Microsoft SDKs\Windows\v6.0A\Bin\SqlMetal.exe against your favorite sdf file. This will generate a .cs file.
e.g. >Sqlmetal.exe Northwind.sdf /code: Northwind.cs
· Create a new project and add the SqlMetal generated file to it.
· In solution explorer, add a reference to System.Data.Linq.dll.
· Instantiate the class that derives from DataContext (e.g. Northwind class for Northwind.cs) using connection string.
· Start writing queries against the instance just created.
Note: We didn’t specify SQL Compact as the provider anywhere! It is determined at runtime (unless you choose to override the default as a SQLMetal command line option).
Here’s a sample program:
static void Main(string args)
string connectionstring = "...";
Northwind db = new Northwind(connectionstring);
db.Log = Console.Out; // This lets you see the SQL we generate easily
var q = from c in db.Customers
where c.City == "London"
foreach (Customer cust in q) Console.WriteLine(cust.ContactName);
Now that you have your first program running, the article LINQ to SQL: .NET Language-Integrated Query for Relational Data should help discover other features of LINQ to SQL – a comprehensive but concise piece of write up! There are also numerous good posts on writing LINQ applications. I’ll point you to some of my favorites at the end of this post.
Differences between LINQ to SQL for SQL Server and SQL Server Compact:
Here are some of my favorite LINQ/ LINQ to SQL resources: