手っ取り早く LINQ to SQL を試すためのテストコード
私の 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 が生成されているのがわかります.