ADO.NET Entity Framework Beta2クイックスタート
こんにちは、こだかです。
今日は、先日リリースされました、ADO.NET Entity Framework Beta2とEntity Framework Tools CTPを使用した概念モデルの設計とクエリーのクイックスタートをご紹介します。
１．実行環境を整える
以下をインストールする。
Visual Studio 2008 Beta2英語版
SQLServer 2005(Expressでも可)
ADO.NET Entity Framework Beta2
Entity Framework Tools CTP
２．データベースを用意する
以下のスクリプトを実行し、データベース、テーブル等を作成する。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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)
BEGIN
CREATE 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]
END
GO
-- Create the Person table.
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[Person]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE 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]
END
GO
-- Create the Enrollment table.
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[Enrollment]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE 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]
END
GO
-- Create the CourseInstructor table.
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CourseInstructor]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE 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]
END
GO
-- 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 School
GO
SET IDENTITY_INSERT dbo.Person ON
GO
INSERT 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);
GO
SET IDENTITY_INSERT dbo.Person OFF
GO
-- 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 ON
GO
INSERT 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);
GO
SET IDENTITY_INSERT dbo.CourseInstructor OFF
GO
-- 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(https://www.microsoft.com/japan/powerpro/mson/)等でこのあたり(データアクセス概要)をご説明する予定になっています。
もしご興味のある方は、合わせてご確認下さい。
こだかたろう
Comments
- Anonymous
June 16, 2009
PingBack from http://fixmycrediteasily.info/story.php?id=6341