LINQ Exchange

Using LINQ to SQL with SQL CE 3.5 can be
a bit of a challenge.  First off, the LINQ to SQL Visual Studio
designer doesn’t support SQL CE so you need to run sqlmetal
from the command line to create the object model (or write it by
hand).  Once you get past this point then you can use LINQ to SQL the
same way you would for SQL Sever BUT there is a catch.

The way LINQ to SQL is built makes it work well with SQL Server and its connection pooling ability.  If you look at this FAQ under the "Database Connection: Open How Long?" section it says:

A connection typically remains open until you consume the query results.

Therefore given the following code:

   1: using(Northwind db = new Northwind(MyConnectionString))

   2: {

   3:     (from p in db.Products).ToList();

   4:     (from p in db.Customers).ToList();

   5: }

On line 3 and 4 in the above code will open a database connection
(which will be pulled from the open connections in connection pool),
execute the query, and then close the connection after the operation is
completed (which will return the connection to the pool). 

This works great with connection pooling but when you move to SQL CE
you don’t have that luxury.  What happens now is that for each query a
new SQL CE connection will be opened, the query will be executed and
then the connection is closed.  Each query is incurring the cost of
opening a new connection.  To make matters even worse, in SQL CE the
first open connection  brings the database engine into memory, and once
you have no open connections anymore it removes the engine from
memory.  What this means is that each time we close the only open
connection and then re-open we are incurring a HUGE cost.

One way to get around this is to pass into the DataContext an open
SQLCeConnection object.  LINQ to SQL will only automatically close a
connection if it opens it.  Therefore, if you pass it an open
connection then you won’t incur this cost over and over again. This
will work fine in a single threaded application but once you move to a
mutlithreaded app where you are performing database operations from
different threads you encounter a problem: The SQLCeConnection object
is not thread safe.  You need to have a different connection object per
thread in order to make this work.  What you want is to be able to
request a connection from any thread and get an already opened one for
that thread.  This sounds a lot like a simple connection pooler, which
could look something like this:

   1: /// <summary>

   2: /// Manages open connections on a per-thread basis

   3: /// </summary>

   4: public class ConnectionPool

   5: {

   6:     private Dictionary<int, IDbConnection> threadConnectionMap;

   7: 

   8:     /// <summary>

   9:     /// Gets the connection string.

  10:     /// </summary>

  11:     /// <value>The connection string.</value>

  12:     public string ConnectionString

  13:     {

  14:         get;

  15:         private set;

  16:     }

  17: 

  18:     /// <summary>

  19:     /// Gets a connection.

  20:     /// </summary>

  21:     /// <returns>An open connection</returns>

  22:     public IDbConnection Connection

  23:     {

  24:         get

  25:         {

  26:             lock (threadConnectionMap)

  27:             {

  28:                 int threadId = Threading.Thread.CurrentThread.ManagedThreadId;

  29: 

  30:                 IDbConnection connection = null;

  31:                 if (threadConnectionMap.ContainsKey(threadId))

  32:                 {

  33:                     connection = threadConnectionMap[threadId];

  34:                 }

  35:                 else

  36:                 {

  37:                     connection = new SqlCeConnection(ConnectionString);

  38:                     connection.Open();

  39:                     threadConnectionMap.Add(threadId, connection);

  40:                 }

  41: 

  42:                 return connection;

  43:             }

  44:         }

  45:     }

  46: 

  47:     /// <summary>

  48:     /// Initializes a new instance of the <see cref="ConnectionPool"/> class.

  49:     /// </summary>

  50:     /// <param name="connectionString">The connection string.</param>

  51:     public ConnectionPool(string connectionString)

  52:     {

  53:         threadConnectionMap = new Dictionary<int, IDbConnection>();

  54:         ConnectionString = connectionString;

  55:     }

  56: }

With this you create a DataContext like this:

   1: // Defined somewhere

   2: ConnectionPool connectionPool = new ConnectionPool(MyConnectionString);

   3: 

   4: // …

   5: // …

   6: 

   7: using(Northwind db = new Northwind(connectionPool.Connection))

   8: {

   9:     (from p in db.Products).ToList();

  10:     (from p in db.Customers).ToList();

  11: }

Now each thread will have its own open connection which will minimize the cost of opening connections.

This post originated from and is provided by the MSDN Blogs RSS feed. The original post of the article can be found here.