読者です 読者をやめる 読者になる 読者になる

手っ取り早く LINQ to SQL を試すためのテストコード

.NET

私の LINQ to SQL 経験値は 0 に近いのですが,とりあえず見よう見まねで書いてみました.設定ファイルを使い出すと (私が) 分からなくなるので,テーブル定義,リレーション定義,テストデータ入力,クエリの全部を C# のコードで完結させてみます.
このサンプルの実行には SQL Server Compact 3.5 が必要です.ただし .NET Framework 3.5 に SQL Server Compact 3.5 は含まれていません.Visual Studio 2008 をインストールした人は恐らく一緒に SQL Server Compact 3.5 もインストールされるので大丈夫ですが,そうでない人は別途『SQL Server Compact 3.5 ランタイムライブラリ』をインストールして下さい.
SQL Server Compact 3.5 は,SQLite のような In-Process 型の RDBMS です.以下のコードを実行すると,カレントディレクトリに polydb.sdf というデータベースファイルが作られます.

コード

using System;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;

// Required Assemblies
// - System.dll
// - System.Core.dll
// - System.Data.dll
// - System.Data.Linq.dll

public sealed class PolygonDB : DataContext
{
    public Table<Vertex> VertexList;
    public Table<Triangle> TriangleList;
    public PolygonDB(string connection) : base(connection) { }
}

[Table(Name = "VertexList")]
public sealed class Vertex
{
    [Column(IsPrimaryKey = true)]
    public int ID { get; set; }
    [Column]
    public float X { get; set; }
    [Column]
    public float Y { get; set; }
    [Column]
    public float Z { get; set; }
}

[Table(Name = "TriangleList")]
public sealed class Triangle
{
    [Column(IsPrimaryKey = true)]
    public int ID { get; set; }
    [Column]
    public int Index1 { get; set; }
    [Column]
    public int Index2 { get; set; }
    [Column]
    public int Index3 { get; set; }

    [Association(Name = "FK_VertexIndex1", ThisKey = "Index1", IsForeignKey = true)]
    public Vertex Vertex1 { get; set; }
    [Association(Name = "FK_VertexIndex2", ThisKey = "Index2", IsForeignKey = true)]
    public Vertex Vertex2 { get; set; }
    [Association(Name = "FK_VertexIndex3", ThisKey = "Index3", IsForeignKey = true)]
    public Vertex Vertex3 { get; set; }
}

static class Program
{
    static void Main(string[] args)
    {
        using (var db = new PolygonDB(@"polydb.sdf"))
        {
            db.DeleteDatabase();
            db.CreateDatabase();

            //Submit
            //MEMO: このサンプルだとトランザクションは不要
            using (var tx = db.Connection.BeginTransaction())
            {
                var vertices =
                    new[]
                    {
                        new Vertex{ ID = 0, X = -10.0f, Y =  10.0f, Z = 0.0f },
                        new Vertex{ ID = 1, X =  10.0f, Y =  10.0f, Z = 0.0f },
                        new Vertex{ ID = 2, X =  10.0f, Y = -10.0f, Z = 0.0f },
                        new Vertex{ ID = 3, X = -10.0f, Y = -10.0f, Z = 0.0f },
                    };

                var triangles =
                    new[]
                    {
                        new Triangle
                        {
                            ID = 0,
                            Vertex1 = vertices[0],
                            Vertex2 = vertices[1],
                            Vertex3 = vertices[2]
                        },
                        new Triangle
                        {
                            ID = 1,
                            Vertex1 = vertices[2],
                            Vertex2 = vertices[3],
                            Vertex3 = vertices[0]
                        },
                    };

                db.VertexList.InsertAllOnSubmit(vertices);
                db.TriangleList.InsertAllOnSubmit(triangles);

                db.SubmitChanges();
                tx.Commit();
            }

            // Fetch
            {
                Console.WriteLine("Dump All Triangles");
                var q1 = from tri in db.TriangleList
                         select tri;
                Console.WriteLine(q1);

                foreach (var tri in q1)
                {
                    Console.WriteLine("Triangle ID = {0}", tri.ID);
                    Console.WriteLine("\t({0}, {1}, {2})", tri.Vertex1.X, tri.Vertex1.Y, tri.Vertex1.Z);
                    Console.WriteLine("\t({0}, {1}, {2})", tri.Vertex2.X, tri.Vertex2.Y, tri.Vertex2.Z);
                    Console.WriteLine("\t({0}, {1}, {2})", tri.Vertex3.X, tri.Vertex3.Y, tri.Vertex3.Z);
                }
                Console.WriteLine();

                Console.WriteLine("Fetch Triangle ID = 0");
                var q2 = from tri in db.TriangleList
                         where tri.ID == 1
                         select tri;
                Console.WriteLine(q2);

                foreach (var tri in q2)
                {
                    Console.WriteLine("Triangle ID = {0}", tri.ID);
                    Console.WriteLine("\t({0}, {1}, {2})", tri.Vertex1.X, tri.Vertex1.Y, tri.Vertex1.Z);
                    Console.WriteLine("\t({0}, {1}, {2})", tri.Vertex2.X, tri.Vertex2.Y, tri.Vertex2.Z);
                    Console.WriteLine("\t({0}, {1}, {2})", tri.Vertex3.X, tri.Vertex3.Y, tri.Vertex3.Z);
                }
            }
        }
    }
}

実行結果

Dump All Triangles
SELECT [t0].[ID], [t0].[Index1], [t0].[Index2], [t0].[Index3]
FROM [TriangleList] AS [t0]

Triangle ID = 0
        (-10, 10, 0)
        (10, 10, 0)
        (10, -10, 0)
Triangle ID = 1
        (10, -10, 0)
        (-10, -10, 0)
        (-10, 10, 0)

Fetch Triangle ID = 0
SELECT [t0].[ID], [t0].[Index1], [t0].[Index2], [t0].[Index3]
FROM [TriangleList] AS [t0]
WHERE [t0].[ID] = @p0

Triangle ID = 1
        (10, -10, 0)
        (-10, -10, 0)
        (-10, 10, 0)

実行時に SQL が生成されているのがわかります.