被遺忘的瞇瞇: Entity Framework

Entity Framework - 控制Entity Transaction

Entity Transaction方法有:

EntityTransaction - 執行EntityCommand交易時使用,由EntityConneciton取得.
Transaction
TransactionScope

Transaction Scope Sample Code

bool Success=true;
using (TransactionScope transaction = new TransactionScope()){
    try{
        // do something
        context.SaveChanges(SaveOptions.DetectChangesBeforeSave);
        transaction.Complete();
    }
    catch(Exception ex){
        bool Success=false;
    }
}
if(Success){
    context.AcceptAllChanges();
}

Entity Framework - Entity SQL

Entity SQL 是與儲存體無關的查詢語言，與 SQL 類似。 Entity SQL 可讓您查詢實體資料 (無論以物件形式或在表格式資料表中)。

支援語法可以參考: Entity SQL Reference

常用語法範例:

select
T-SQL	select * from course
Entity SQL	select value c from courses as c

select where
T-SQL	select title from course where course.departmentId=4
Entity SQL	select c.title from courses as c where c.departmentId=4

select where in

T-SQL

select *

from course 
where departmentid in (
    select departmentid
    from department
    where name in ('Economics','English')
)

Entity SQL

~~select value c~~

from courses as c
where c.departmentId in {
    select value d.departmentId
    from departments as d
    where d.name in
        {'Economics','English'}
}

// 取得符合條件之departmentIdList
ObjectQuery<int> DepartmentIdQuery
    = schoolContext.CreateQuery<int>(@"
select value d.departmentId
from departments as d
where d.name in {'Economics','English'}
");
// 將departmentIdList轉成{id,id,id,id}格式
// ,以靜態方式串入Entity SQL
ObjectQuery<Course> PersonQuery =
    schoolContext.CreateQuery<Course>(
            string.Format(@"
select value c
from courses as c
where c.departmentId in {{{0}}}
",string.Join(","
    ,DepartmentIdQuery.ToArray())));

outer join
T-SQL	select d.name,c.title from course c,department d where c.departmentid=d.departmentid
Entity SQL	select d.name,c.title from courses as c CROSS JOIN departments as d where c.departmentId=d.departmentId

left join

T-SQL

select s.studentid,

s.grade,
       p.lastname+' '+p.firstname as fullname
from studentgrade s
left join person p
on p.personid=s.studentid

Entity SQL

select s.studentid,

s.grade,
       p.lastname+' '+p.firstname fullname
from studentgrades as s
left outer join people as p
on p.personid=s.studentid

count/group by/having
T-SQL	select studentid,count(*) as num from studentgrade group by grade,studentid
Entity SQL	select s.studentid,count(s.grade) as num from studentgrades as s group by s.grade,s.studentid having count(s.grade) > 0 -- 預設連=0都會顯示,所以需要用having>0方式替除

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());

被遺忘的瞇瞇