Partager via

LINQ with SQL Server Compact (a.k.a. DLINQ with SQL CE)

  • Article

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:

using System;

using System.Linq;

using System.Collections.Generic;

using System.Text;

class 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"

                 select c;

         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:

  • Notice the command line tool I referred you to for generating the object-relational mapping. While SQL Server will have a nice designer to do the same thing, atleast till Orcas, SQL Compact will have to do with SqlMetal command line tool. No designer support. (If you prefer command line tools like me, you shall have no problem!)
    • Note: SQLMetal extracts SQL metadata from your database and generates a source file containing entity class declarations. Alternatively, you can split the process into two steps, first generating an XML file representing the SQL metadata and then later translating that XML file into a source file containing declarations. This split process allows you to retain the metadata as a file so you may edit it.
  • LINQ queries are eventually mapped to SQL queries. Since SQL supported by SQL Server Compact is a strict subset of that on SQL Server, LINQ follows suit. (To see the SQL which is generated, set db.Log = Console.Out where db is the DataContext.).
  • Stored procedures and views are not supported by SQL Server Compact, and so will not be supported through LINQ to SQL either.

Here are some of my favorite LINQ/ LINQ to SQL resources:

 

Thanks,

Pragya Agarwal

Comments

  • Anonymous
    August 20, 2007
    The comment has been removed

  • Anonymous
    August 20, 2007
    Nella beta 2 di "Orcas", Linq to Sql supporta anche SQL Compact come Data Base! Ovviamente

  • Anonymous
    August 21, 2007
    Nella beta 2 di "Orcas", Linq to Sql supporta anche SQL Compact come Data Base! Ovviamente

  • Anonymous
    August 30, 2007
    HI! I executed the sqlmetal as described in this post. The problem is that when I try to compile the project I obtain a mistake regarding no reference to system.linq.expressions that is in system.core in the normal framework but that is not in the compact. how do I solve this? thx in advance!

  • Anonymous
    November 13, 2007
    The comment has been removed

  • Anonymous
    November 26, 2007
    TransactionScope with Linq on SqlCe does not seem to work even though TransactionScope was promised for RTM for CE. Any idea why ? see: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2466351&SiteID=1&mode=1

  • Anonymous
    December 30, 2007
    Hey Taryon,  I think your problem is with a particular machine configuration.  Please make sure of the following:  1) Chose Add Reference, and add a reference to System.Linq.dll (This is common for any data provider)  2) Start with another fresh project Thanks, Laxmi

  • Anonymous
    December 30, 2007
    Hey gbhasin76,  I think the extra space after the switch /code: is causing the issue.  Please make sure to not have extra spaces. Thanks Laxmi

  • Anonymous
    February 04, 2008
    With 2008 now RTM, I've created a .NET 3.5 PDA application. I created a 3.5 SQL CE database, ran sqlmetal against it and added the .dbml file to the project. I get the same issue as Taryon, the compact framework Linq.Core does not contain the Linq.Expressions namespace, in fact it contains about a 1/10 of the namespaces of the full framework version. LINQ To SQL DataSet is there, LINQ to SQL isn't. Can you confirm that LINQ To SQL CE didn't make it to RTM?

  • Anonymous
    May 05, 2008
    Hi Tristan Smith, LINQ to SQL CE for .NET Framework has got actually released in Visual Studio 2008 RTM. LINQ to SQL CE for .NET Compact Framework has not been released in Visual Studio 2008 RTM. We are sorry about this mismatch and we are working towards making it available in our next version, but at the same time I can not guarrenty it being available in SQL CE v4.0 as priorities change over time.   Thanks, Laxmi

  • Anonymous
    January 30, 2009
    Using LINQ to SQL with SQL Server Compact Edition