SQL Compact EditionでLinq to SQLを使う方法

DBMLファイルの作成

Visual Studio 2008のObject Relational DesignerはSQL Server Comapct Editionに対応していおらず、サーバーエクスプローラからのドラッグアンドドロップに対応していません。このためdbmlファイルを作成するにはコマンドラインツールを使用して作成します。

SqmplDb.sdfというSQL CEのデータベースファイルを元にdmlファイルを作成する場合には以下のように作成します。

sqlmetal /dbml:SqmpleDb.dbml SampleDb.sdf

作業はVS2008のコマンドプロンプトで行います。

参考(コード生成ツール (SqlMetal.exe)MSDNライブラリ)

簡単なサンプル

サンプルの中で使用するSqmpleDbのLogMasterテーブルは以下のような構造です

☆id列は自動追加

また、データベースにはパスワードはかけていません。

以下のサンプルでは行の追加、条件付き検索、データの更新、行の削除のサンプルとなっています。

まずはWinFormプロジェクトを新規作成し、デフォルトのFormにボタンを一つ貼り付けます。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
namespace CompacEdition
{
    public partial class Form1 : Form
    {
        public Form1() {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e) {
            string conStr = @"Data Source='SampleDb.sdf'";
            using (SampleDb db = new SampleDb(conStr)) {
                //行の追加
                LogMaster lm = new LogMaster() { Description = "送ガス管(A)温度", Type = "double", Unit = "℃" };
                db.LogMaster.InsertOnSubmit(lm);
                db.SubmitChanges();
                var rows = from x in db.LogMaster select x;
                int lastId = 0;
                foreach (var i in rows) {
                    MessageBox.Show(i.Id.ToString() + " : " + i.Description);
                    lastId = i.Id;
                }
                //行の変更
                var lastrow = from x in db.LogMaster
                               where x.Id == lastId
                               select x;
                foreach (var i in lastrow) {
                    i.Description = "送ガス管(A)最高温度";
                }
                db.SubmitChanges();
                //foreachのタイミングでDBにクエリ発行されているならばここでの表示は先ほどの変更が反映されているはず
                foreach (var i in rows) {
                    MessageBox.Show(i.Id.ToString() + " : " + i.Description);
                }
                //行の削除
                foreach (var i in lastrow) {
                    db.LogMaster.DeleteOnSubmit(i);
                }
                db.SubmitChanges();
            }
        }
    }
}

複雑なクエリ

以下のサンプルでは、標準で添付されるNorthwindデータベースを使用しています。
やりたいことは、従業員ごとに何件のOrderを処理しているか確認したいということにしておきます。

やっていることは、EmployeesテーブルにOrdersテーブルをJOINします。

JOINしたものをEmployeesテーブルのEmployeeIDでグループ化します。

グループ化したものを元に従業員番号ごとに注文の数を数えます。

結果を書式に乗っ取り表示します。
（おまけでADO.NETでSQL CEを扱う倍のコードをちょっとだけ書いておきます。）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data.Common;
using System.Data;

namespace CompactEdition2
{
    class Program
    {
        static void Main(string[] args) {
            /*
            //ADO.NETの場合
            SqlCeConnection con = new SqlCeConnection();
            con.ConnectionString = @"Data Source='Northwind.sdf'";
            con.Open();
            string query = @"SELECT * FROM Employees";
            SqlCeDataAdapter da = new SqlCeDataAdapter(query, con);
            NorthwindDataSet set = new NorthwindDataSet();
            da.Fill(set, "Employees");
            foreach (var row in set.Employees) {
                Console.WriteLine("{0},{1}", row.Employee_ID, row.First_Name + " " + row.Last_Name);
            }
             * */
            //Linq to SQLを使用した場合
            //JOIN Groupingの例
            string conString = @"Data Source='Northwind.sdf'";
            using (Northwind nw = new Northwind(conString)) {
                var ordersByEmployee = from row in nw.Employees
                                       join e in nw.Orders on row.EmployeeID equals e.EmployeeID
                                       into haveOrderEmps
                                       from d in haveOrderEmps
                                       select d;

                var countOfEmployee = from e in ordersByEmployee
                                      group e by e.EmployeeID
                                      into g
                                      select new {
                                          g.Key,
                                          Count = g.Count()
                                      };

                var employeesList = nw.Employees.ToDictionary(p => p.EmployeeID);
                foreach (var i in countOfEmployee) {
                    string name = employeesList[(int)i.Key].FirstName 
                        + " " 
                        + employeesList[(int)i.Key].LastName;

                    Console.WriteLine("{0, -4} {1, -20}: {2}", i.Key, name, i.Count);
                }
            }
        }
    }
}