Entity Framework - 入門範例

建立範例資料庫(School)

• 利用附件school.sql於sql server上執行,建立範例資料庫.

產生Entity Framework Model

• 於Solution Explorer的Project上點右鍵->Add->New Item.

• 選擇ADO.Net Entity Data Model並取名School.edmx後,按下Add按鈕.

• 選擇[Generate From database],按下[Next].

• 選擇連線之Data Connection,勾取將setting加入app.config後將conneciton name取名為SchoolEntities並點選[Next].

• 勾選Tables(全部Table)/Store Procedures(全部Store Procedure),並勾選下列兩項後,將namespace取名為SchoolModel,按下[Finish].
• Pluralize or singularize generated object names - 單/複數方式產生物件名稱.
• include foreign key columns in model - diagram預設帶入FK relation.

• 產生School Entity Diagram與Classes完成.

利用ObjectContext 進行Select/Insert/Update/Delete

• Init - Create ObjectContext

SchoolEntities schoolContext=new SchoolEntities();

• Select

// init
SchoolEntities schoolContext=new SchoolEntities();
// 取出所有的department資料
var departmentList=schoolContext.Departments;
// 取出所有的department資料,
// 並且每一個department因為FK設定,都可以取得對應的Courses.
var departmentIncludeCoursesList=schoolContext.Departments.Include("Courses");
// 取得第一筆department的Courses.
var courseList=departmentIncludeCoursesList[0].Courses;
// LINQ Join
var departmentCourseInfoQuery = 
    from a in schoolContext.Departments.Include("Courses")
    from b in schoolContext.Courses
    where a.DepartmentID==b.DepartmentID
    select new {a.Name,b.Title};

• Insert

// init
SchoolEntities schoolContext=new SchoolEntities();
// create Course
Course Course = new Course();
Course.CourseID = 4444;
Course.Title = "insert";
Course.Credits = 4;
Course.DepartmentID = 7;
// insert to ObjectContext
schoolContext.Courses.AddObject(Course);
// save change
schoolContext.SaveChanges();

• Update

var Course =
   from c in schoolContext.Courses
   where c.CourseID == 4444
   select c;
Course.First().Title = "AAAA";
schoolContext.SaveChanges();

• Delete

var Course =
     from c in schoolContext.Courses
     where c.CourseID == 4444
     select c;
schoolContext.Courses.DeleteObject(Course.First());
schoolContext.SaveChanges();

利用ObjectContext執行Store Procedure

• 於Model Browser視窗內,找到School.edmx->SchoolModel.Store->Stored Procedures->InsertPerson,於InsertPerson項目上點選右鍵->選擇[Add Function Import].

• 調整Return a Collection Of項目回傳Scalars - Decimal後,按下OK].

• 確認於School.edmx->SchoolModel->EntityContainer: SchoolEntities->Function Imports內,有產生InsertPerson Function.

• 執行InsertPerson

SchoolEntities schoolContext=new SchoolEntities();
decimal UserId = ((decimal)schoolContext.InsertPerson(
    "lastName","firstName",
    DateTime.Now,DateTime.Now
    ).First());