こんにちは、こだかです。今日は、先日リリースされました、ADO.NET Entity Framework Beta2とEntity Framework Tools CTPを使用した概念モデルの設計とクエリーのクイックスタートをご紹介します。
１．実行環境を整える以下をインストールする。Visual Studio 2008 Beta2英語版SQLServer 2005(Expressでも可)ADO.NET Entity Framework Beta2Entity Framework Tools CTP
２．データベースを用意する以下のスクリプトを実行し、データベース、テーブル等を作成する。
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO
USE [master];GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'School') DROP DATABASE School;GO
-- Create the School database.CREATE DATABASE School;GO
USE School;GO
-- Create the Course table.IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Course]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)BEGINCREATE TABLE [dbo].[Course]( [CourseID] [int] NOT NULL, [Title] [nvarchar](100) NOT NULL, [StartDate] [datetime] NULL, [EndDate] [datetime] NULL, [Credits] [int] NULL, CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED ( [CourseID] ASC) ON [PRIMARY]) ON [PRIMARY]ENDGO
-- Create the Person table.IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Person]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)BEGINCREATE TABLE [dbo].[Person]( [PersonID] [int] IDENTITY(1,1) NOT NULL, [LastName] [nvarchar](50) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [HireDate] [datetime] NULL, [EnrollmentDate] [datetime] NULL, CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED ( [PersonID] ASC) ON [PRIMARY]) ON [PRIMARY]ENDGO
-- Create the Enrollment table.IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Enrollment]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)BEGINCREATE TABLE [dbo].[Enrollment]( [EnrollmentID] [int] IDENTITY(1,1) NOT NULL, [CourseID] [int] NOT NULL, [StudentPersonID] [int] NOT NULL, [Grade] [decimal](3, 2) NULL, CONSTRAINT [PK_Enrollment] PRIMARY KEY CLUSTERED ( [EnrollmentID] ASC) ON [PRIMARY]) ON [PRIMARY]ENDGO
-- Create the CourseInstructor table.IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CourseInstructor]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)BEGINCREATE TABLE [dbo].[CourseInstructor]( [CourseInstructorID] [int] IDENTITY(1,1) NOT NULL, [CourseID] [int] NOT NULL, [PersonID] [int] NOT NULL, CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED ( [CourseInstructorID] ASC) ON [PRIMARY]) ON [PRIMARY]ENDGO
-- Define the relationship between Enrollment and Course.IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Enrollment_Course]') AND type = 'F')ALTER TABLE [dbo].[Enrollment] WITH CHECK ADD CONSTRAINT [FK_Enrollment_Course] FOREIGN KEY([CourseID])REFERENCES [dbo].[Course] ([CourseID])GO
ALTER TABLE [dbo].[Enrollment] CHECK CONSTRAINT [FK_Enrollment_Course]GO
-- Define the relationship between Enrollment and Person.IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Enrollment_Student]') AND type = 'F')ALTER TABLE [dbo].[Enrollment] WITH CHECK ADD CONSTRAINT [FK_Enrollment_Student] FOREIGN KEY([StudentPersonID])REFERENCES [dbo].[Person] ([PersonID])GO
ALTER TABLE [dbo].[Enrollment] CHECK CONSTRAINT [FK_Enrollment_Student]GO
-- Define the relationship between CourseInstructor and Course.IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Course]') AND type = 'F')ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])REFERENCES [dbo].[Course] ([CourseID])GO
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Course]GO
-- Define the relationship between CourseInstructor and Person.IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Person]') AND type = 'F')ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY([PersonID])REFERENCES [dbo].[Person] ([PersonID])GO
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Person]GO
-- Insert data into the Person table.USE SchoolGOSET IDENTITY_INSERT dbo.Person ONGOINSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (1, 'Abercrombie', 'Kim', '1995-03-11', null);INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (2, 'Barzdukas', 'Gytis', null, '2005-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (3, 'Justice', 'Peggy', null, '2001-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (4, 'Fakhouri', 'Fadi', '2002-08-06', null);INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (5, 'Harui', 'Roger', '1998-07-01', null);INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (6, 'Li', 'Yan', null, '2002-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (7, 'Norman', 'Laura', null, '2003-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (8, 'Olivotto', 'Nino', null, '2005-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (9, 'Tang', 'Wayne', null, '2005-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (10, 'Alonso', 'Meredith', null, '2002-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (11, 'Lopez', 'Sophia', null, '2004-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (12, 'Browning', 'Meredith', null, '2000-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (13, 'Anand', 'Arturo', null, '2003-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (14, 'Walker', 'Alexandra', null, '2000-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (15, 'Powell', 'Carson', null, '2004-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (16, 'Jai', 'Damien', null, '2001-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (17, 'Carlson', 'Robyn', null, '2005-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (18, 'Zheng', 'Roger', '2004-02-12', null);INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (19, 'Bryant', 'Carson', null, '2001-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (20, 'Suarez', 'Robyn', null, '2004-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (21, 'Holt', 'Roger', null, '2004-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (22, 'Alexander', 'Carson', null, '2005-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (23, 'Morgan', 'Isaiah', null, '2001-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (24, 'Martin', 'Randall', null, '2005-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (25, 'Kapoor', 'Candace', '2001-01-15', null);INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (26, 'Rogers', 'Cody', null, '2002-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (27, 'Serrano', 'Stacy', '1999-06-01', null);INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (28, 'White', 'Anthony', null, '2001-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (29, 'Griffin', 'Rachel', null, '2004-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (30, 'Shan', 'Alicia', null, '2003-09-01');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)VALUES (31, 'Stewart', 'Jasmine', '2000-02-12', null);GOSET IDENTITY_INSERT dbo.Person OFFGO
-- Insert data into the Course table.INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)VALUES (4050, 'Math', '2007-09-01', '2007-09-01', 4);INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)VALUES (4051, 'Science', '2007-09-01', '2007-09-01', 4);INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)VALUES (3030, 'Art', '2007-09-01', '2007-09-01', 3);INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)VALUES (2021, 'Woodshop', '2007-09-01', '2007-09-01', 2);INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)VALUES (2022, 'Cooking', '2007-09-01', '2007-09-01', 2);INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)VALUES (4041, 'History', '2007-09-01', '2007-09-01', 4);INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)VALUES (4042, 'Literature', '2007-09-01', '2007-09-01', 4);GO
-- Insert data into the CourseInstructor table.SET IDENTITY_INSERT dbo.CourseInstructor ONGOINSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)VALUES (1, 4051, 1);INSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)VALUES (2, 2021, 31);INSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)VALUES (3, 2022, 5);INSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)VALUES (4, 3030, 4);INSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)VALUES (5, 4041, 27);INSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)VALUES (6, 4042, 25);INSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)VALUES (7, 4050, 18);GOSET IDENTITY_INSERT dbo.CourseInstructor OFFGO
-- Insert data into the Enrollment table.INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (2021, 2, 4);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (2021, 3, 3);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (2022, 6, 2.5);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (2022, 7, 3.5);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (2022, 8, 3);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4041, 9, 3.5);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4041, 10, null);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4041, 11, 2.5);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4041, 12, null);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4041, 13, 4);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4042, 14, 3);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4050, 15, 2.5);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4050, 16, 2);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4050, 17, null);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4050, 19, 3.5);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4050, 20, 4);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4050, 21, 2);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4050, 22, 3);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4041, 22, 3.5);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (2022, 22, 2.5);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4051, 22, 3);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4051, 23, 1.5);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4051, 24, 4);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4051, 26, 3.5);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4051, 28, 2.5);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (4051, 29, 3);INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)VALUES (3030, 30, 3.5);GO
３．概念モデルを作成する1)Visual Studio 2008を起動して、新規でWindowsFormアプリケーションを作成する。2)項目の新規追加でADO.NET Entity Data Modelを選択する。名前は適当でOK。3)ウィザードが起動するので下記のように設定する。プロジェクトにedmxファイルが作成される。
４．画面を設計＆プログラムを行う
1)フォームにボタン、コンボボックス、データグリッドをひとつづつ、配置する。
2)ボタンのNameプロパティをcloseForm、コンボボックスのNameプロパティをstudentsList、データグリッドののNameプロパティをstudentClassesとする。3)ボタンのCloseイベントを下記とする。 private void closeForm_Click(object sender, EventArgs e) { // Dispose the object context. schoolContext.Dispose();
// Close the form. this.Close(); }4)フォームのLoadイベントを下記とする private void StudentSchedule_Load(object sender, EventArgs e) { // Initialize the ObjectContext. schoolContext = new SchoolEntities();
// Define the DataGridView. studentClasses.Columns.Add("courseName", "Course Name"); studentClasses.Columns.Add("courseDate", "Date Completed"); studentClasses.Columns.Add("courseGrade", "Grade"); studentClasses.Columns.Add("courseCredits", "Credits");
// Get students as all people who have enrollment dates. ObjectQuery<Person> students = schoolContext.Person.Where( "it.EnrollmentDate IS NOT NULL").OrderBy("it.LastName");
// Define the query path for queries that return a Person object // and bind the ComboBox to the collection returned by the query. this.studentsList.DataSource = students.Include("Enrollment.Course"); this.studentsList.DisplayMember = "LastName"; }
5)コンボボックスのSelectedIndexChangedイベントを下記とする private void studentsList_SelectedIndexChanged(object sender, EventArgs e) { try { // clear existing rows from the DataGridView. studentClasses.Rows.Clear();
// Get the Person object for the selected student. Person student = (Person)studentsList.SelectedItem;
foreach (Enrollment enrollment in student.Enrollment) { // Create an array of row cells. object[] row = new object[4];
// Load object values from entities. row[0] = enrollment.Course.Title; row[1] = enrollment.Course.EndDate; row[2] = enrollment.Grade; row[3] = enrollment.Course.Credits;
// Add the new row to the DataGridView. studentClasses.Rows.Add(row); studentClasses.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
これでビルドが出来るはずです。
作成されたedmxファイルが概念モデルでの実装になります。Entity Designer等を使用してビジュアルに設計ができる点をご確認頂ければと思います。また、フォームロードのタイミングで実行される下記のコードがObjectQueryから実行されたQueryBuilderのクエリーです。 ObjectQuery<Person> students = schoolContext.Person.Where( "it.EnrollmentDate IS NOT NULL").OrderBy("it.LastName");
これをLinqで実装した場合は、例えば下記のように変更します。 var students = (ObjectQuery<Person>)from x in schoolContext.Person where x.EnrollmentDate != null orderby x.LastName select x;
いかかでしたでしょうか？今後MicrosoftON(http://www.microsoft.com/japan/powerpro/mson/)等でこのあたり(データアクセス概要)をご説明する予定になっています。もしご興味のある方は、合わせてご確認下さい。
こだかたろう